distributing/concatenating values from multiple columns

[vlady]

distribute/concatenate values from 4 columns ..

e.g. from attachment

ef.xlsx

[martindwilson]

seems to be no logic to that ,where does hr come from?

[Pete_UK]

Not sure where HR comes from in G13 onwards - should it be FH ?

Not sure where the 11 in G18 comes from - should that be 12 (from D3), and other numbers follow ?

Why does HR (FH ?) carry on in G18 and not reset back to XY?

Where does 12 in the middle of G18 come from - should that be 22 (from B4) ?

Pete

[vlady]

ahh yes my mistake..Thanks both.
here's a better sample .
ef.xlsx

[vlady]

AA 22 XY 12 <- should be 22

[Pete_UK]

In the attached I have kept the individual columns (K to N) as well the concatenated result (column I), though you don't need both.

I've also kept it fairly generic, so you can have any number of entries in cells A3:D10 and it will respond to them (though you might need to copy the formulae down further). Here's the monster formula that produces the concatenated result:

=INDEX(A$3:A$10,MOD(INT((ROWS($1:1)-1)/COUNTA(D$3:D$10)/COUNTA(C$3:C$10)/COUNTA(B$3:B$10)),COUNTA(A$3:A$10))+1)&" "&INDEX(B$3:B$10,MOD(INT((ROWS($1:1)-1)/COUNTA(D$3:D$10)/COUNTA(C$3:C$10)),COUNTA(B$3:B$10))+1)&" "&INDEX(C$3:C$10,MOD(INT((ROWS($1:1)-1)/COUNTA(D$3:D$10)),COUNTA(C$3:C$10))+1)&" "&INDEX(D$3:D$10,MOD(ROWS($1:1)-1,COUNTA(D$3:D$10))+1)

Hope this helps.

Pete

[vlady]

Thanks working perfectly.
Can't think solutions for this very dizzy today.
Problem in EF. slowing down on me.

Thanks again.

Regards,
Vladimir

[Pete_UK]

You're welcome - glad to help.

If you don't need the flexibility built in, you can change those COUNTA functions to the actual number of cells used in each column and shorten the formula considerably.

Pete