CODEWITHSUNDEEP
excelexcel-formulaformulauniquehorizontallist
KirklandReds
KirklandReds

Reputation: 11

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

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

Upvotes: 1

Views: 472

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

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))

enter image description here

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),""))

Upvotes: 3

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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

enter image description here

• 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.

enter image description here

• 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,

enter image description here

• 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

enter image description here

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"

enter image description here

• 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.

Upvotes: 2

Ike
Ike

Reputation: 13024

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,""))

enter image description here

Upvotes: 1

Related Questions