Picking entries left to right and skipping blanks

[Hitch75]

In the example attached, I have formulas which pick the entries from the "red" columns, one by one left to right (skipping the blanks), and when there is no more entries, the formula goes back to the beginning and picks the entries in the same order again.

For example:
_ 1 _ 2 = 1212
_ _ 1 _ = 1111
1 _ 2 3 = 1231

I need the same but with SIX columns. Can someone help me do that, using similar formulas or somehow "spreading" the idea to two more columns?

For example:
_ 1 _ 2 _ _ = 121212
1_ _ _ 2 3 = 123123

Thanks in advance

[Andrew-R]

Hi, you can replace your existing formula with:

=INDEX($A1:$D1,1,LARGE(INDEX(COLUMN($A1:$D1)*--($A1:$D1<>""),0),COUNTA($A1:$D1)-MOD(COLUMN()-5,COUNTA($A1:$D1))))

This is set to work on 4 column (A-D), but can be extended to any number of columns by changing all of the references to $A1:$D1 to the new range (e.g. $A1:$F1 for 6 columns)

However, the part which says COLUMN()-5 must be adjusted so that the 5 is replaced by whatever column number the formula first appears in. So if you were going to have data in columns A:F and then this formula in columns G:L then you'd have to change that bit to COLUMN()-7

I've updated your example sheet to show this working with 6 columns.

[NBVC]

Assuming data starts in A1 to F1 and down. then in G1:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER copies across 6 columns and down

[Hitch75]

Andrew, the formula doesn't work when a blank space is actually a result of a formula...
And that's what it is in my case...

[Hitch75]

NBVC, the same problem with your formula...

[NBVC]

If that is the case, change my formula to:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down and over the matrix.

[Hitch75]

It works in your file, but for some reason it doesn't in mine...
In my file, the "red" columns are starting in the cell T7 and going to Y7; and the formulas start in Z7.

So I changed it this way:
=INDEX($T7:$Y7;SMALL(IF($T7:$Y7<>"";COLUMN($T7:$Y7));MOD(COLUMNS($T$7:T$7)-1;COUNTIF($T7:$Y7;"?*"))+1))

I do it with CTRL+SHIFT+ENTER and still I get #REF.
What am I doing wrong?

[NBVC]

You need to adjust for the offset from the 1st column....

Try:

=INDEX($T7:$Y7;SMALL(IF($T7:$Y7<>"";COLUMN($T7:$Y7)-COLUMN($T7)+1);MOD(COLUMNS($T$7:T$7)-1;COUNTIF($T7:$Y7;"?*"))+1))

[Hitch75]

That's it Great!!
Thanks a lot!