MS EXCEL: Extract Unique Values Based On Criteria

0
votes

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.

2
exceluniqueextract
Do you need to use formula? Excel has already a "Remove duplicates" function - Magnetron
Or use Advanced Filter and check the Unique choice. - Scott Craner
Thanks, I'm looking for a formula, instead of a function, that will remove duplicates and return only values that meets the criteria of Only displaying records under the Status as "Own" and returning the corresponding related fields associated with the owner. - Miaka3
Please share what you have tried and where it fails. - Scott Craner
Trying to equate over three columns to get only unique with an array formula is going to be arduous. And will result in circular references. - Scott Craner

2 Answers

0
votes

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.

0
votes

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