CODEWITHSUNDEEP
excelexcel-formula
Harshal Gajare
Harshal Gajare

Reputation: 615

Get list of filtered unique values from different sheet using formula

I have below data

 Person Product id Purchased
X   43
Y   58
X   34
X   28
X   34
Y   26
Y   57

I need unique Product ids purchased by Person X using formula in excel in different sheet.

it seems very simple if I use pivot table but just wanted to know if there is any way to get the desired values using formula.

Expected results are:

 Person Product id Purchased
X   43
X   34
X   28

Upvotes: 0

Views: 40

Answers (1)

JvdV
JvdV

Reputation: 75850

Well for example you could try:

enter image description here

Formula in F2:

=IFERROR(INDEX($B$2:$B$8,MATCH(0,IF($E$2=$A$2:$A$8,COUNTIF($F$1:$F1,$B$2:$B$8),""),0)),"")

Formula in J2:

=IFERROR(INDEX($B$2:$B$8,MATCH(0,IF($I$2=$A$2:$A$8,COUNTIF($J$1:$J1,$B$2:$B$8),""),0)),"")

Both entered through CtrlShiftEnter

Sidenote: Might you be interested, google spreadsheet can do the exact same thing as UNIQUE does. For Example, with your sample data just type: =UNIQUE(FILTER(B2:B8,A2:A8="X")) and it will create the whole list for you!

Upvotes: 2

Related Questions