Forum Discussion
Solved
Consolidate multiple tables of data into one table
Hello,
I have been trying to work on a formula to combine the 5 tables of data into one table and sort into categories for over a week. I am truly stuck. Could someone please assist me in this endeavor? The screenshots will be uploaded.
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)
7 Replies
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)Hello Patrick2788,
Thank you for taking the time to figure this out. That's exactly what my goal was. I have a Userform where they input their Area and Initials. Then it goes into the 5 tables. From there I wanted the initials to be sorted by Area which I see you did.I appreciate you drawing the steps out for me however what cell do I put all of these codes in?
Also, is there a way to not allow duplicate initials from being entered into the Userform?Thanks
- Glad I could help. You may want to start a new discussion for the userform. I use VBA as a last resort and don't keep up with it as much as some on this board.
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)?
- mathetes,
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.
