Have you ever visited excelxor.com ? It’s an amazing blog with a lot of Excel formula challenges. Here’s one challenge: “given the the 6-by-4 array on the left we may wish to create the single-column on the right“
The image below shows a 6 row and 4 column matrix of non blank and blank values.
In column G we see the extracted list of non blank entries. There are actually two challenges as we can extract the values by row or by column. In this case we see the ‘by row left to right’ result.
I’ll list Excelor’s solutions and then review mine. Excelxor’s original posts can be found here and here.
Get the Excel file here or here (One Drive) and watch my YouTube video.
EXCELXOR: By Row Solution
Here is the array formula (to enter press Control Shift Enter)
=IF(ROWS($1:1)>$F$1,””,INDIRECT(TEXT(SMALL(IF(Range1<>””,10^5*
ROW(Range1)+COLUMN(Range1)),ROWS($1:1)),”R0C00000″),0))
The only helper the above formula requires is this formula =SUM(COUNTIF(Range1,{“>0″,”?*”})) that counts the number of non blanks. Notice the interesting use of an array constant inside the countif.
What I learned from this solution is that the INDIRECT function can accept a reference in R1C1 format! If you highlight indirect’s ‘ref_text’ argument and press the F9 key to evaluate it you’ll see this: INDIRECT(“R1C00001”,0) The second argument, set to 0, is what tells Excel that we want to use the R1C1 (Row Column) format and not the more common cell address method.
EXCELXOR: By Column Solution
Here is the array formula (to enter press Control Shift Enter).
=IF(ROWS(I$1:I1)>$F$1,””,INDIRECT(TEXT(SMALL(IF(TRANSPOSE($A$1:$D$6)<>””,TRANSPOSE(10^(4+COLUMN($A$1:$D$6))*ROW($A$1:$D$6)+COLUMN($A$1:$D$6))),
ROWS(I$1:I1)),”R0C”&REPT(0,LEN(SMALL(IF(TRANSPOSE($A$1:$D$6)<>””,TRANSPOSE(10^(4+COLUMN($A$1:$D$6))*ROW($A$1:$D$6)+COLUMN($A$1:$D$6))),
ROWS(I$1:I1)))-1)),0))
It appears that it’s more challenging to solve when values are to be displayed by column.
My Solution: By Column
My solution works for both ‘By Row’ and ‘By Column’ challenges (requires slight changes). I’m able to simplify the solution as I use helper columns. This goes against the challenge a bit but this makes the solution more understandable.
- Step 1 Add a simple counter.
- Step 2 Array formula in column J: =SMALL(IF($A$1:$D$6<>””,COLUMN($A$1:$D$6)+ROW($A$1:$D$6)/1000,””),$G4) If the range isn’t blank I get the column numbers as integers. Then I add the row numbers as a decimal! I do this by dividing the row numbers by 1000! Small function then extracts the values one by one using the counter.
- Step 3 In column K this =INT(J4) gives us the integer which is the column number.
- Step 4 In column L this =(J4-K4)*1000 part creates the row number using the decimal value.
- Step 5 =INDEX($A$1:$D$6,L4,K4) Now we simply retrieve the value. Done!
My Solution: By Row
In the array (Step 2 above), we just have to change this COLUMN($A$1:$D$6)+ROW($A$1:$D$6)/1000 to this ROW($A$1:$D$6)+COLUMN($A$1:$D$6)/1000 and voila!
It’s very easy to modify as the two solutions are almost identical (see my Excel file below).
Making It Stop!
In many cases formulas are calculating when it’s not necessary. In this case, we can count the number of non blanks and simply add an IF function like this =IF($G4>$G$1,””, to turn off the calculation (cell G1 counts the non blanks).
Why Solve/Study These Challenges?
Trying to solve these challenges is: (a) a way to improve your problem solving abilities and (b) an exercise in exploring Excel functions. If you work with Excel enough you’ll sooner or later need to solve bizarre challenges and solving/studying Excelxor’s challenges is the workout that will help.
More Challenges From Excelxor!
Despite years of experience working in Excel I’ll be honest that I can’t solve most of Excelxor’s challenges. Nevertheless, I enjoy reading and studying the solutions. Here are some interesting ones:
- excelxor.com/shortest-formula-challenge-6-scrabbled/
- excelxor.com/13-single-array-containing-all-entries-from-a-given-range-in-multiple-sheets/
- excelxor.com/counting-rows-where-at-least-one-condition-is-met/
Thanks Excelxor for the amazing Excel challenges!
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dogs Cali & Fenton, learning Spanish, playing Chess and reading Excel books 🙂