Multiple Level Combining Into One Column

I need a formula that does the following:

Result Colours Fruit Animals Numbers
blue,apple,dog,6 blue apple dog 6
blue,apple,dog,7 red peach cat 7
blue,apple,dog,8 yellow grape cow 8
blue,apple,dog,9 green 9
blue,apple,cat,6
blue,apple,cat,7


The Result Column is in column A. This will be the column that has formula that I drag down. I believe that should be 144 rows in total from the formula (4 times 3 times 3 times 4). The column names could be anything. And the number of columns is not fixed. There could be more or less columns. Also the number of rows in each column is not fixed.

I can't seem to figure out this formula

excel2.png

Are you really on Excel 2003? Please update your profile as answers depend on Excel version you have.

sorry it is Microsoft 365 Version 2112 64bit. I didn't realize Excel 2003 was shown in my profile. I've just updated now. Thanks for letting me know

I'm wondering whether perhaps a macro would have worked better for this problem. Do you know if there is a macro somewhere on excelforum that I could use for this? I'm not sure what to search

Please Login or Register  to view this content.

Try this: RESULTS are in Column A and INPUT in column B onwards

Try this dynamic array:

=LET(c,B2:B10,rc,COUNTA(c),f,C2:C10,rf,COUNTA(f),a,D2:D10,ra,COUNTA(a),n,E2:E10,rn,COUNTA(n),x,rc*rf*ra*rn,INDEX(c,INT(SEQUENCE(x,,,1/(rf*ra*rn))))&", "&INDEX(f,1+MOD(INT(SEQUENCE(x,,,1/(ra*rn)))-1,rf))&", "&INDEX(a,1+MOD(INT(SEQUENCE(x,,,1/rn))-1,ra))&", "&INDEX(n,1+MOD(SEQUENCE(x)-1,rn)))

In A2 copied down. If additional items are added to any columns B, C, D, E automatically list will be updated. Formula should be copied down till blank cell is seen.

Please Login or Register  to view this content.