Forum Discussion
Consolidate multiple tables of data into one table
I'll step you through my solution.
1. Stack the tables with VSTACK- called 'tStack'
=VSTACK(Table1, Table2, Table3, Table4, Table5)2. Pull the 'Initials' from the tStack
=TAKE(tStack, , -1)3. Pull the 'Area' from tStack
=TAKE(tStack, , 1)4. Define a constant called 'order' to help with the ordering of columns in the end result:
={"NORTH"; "SOUTH"; "EAST"; "PAC NORTH"; "PAC SOUTH"; "TMU"; "FLT DATA"; "IAP"; "DAP"; "TRNG"}5. 'Organize' Lambda checks a unique listing of areas in the order you prefer left to right. It locates potentially multiple initials per area, stacks it vertically, and then stacks results horizontally.
Organize Lambda =LAMBDA(a,v,LET(filtered, FILTER(Initials, Area = v), IFERROR(HSTACK(a, VSTACK(v, filtered)), "")))6. REDUCE delivers the solution.
=DROP(REDUCE("",Order,Organize),,1)
A couple initial responses to your posting.
1. Those pretty clearly aren't your real tables.
2. It's not clear what you are expecting to enter into the columns under the headings in the "Master Table" since you left those rows blank
Rather than images, since you've gone to the trouble (it would appear) to create actual spreadsheets with those entries, why don't you post those actual (albeit hypothetical) spreadsheets on OneDrive or GoogleDrive, and paste a link here that grants edit access. In addition to doing that, please clarify what I point to in #2 above. And maybe give an idea of the magnitude of the actual tables. How many rows? How interrelated are they (I'm alluding to the recurring initials in your example file)?
It is a hypothetical table. I haven't initiated this yet. I am running a Userform that will show a short video but before I can do that I need to figure out how to sort the 5 tables of data into one table which has the column headers "NORTH, SOUTH, EAST..." I am trying to take the data from sheet2 and sorting it into sheet1 under the associated column headers.
Thank you for your response.