Consolidation (Combining multiple tables (or data) into one)

Hi Everyone,

First of all, thanks in advance for your help on this.

So I have this giant excel (thousands of rows with multiple worksheets) that I need to combine into one excel to be uploaded into our new reporting system. I figured this would be best explained by a sample, which is attached.

Let's say we're selling fruits to customers in three different regions (Europe, Asia and America) and used to track customers separately. Each market sells different types of fruits (In Asia, we sell Coconut, but not in Europe). And now we have a global reporting system that we can combine all of them with a region as a column and list all fruits we sell.

I can manually use INDEX MATCH to to avoid the issue with different columns under each region. But having 5-6 regions with multiple worksheet, I thought I'd ask your help.

Please let me know if you want me to clarify anything. Thanks!

Hi avk,

Sure, I can change the old structure a little bit, but not much. Would you please let me know how I can change so that I can solve my issue?
Thanks!

For Customer, put this on L5 and copied down :
=IFERROR(IFERROR(INDEX($A$5:$A$7,ROWS($A$1:A1)),INDEX($A$15:$A$17,ROWS($A$1:A1)-3)),INDEX($A$23:$A$26,ROWS($A$1:A1)-6))

For Region, put this on M5 and copied down :
=IFERROR(IFERROR(INDEX($A$3:$G$3,SUMPRODUCT(($A$5:$A$7=$L5)*COLUMN($A$5:$A$7))-COLUMN($A$2:$G$2)+1),INDEX($A$13:$G$13,SUMPRODUCT(($A$15:$A$17=$L5)*COLUMN($A$15:$A$17))-COLUMN($A$12:$G$12)+1)),INDEX($A$21:$H$21,SUMPRODUCT(($A$23:$A$26=$L5)*COLUMN($A$23:$A$26))-COLUMN($A$20:$H$20)+1))

For Fruit, put this on N4 and copied cross :

=IFERROR(IFERROR(INDEX($B$4:$G$4,MATCH(0,INDEX(COUNTIF($M$4:M4,$B$4:$G$4&""),0,0),0)),INDEX($B$14:$H$14,MATCH(0,INDEX(COUNTIF($M$4:M4,$B$14:$H$14&""),0,0),0))),INDEX($B$22:$H$22,MATCH(0,INDEX(COUNTIF($M$4:M4,$B$22:$H$22&""),0,0),0)))

For Values, put this on N5 and copied down and cross :

=IFERROR(CHOOSE(MATCH($M5,{"Europe","Asia","America"},0),INDEX($B$5:$G$7,MATCH($L5,$A$5:$A$7,0),MATCH(N$4,$B$4:$G$4,0)),INDEX($B$15:$H$17,MATCH($L5,$A$15:$A$17,0),MATCH(N$4,$B$14:$H$14,0)),INDEX($B$23:$H$26,MATCH($L5,$A$23:$A$26,0),MATCH(N$4,$B$22:$H$22,0))),"")

and hope this works

Thank you so much! I think this can work.
I will give a shot!