Below is a possible solution to the challenge – Challenge 33 – Convert Matrix into Linear Column – II. Enter below formula and drag down – =IF(OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),ROUNDUP(ROWS($1:1) /COUNTA($A:$A),0)-1)=0,"",OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)), ROUNDUP(ROWS($1:1)/COUNTA($A:$A),0)-1)) The workbook containing the above solution can be downloaded from Solution – Challenge 33 – Convert Matrix into Linear Column – II.
Article 35 – VBA – Timer Function and Overcoming Midnight Limitation of Timer Function
The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in…
Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm
This time challenge is to come up with a formula (or a VBA function though formula is preferred) to give the result of TRUE or FALSE if a 16 digit credit card number can be validated or not using Mod 10 algorithm. The credit card numbers will be stored as Text as Excel can't contain…
Excel Quiz 30
Tips & Tricks 128 – Used F9 to See Values in the Formula but Values Stick / Formula doesn't gets Restored
We know that great trick that you can select part of the formula and see it values by pressing F9. See the below snip where I have selected part of the formula (see shaded area) and pressed F9 to see its values. Now, if I press enter that part of the formula gets converted to…
Tips & Tricks 127 – Change Default File Extension for Saving
We know that when we save an Excel file, it gets saved as .xlsx file. But sometimes, few users may need to change the default file extension to something else. You can change it in File > Options > Save
Solution – Challenge 32 – Convert Matrix into Linear Column – I
Below is a possible solution to Challenge 32 – Convert Matrix into Linear Column – I Enter following formula and drag down – =IF(OFFSET($A$1,ROUNDUP(ROWS($1:1)/COLUMNS($A$1:$D$100),0)-1, MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100)))=0,"",OFFSET($A$1,ROUNDUP(ROWS($1:1) /COLUMNS($A$1:$D$100),0)-1,MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100))))
Article 34 – Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)
I had already written about one formula where I dealt with coming day formula – Article 3 – Show Date for a Coming Day (e.g. Coming Saturday) I wanted to revisit this with following things in mind – 1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional…
Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits
I had posted a relatively simpler problem Challenge 31 – Increment All Digits by 1 which talked about adding 1 to all digits i.e. if your number is 7409, you needed to add 7409+1111 and come up with the answer of 8520. I had also posted Tips & Tricks 119 – Numerology Sum of the Digits…
Tips & Tricks 126 – Press CTRL+A Three Times to Select Entire Worksheet not Two Times
The safest bet to select entire worksheet is through pressing CTRL+A three times not two times or one time, if you are using shortcut (The safest bet is to press the triangle between 1 and A as marked in Red in the given picture. The tip is for CTRL+A shortcut.) This peculiarity of CTRL+A shortcut…