Rank Data without duplicates

My problem is that I have a list of 20K agreement numbers. Each agreement has a supplier name and spend associated with the agreement. Every agreement has a unique ID however one supplier might have multiple agreement numbers.

I would like to have a table created that has the top suppliers based on spend. Top agreement by spend I can do, but when it comes to supplier where I have to group the spend, that's where I get messed up.

I however don't want a vlookup because there are filterings on top of that, which is not shown in the example spreadsheet attached.

Grouping Data.xls

Daddylonglegs - thanks, it worked.

MarvinP - my mistake, it's not vlookup that I can't use, it's Pivot Table that I prefer not to use. The reason is that there's an additional column called "Commodity" and the data changes everytime a different commodity is picked. So I would need to refresh the pivot table everytime I change a commodity and I prefer not to do that.

Originally Posted by daddylonglegs

Try this "array formula" in B12 copied down

=INDEX(B$2:B$8,MATCH(C12,IF(COUNTIF(B$11:B11,B$2:B$8)=0,SUMIF(B$2:B$8,B$2:B$8,C$2:C$8))))

and in C12

=MAX(IF(COUNTIF(B$11:B11,B$2:B$8)=0,SUMIF(B$2:B$8,B$2:B$8,C$2:C$8)))

both confirmed with CTRL+SHIFT+ENTER, see attached

Daddylonglegs - I tried to do an "IF" statement into your formula above for commodity and it didn't quite worked. Can you help me out again?
Revised Grouping Data_daddy.xls