I have 20 columns that I would like to stack into one (preferably leaving out any blanks).
These columns are on different sheets (all in the A column), but I can bring them into the same spreadsheet first if need be.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
To:
1 5 9 2 6 3 7 10 12 14 4 8 11 13
Is there a way I can do this without any macros or VBA?
Could you confirm your Excel version?
2010?
"Microsoft Excel for Microsoft 365 MSO" 32-bit
v2108
Solution proposed:
Formula:
Please Login or Register to view this content.
1. Please update your version in your profile.
2. If you can bring all the columns into 1 sheet (say from Col A to Col T, you could try this:
=LET(Rng,A1:T20,r,ROWS(Rng),c,COLUMNS(Rng),s,SEQUENCE(r*c,,0),i,INDEX(Rng,MOD(s,r)+1,s/r+1),FILTER(i,i<>0))
The only thing you need to control in this formula is right after "Rng" (where it says A1:T20). Change A1:T20 to whatever range the columns are that you want to merge into one.
Another option if you keep all the columns on different sheets is to use this formula:
=LET(
a, CHOOSE({1,2},Sheet1!A1:A20,Sheet2!A1:A20),
b, SEQUENCE(ROWS(a)*COLUMNS(a),,0),
c, FILTER(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1),NOT(ISERROR(INDEX(a,MOD(b,ROWS(a))+1,SEQUENCE(ROWS(a)*COLUMNS(a),,0,1/ROWS(a))+1)))),
FILTER(c,c<>0))
The only things you need to change on this formula is after the "Choose", within the brackets, enter in sequential numbers starting with 1 to the number of columns you want to combine, then right after that, list the range of the columns (Sheet1!A1:A20, etc.)
Last edited by Gregb11; 11-25-2021 at 11:32 PM.
This is PERFECT and exactly what I wanted. Thank you!!!!!!!!Originally Posted by Gregb11
Thank you for the help. This broke my cells that had multiple words into separate cells. I have what I wanted with Greg's answer, but I do appreciate the time you took to try and help. Repped!
Last edited by justinx; 11-26-2021 at 12:11 AM.
This happen because the exemple given.
Good lock, be happy.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks