CODEWITHSUNDEEP
excel
DanielleinaD
DanielleinaD

Reputation: 45

Excel - Find the Top 'X' amount of values based on criteria

I am trying to solve the following problem.

I want to find the top 'X' amount of values based on the specified criteria.

I found the following code online, but i am having trouble understanding it / modifying it to include if statements.

=INDEX(DATA!B:B,MATCH(1,INDEX((DATA!B:B=LARGE(DATA!B:B,ROWS(U$1:U1)))*(COUNTIF(U$1:U1,DATA!B:B)=0),),0))

Data!B:B refers to my column where i am storing my values.

I have a list of names in Column C and a list of states in column A.

Basically, i want to only show the top values, for those that are in a specific state and name.

State   Random Value    Name
VIC     $99,581.00      Dan
WA      $95,713.00      Jim
NT      $93,469.00      Bob
NSW     $93,259.00      Cat
NSW     $92,425.00      Dan
ACT     $89,513.00      Hat
NSW     $88,727.00      Potato
NSW     $86,612.00      Fence

Edit*

I forgot to mention that i have tried using a pivot table, but my main issue is that i dont want people or similar strings grouped together. When i created my pivot table it grouped the two strings together and summed them. I want distinct entries for each.

Upvotes: 0

Views: 5865

Answers (3)

M.L
M.L

Reputation: 328

I'd use Pivot table on that data - it is very easy once you get the hang of it. Here's a link that shows you how: Excel Pivot Table Filters Top X

Once you get the hang of it, it is easier to change the criteria as well. If you have data that has say another column with cities in it, then not only can you filter by states, but if you want top 5 states AND top 5 cities inside each state, then that's easy to get as well. You will need to just learn to play around with the labels/column headers.

EDIT: Based on your comment to previous answers, and don't want to be feeling stuck on Pivots, but how about this: If you want to sort by State, then row label 1 = Random Value, and row label 2 = State; sort Largest to Smallest on cell E1. RV - State

If you want to sort by Name, then row label 1 is still = Random Value, and row label 2 = Name; sort Largest to Smallest on cell E1 as well. RV - Name

If you want to filter by Name and State, then Row Label 1 = Random Value, RL2 = Name, RL3 = State. Use Sort on Row Label 1, and filters on Name and State. RV - Name&State

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

Try this formula:

=INDEX($B$1:$B$9,AGGREGATE(14,6,ROW($1:$9)/(($A$1:$A$9="NSW")*($C$1:$C$9="Dan")),ROW(1:1)))

enter image description here

The you can drag it down as many rows as you want and it will bring the next highest amount that meets the criteria.

The Row(1:1) will increase by one for every row it is drug down, thus bring the 2nd then the third highest.

Upvotes: 1

Kurt
Kurt

Reputation: 21

Sort by column B and pick the top x rows. You can use an advanced sort if you need to.

Upvotes: 0

Related Questions