Forum Discussion
Consolidate multiple tables of data into one table
- Mar 07, 2023
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
- keao46Mar 08, 2023Copper Contributor
- Patrick2788Mar 08, 2023Silver Contributor