CODEWITHSUNDEEP
excel
D. Morley
D. Morley

Reputation: 73

Return Unique Values that Match Text

I have 2 columns that have this data in columns D and E, starting at row 1:

Errors LG ID    Current Status
26054034    COMPLETE
26054032    COMPLETE
26054030    COMPLETE
26054029    COMPLETE
26054028    ERROR
26054028    ERROR
26054023    ERROR
26054023    ERROR
26052400    ERROR
26051579    REJECTED
26035771    ERROR
26035771    ERROR
26035771    ERROR
26035764    COMPLETE
26035764    COMPLETE
26035592    COMPLETE
26035592    COMPLETE
26035585    ERROR
26035585    ERROR
26035585    ERROR
26035560    COMPLETE
26035560    COMPLETE
26035559    ERROR
26035559    ERROR
26035559    ERROR
26035555    COMPLETE
26035555    COMPLETE
26035551    ERROR
26035551    ERROR
26035551    ERROR

I am trying to have column G return a unique list of IDs with a status of "Error". There are 7 unique IDs within this list so the output I'm looking for would be a list of the 7 IDs in column G. Any suggestions? I need this done in a formula since I cannot do this manually as the list gets longer throughout the day.

Thanks

UPDATE: This question is not a duplicate of "Count unique values in a column in Excel" previously asked because that question is merely asking for a count of unique values, which I already have on my sheet. I'm asking for a list of unique values that match the text "Error".

Upvotes: 0

Views: 202

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use this array formula:

=IFERROR(INDEX($A$2:$A$31,MATCH(1,(COUNTIF($D$1:D1,$A$2:$A$31)=0)*($B$2:$B$31="ERROR"),0)),"")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. IF done correctly then Excel will put {} around the formula.

So put in D2, Hit Ctrl-Shift-Enter, then copy down.

enter image description here

Upvotes: 1

Related Questions