CODEWITHSUNDEEP
exceluniqueextract
Miaka3
Miaka3

Reputation: 405

MS EXCEL: Extract Unique Values Based On Criteria

Using MS Excel 2010, I would like to use an Array Formula that will extract values from a list and return only non-duplicates that are unique based on the "Owner" AND "Status".

enter image description here

Please Notice: The desired output results example shown above, displays the Owner "David Boone" twice, as he owns two different make & model vehicles, but both share the same year.

Upvotes: 0

Views: 1943

Answers (2)

Miaka3
Miaka3

Reputation: 405

James, this is a little long, but effective in producing the desired outcome, based off of your previous answer.

Enter the following in Column E2 and copy down: (Formula brings columns A2 through D2 together as one text string) =TRIM(CONCATENATE(A2," ",B2," ",C2," ",D2))

Enter the following in Column F2 and copy down: (Formula Extracts only unique values found in Column E2 where the Status = "Own") =IFERROR(INDEX($E$2:$E$8,MATCH(0,IF($B$2:$B$8="Own",COUNTIF($F$1:$F1,$E$2:$E$8),""),0))&"","")

Enter the following in Column G2 and copy down "Owner": (Index Match returns the corresponding value identified in Column F2) =IFERROR(INDEX($A$2:$A$8,MATCH(F2,$E$2:$E$8,0)),"")

Enter the following in Column H2 and copy down "Status": (Index Match returns the corresponding value identified in Column F2) =IFERROR(INDEX($B$2:$B$8,MATCH(F2,$E$2:$E$8,0)),"")

Enter the following in Column I2 and copy down "Make/Model": (Index Match returns the corresponding value identified in Column F2) =IFERROR(INDEX($C$2:$C$8,MATCH(F2,$E$2:$E$8,0)),"")

Enter the following in Column J2 and copy down "Year": (Index Match returns the corresponding value identified in Column F2) =IFERROR(INDEX($D$2:$D$8,MATCH(F2,$E$2:$E$8,0)),"")

Upvotes: 0

James Hawkins
James Hawkins

Reputation: 218

I'm not 100% sure I understand the question as it seems to me that the first John Doe should be in the result in that he is a unique owner with a unique status. In any case, if I'm off track, you can likely tweak this concept to for what you want.

// copy this down
E2: =CONCATENATE(A2," ",B2)

// copy this over 3 additional columns (G, H, I) and then all of these down
F2: =IF(COUNTIF($E$1:$E1,$E2)>0,"",B2)

Columns F, G, H, I will display the info with blank rows where the Owner and Status are not a unique combination.

Upvotes: 0

Related Questions