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…
Tips & Tricks 125 – Sometimes Excel sorts Lowercase first and Sometimes Uppercase in the same range
You have been given following data and you sort this data in Excel and you were expecting that lowercase will be sorted before uppercase. But Excel is sorting on the basis of FIFO i.e. First In First Out for the same alphabet. Hence if A is coming before a, it will get sorted as A,…
Excel Quiz 29
Solution – Challenge 31 – Increment All Digits by 1
Below is a possible solution to the problem Challenge 31 – Increment All Digits by 1 Use below formula – =A1+REPT(1,LEN(A1))
Challenge 33 – Convert Matrix into Linear Column – II
Last challenge, we converted a matrix into Linear Column. Last time, we picked up values in a row and populated the column and then moved to next row. This time, we have to do exchange row with column. Hence, you need to pick up values from one column and move to the next column and…
Article 33 – Rank when Duplicates Exist (Ties)
Ranking when duplicates (ties) exist is an interesting problem and you will be called upon to make choices when duplicates exist and you have to rank them. Suppose, you have the data like below. 2 rows are formatted in Yellow and 3 rows are formatted in Green to demonstrate the existence of duplicates.