How can i return unique values horizontally based off another list on Excel?

Question

I have table that looks like this from A1-B10

	A	B
1	ID	Animal
2	TEST1	Zebra
3	TEST2	Zebra
4	TEST4	Zebra
5	TEST1	Giraffe
6	TEST2	Giraffe
7	TEST4	Monkey
8	TEST5	Monkey
9	TEST5	Cat
10	TEST1	Cat

Currently i look down the B1-B10 list and copy and paste to column C,D,E...etc

So a formula should be able to look for all the Animals associated with Tests and populate all results horizontally.

ID	Animal	Animal	Animal
TEST1	Zebra	Giraffe	Cat
TEST2	Zebra	Giraffe
TEST3
TEST4	Zebra	Monkey
TEST5	Monkey	Cat

Answer 1

If you have Excel 365 you can use this formula in F2 to return all animals of TEST!

=TOROW(FILTER(B2:B10,A2:A10=E2,""))

Answer 2

You can use one single dynamic array formula to spill, here are two alternatives, also you can use Power Query to perform this task.

---

Using Formulas

---

• Formula used in cell E2

=TRANSPOSE(FILTER($B$2:$B$10,$D2=$A$2:$A$10,""))

---

The above formula needs to fill down, while the formulas are one single dynamic array formulas which spills.

---

---

• Formula used in cell D2

=LET(
a,A2:A10,
b,B2:B10,
ua,UNIQUE(a),
HSTACK(VSTACK(A1,ua),IFERROR(REDUCE(TEXTSPLIT(REPT(B1&"|",3),"|",,1),ua,LAMBDA(x,y,
VSTACK(x,TOROW(FILTER(b,ISNUMBER(XMATCH(a,y)),""))))),"")))

---

Or,

---

• Formula used in cell D2

=LET(array,A2:A10,
Col,COUNTIF(A2:A10,A2:A10),
VSTACK(HSTACK($A$1,TEXTSPLIT(REPT($B$1&"|",SEQUENCE(,MAX(Col))),"|",,1)),
IFERROR(UNIQUE(HSTACK(A2:A10,MAKEARRAY(ROWS(array),MAX(Col),
LAMBDA(r,c,INDEX(FILTER(B2:B10,array=INDEX(array,r)),c))))),"")))

---

Or, Using Power Query

---

To perform the above task using Power Query follow the steps:

• First convert the source range into a table and name it accordingly, for this example I have named it as Table1

---

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

---

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following by removing whatever you see, and press Done

M-Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"All", each _, type table [ID=text, Animal=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Animal", "Index"}, {"ID", "Animal", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Custom", {{"Index", each "Animal " & Text.From(_, "en-US"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Animal")
in
    #"Pivoted Column"

---

---

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a new sheet with the required output while the latter will prompt a window asking you where to place the result.

---

Answer 3

Return Matches in Columns

In D2 (also return unique values; none can be missing):

=LET(Data,A2:B10,uCol,1,vCol,2,
    ud,INDEX(Data,,uCol),vd,INDEX(Data,,vCol),
    u,SORT(UNIQUE(ud)),
    v,IFNA(DROP(REDUCE("",u,LAMBDA(rr,r,
        VSTACK(rr,TOROW(FILTER(vd,ud=r))))),1),""),
HSTACK(u,v))

In J2 (I2:I6 already contains the unique values; some may be missing):

=LET(Data,A2:B10,uCol,1,vCol,2,lData,I2:I6,
    ud,INDEX(Data,,uCol),vd,INDEX(Data,,vCol),
IFNA(DROP(REDUCE("",lData,LAMBDA(rr,r,
    VSTACK(rr,TOROW(FILTER(vd,ud=r,""))))),1),""))