CODEWITHSUNDEEP
excelunique
lostinOracle
lostinOracle

Reputation: 408

Pulling unique values with multiple conditions

I have 3 columns in a table: Column A has id numbers (500 different unique numbers), B has ranking (rank 1 to 500), and C has years (2011 to 2020).

enter image description here

I want to extract to a new table that lists 50 numbers by ranking across the 10 years, from greatest to least for each year; i.e. pull in all unique values that are of that same year or older. enter image description here

My formula for the first year column is straightforward: =LARGE(IF('Sale Candidates'!$O$7:$O$797<=C$2,'Sale Candidates'!$D$7:$D$797),$B3) ; where $O$7:$O$797 is the years from the main data table, and $D$7:$D$797 the ranking from the main data table.

It's the columns that come after that I'm having difficulty with. How can I pull in unique values for the 2nd year and on, so that it's not pulling in values that were already pulled in the prior columns?

Upvotes: 0

Views: 55

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34440

You just need to introduce another condition to check if the number has been used before

=IFERROR(LARGE(IF(($C$2:$C$11<=F$1)*(COUNTIF($E$2:E11,$B$2:$B$11)=0),$B$2:$B$11),$A2),"")

entered as an array formula starting in F2 and assuming the first column of largest numbers starts in E2.

Or you could do it with AGGREGATE

=IFERROR(AGGREGATE(14,6,$B$2:$B$11/(($C$2:$C$11=F$1)*(COUNTIF($E$2:E$11,$B$2:$B$11)=0)),$A2),"")

which doesn't have to be entered as an array formula.

enter image description here

Upvotes: 2

Related Questions