Consolidate multiple columns to one using formulas

Hello,

I have multiple columns that are different in size and that are getting updated from different sheets each time a new value is added.

I am looking for a formula solution that will consolidate them in a single column, one under another. I have a total of 9 columns which differ in the number of rows and will be different in the future as well.

In the attachment I color coded them to be more visible.

Thank you for your help!

try below formula in O4, copy and paste it towards down

Formula:

Please Login or Register  to view this content.

Originally Posted by Tjfinley

HI Samba_ravi. I am working with a similar workbook so I tried adjusting your formula to fit my sheet. Can you help me figure out where I went wrong? I don't think it's finding all of the values and it's not leaving out blanks.

Thanks

Paste the formula in Q2 and you will get the same result.

=IFERROR(INDEX($A:$M,MOD(AGGREGATE(15,6,($D$2:$M$9="")*10^10+(COLUMN($D$2:$M$9)&TEXT(ROW($D$2:$M$9),"000000")),ROWS(M$1:M1)),1000000),INT(AGGREGATE(15,6,($D$2:$M$9="")*10^10+(COLUMN($D$2:$M$9)&TEXT(ROW($D$2:$M$9),"000000")),ROWS(M$1:M1))/1000000)),"")


You also only went down to row 40, the data fills in all the way to row 45 but you can keep filling in the formula down to what ever row desired and will stay blank until there is a value in your columns with data.