Advanced Formula Challenge #1: Single column from many (containing blanks) (2) – Columns first 2

We saw in a previous post (here) an outline for a solution which, given a two-dimensional array, potentially containing some empty cells, generated a list of all non-blank entries from that array in a single column.

In that solution the returned entries were listed in an order which is consistent with the entries from an entire row from the original array being returned prior to moving onto those in the next row. The converse, in which entries are returned in a columns-first fashion, is the challenge I would like to set for any readers of this post willing to have a go.

Readers are advised to go through the link to that first solution, if they have not already done so, and either adapt the solution given there to meet this new requirement, or come up with a completely different approach which solves the problem.

To re-cap: given the the 6-by-4 array on the left (assumed to be stored as the Named Range Range1), the previous post gave a solution to generate the entries as per column G.

Several Columns from Many (2) 150

The entry in cell F1 is derived from the formula:

=SUM(COUNTIF(Range1,{">0","?*"}))

i.e. counts the number of non-blank cells in Range1.

And the array formula in G1 is:

=IF(ROWS($1:1)>$F$1,"",INDIRECT(TEXT(SMALL(IF(Range1<>"",10^5*ROW(Range1)+COLUMN(Range1)),ROWS($1:1)),"R0C00000"),0))

Your challenge is to come up with a solution which generates the list in column H: the same values as in column G, but this time returned with respect to columns, that is in an order which is consistent with the entries from an entire column from the original array being returned prior to moving onto those in the next column.

Solutions should involve a single formula placed in H1 which, when copied down an arbitrary number of rows, generates the entries as above. The formula should return a blank for all rows to which the formula is copied beyond row 13.

Note also that a set-up which goes through each of columns A to D in turn, i.e. returning non-blanks from one before moving on to the next, is not really the point: it should be borne in mind that the number of columns here is purely arbitrary, and a solution should be able to cope with any number of columns, without the need for the addition of extra clauses to the formula.

If you’re not sure what I mean by this, try making Range1 100 columns wide, not just 4: if you find your solution suddenly requiring another 96 clauses adding to it then you’re probably on the wrong track!

Solution next Sunday. Good luck!

2 comments

  1. Hi XOR LX…

    Maybe this formula in H1:

    =IF(ROWS(G$1:G1)>$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(G$1:G1)),"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(G$1:G1)))-1)),0))

    Confirm with CSE.

    Blessings!

  2. Great stuff, John! Very impressed! I’ve checked it and it does indeed give the desired results. Well done!

    One observation I might add is that it seems as if the double-transposition is not necessary (they cancel each other out, effectively). I also changed your TEXT function to make it compatible with English versions.

    Also, you might want to think about how your 10^(4+COLUMN(… set-up would work if the range was not A1:D6, but e.g. T1:W6: those powers of 10 will become pretty big! How would you re-write this part so that it functions for any range?

    Thanks again for the contribution!

    XOR

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s