Alright, I'm pretty much at my wits end. I feel like I'm all over a solution but I'm probably over thinking this.
1. I am trying to extract a dynamic unique list from a column in a growing table on sheet 1. (I think I have this.)
2. I would like to display this vertically on sheet 2. (I think I have this.)
3. Then I need to compare another column in the same table on sheet 1 to my unique list to extract a second unique sub-list that correlates with each item from the first unique list.
4. I would like these sub-lists to be displayed on sheet 2 horizontally to line up with the first vertical unique list.
Notes: All data is mixed (text and numbers) and unsorted. There won't be any blanks in the dataset. All lists created need to be dynamic so data added to the table will automatically be added to the lists.
I included my spreadsheet to help. Please ask any questions. On sheet 2 in column A I have #1 and #2 which I think is correct. Column C on that sheet is the same dynamic info just sorted alphabetically. I intended for my sub-lists to extend horizontally from column E.
*UPDATE 1* I don't have permission to upload files so not sure how to link this.
*UPDATE 2* If this has an option in PowerQuery that might work; however I don't have PowerPivot.
Thank You,
Will
Hi Will,
Welcome to our forum 🙂
Try uploading the file to our Help Desk instead, until the forum will be fully functional: Help Desk
I didn't know I needed to write down the help desk ticket #. Is there anyway you can check to see if it uploaded and then place in the thread?
Thank You,
Will
Hi Will,
You don't need to open a ticket, it's just a temporary solution until the file upload on forum will be functional. Received the file, I will upload it later here on forum.
The best approach, in case you are not familiar with power query, is to use a pivot table to aggregate data (it will remove the duplicates). Then the formula to return multiple matches horizontally is much simpler than a solution without pivot table:
=IFERROR(INDEX(PTRange,SMALL(IF(PTRange='Sheet 2'!$C5,ROW(PTRange)-3),COLUMN(A1)),2),"")
In this formula, PTRange is a dynamic name, that will auto-adjust based on the pivot table range.
It's an array formula, confirme with Ctrl+Shift+Enter.
Thank you for your response Catalin; however the reason I didn't use Pivot Tables from the beginning is they are not truly dynamic. You still have to refresh them. These lists I am trying to make dynamic so I can feed into a userform for more data entry.
I tried using PowerQuery and I did get it to work but I had an individual table for each unique sub-list (about 30) and everytime I added more data it would take almost a full minute to auto update all those queries. So I was trying to find another solution. PowerQuery may be the best way to go. I don't know much about it so I may have been doing it all wrong.
Pivot tables requires to be updated, that is true, but so does Power Query...
I can see no difference from this point of view.
The Pivot table solution is dynamic, every time the table is updated, the names and the formulas will update too.
I'll try a Power Query solution, hope to find some time for this.
Hi Will,
Attached is a Power Query version to create the list of lists, instead of using formulas and pivot tables.
The query is:
let
CombineTrucks = Combiner.CombineTextByDelimiter("^"),
AllTrucksList = Table.Group(NoDuplicatesTable, {"Trucker"}, {{"Truck List", each CombineTrucks([Truck]), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(AllTrucksList,"Truck List",Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv),{"Truck List.1", "Truck List.2", "Truck List.3", "Truck List.4", "Truck List.5", "Truck List.6", "Truck List.7", "Truck List.8", "Truck List.9", "Truck List.10", "Truck List.11", "Truck List.12", "Truck List.13", "Truck List.14", "Truck List.15", "Truck List.16", "Truck List.17", "Truck List.18", "Truck List.19", "Truck List.20", "Truck List.21", "Truck List.22"})
in
#"Split Column by Delimiter"
The Query depends on another query, named NoDuplicatesTable, where I preserved only 2 columns: Truck and Trucker, and removed the duplicates, iin order to have the Trucker-Truck combination only once:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Tare", Int64.Type}, {"Gross", Int64.Type}, {"Tons", type number}, {"Trucker", type text}, {"Truck", type any}, {"Plant", type text}, {"Material", type text}, {"Year", type datetime}, {"Date2", type datetime}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Trucker", "Truck"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Truck", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type1"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Trucker", Order.Ascending}})
in
#"Sorted Rows"
