CODEWITHSUNDEEP
excelexcel-formulavba
epickett
epickett

Reputation: 11

Get highest values across columns

I have a worksheet with colums of names and amounts. The names are in (C2:C33,J2:J33,Q2:Q33,X2:X33). The amounts are in (G2:G33,N2:N33,U2:U33,AB2:AB33).

What I'm trying to do is find the highest 8 amounts, with the coresponding name. Meaning, for example - the name in C3 corresponds to the amount in G3, the name in J3 corresponds to the amount in N3, etc.

Using:

=LARGE((G2:G33,N2:N33,U2:U33,AB2:AB33),1)

I am able to find the top 8 values (by changing the 1 to 2, 3, 4, etc.). I couldn't figure out how to 'bring along' the name with that, so I kept searching. I found an explanation that gave me this:

=INDEX($C$2:$C$33,MATCH(1,INDEX(($G$2:$G$33=LARGE($G$2:$G$33,ROWS(N$36:N36)))*(COUNTIF(N$36:N36,$C$2:$C$33)=0),),0))

Copied down 8 cells will give me the names of the highest 8 combinations, but only from that first grouping of column C using column G values.

I've been searching for how to do this for the past few weeks, and I've run out of ideas. I don't know if I can modify the first formula to also grab the names, or the second formula to go across the other 3 corresponding groups. Once I get the top 8 names and amounts on the first sheet, I need to compare those to the top 8 on more than 100 other sheets to get the top 8 overall.

Does that make sense, and is it even possible? Do I need to use VBA? I'm running Excel 2010, but I don't know if the solution will be version specific. A thousand thanks if someone can figure this one out for me!

Upvotes: 1

Views: 632

Answers (1)

AJY
AJY

Reputation: 188

Not completely sure what you are asking for but here is a set of normal excel code that will provide you with the first instance of the highest value and return it's corresponding cell.

=INDEX(C2:C33,MATCH(MAX(G2:G33),G2:G33,0),1)

If you are asking for the 8 highest values in that column... you would use 

=INDEX(C2:C33,MATCH(LARGE(G2:G33,[replace with 1-8]),G2:G33,0),1)

Edit: Of course it won't work if there are two numbers with the same value. In that case I would use VBA, pull the numbers into a 2D array and reorder the array based on the values in column G.

Upvotes: 1

Related Questions