Unique Rows & Additional Filter

[HanSam]

So I can extract the unique rows from the table on the file using unique then it is being sorted based on column A. Next step however is to only contain one line of the 5th column. If there are two or more lines, yes on both 3rd and 4th column is considered. If yesyes is not available, yesno is next considered and finally nono. Noyes will never be an occurence.



File Here

 

[Chihiro]

Formula isn't the best way to do this. Just use PowerQuery/Get & Transform.

If using formula, You'll need to do it in stages.

1. Extract list of unique values for column 5. I.E. UNIQUE(E:E)
2. Sort based on Column 3 & 4, descending. You could do this using formula, but more efficient to sort the original table.
3. Find first match for Column 5 value from sorted data and retrieve info.
Assuming UNIQUE(E:E) is used in Column L... and original list is sorted...
In H1:
=INDEX(A:A,MATCH($L1,$E$1:$E$39058,0))
Copy across and down.