this formula is wayy too complex i rather VBA it. Is pivot table or something gonna work at all?How about
Fluff.xlsm
A B C D E F G H 1 2 A 1 A 1 2 3 3 A 2 B 1 2 4 A 3 C 1 2 3 4 5 B 1 6 B 2 7 C 1 8 C 2 9 C 3 10 C 4 Sheet6
Cell Formulas Range Formula D2:H4 D2 =LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,TOROW(EXPAND(FILTER(B2:B100,A2:A100=y),m,,"")))))),1)) Dynamic array formulas.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each _, type table [Column1=text, Column2=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"INDEX",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column2", "INDEX"}, {"Column2", "INDEX"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"INDEX", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"INDEX", type text}}, "en-US")[INDEX]), "INDEX", "Column2")
in
#"Pivoted Column"
this one is simple enough but the transposed data has too many irrelavent data. Is there a way to let the filter formula to filter the specified columns to return and then transpose using torow?Another option with a formula
Cell Formulas Range Formula D2:D4 D2 =UNIQUE(A2:A10) E2:G2,E4:H4,E3:F3 E2 =TOROW(FILTER($B$2:$B$10,$A$2:$A$10=D2)) Dynamic array formulas.
Sub test()
Set d = CreateObject("Scripting.Dictionary"): Application.ScreenUpdating = False
With Sheets("Sheet1")
Dim table(): table = .Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
Dim i As Long: For i = LBound(table) To UBound(table)
d(table(i, 1)) = d(table(i, 1)) & table(i, 2) & ","
Next i
.Range("D1").Resize(d.Count) = Application.Transpose(d.keys)
.Range("E1").Resize(d.Count) = Application.Transpose(d.items)
.Range("E1").Resize(d.Count).TextToColumns comma:=True
End With
Application.ScreenUpdating = True: Set d = Nothing: Erase table
End Sub