Function to grab top 2 of each category in spreadsheet

[vantilian]

Hello all. I will use a generic scenario for simplicity of conveying this issue. I have a spreadsheet laid out as follows (Column A=Classification; Column B= Customer #; Column C = Amount):

ColA ColB ColC
Class Cust# Amount
A 245 1.23
A 381 2.15
A 219 3.01
B 191 2.22
B 355 2.78
B 209 3.54


I've got this spreadsheet sorted by Class, then by Amount. In the real spreadsheet, there are about 20 Classes, and Class has several hundred rows. Each time the spreadsheet is updated, there is a different set of customers in the top entries. Since the spreadsheet is updated with the correct Sorting of the data, I am looking for a way to pull out the top 2 in each Class on a Summary Sheet, which would update each time the primary spreadsheet is updated. I would obviously need some function that looks at the Class Column A and grabs the records related to the first two items when the Class changes. Hope I have presented this clearly. Please let me know if I need to clarify any points further. I appreciate the help.

Russell

 

[just_jon]

Enter your classes in column A

In column B for the 1st entry, enter-

=INDEX('Other Sheet'!B:B,MATCH(A1,'Other Sheet'!A:A,0),0)

In column C, to fetch the 2nd etry -

=INDEX('Other Sheet'!B:B,MATCH(A1,'Other Sheet'!A:A,0)+1,0)

Copy B/C down as far as you've got Classes.

 

[vantilian]

Works perfectly! Thank you so much. Couldn't be happier. Cheers!

 

[vantilian]

Hate to continue to be a nuisance, but is there a way to enhance this formula to return a "blank" cell when it encounters a "blank" cell. Presently it is returning a zero into the Summary Sheet for the blank cells it is encountering on the Primary Sheet. I am assuming I would insert an ISBLANK function somewhere, but I can't seem to get it in the right place or format. Oh, I'll catch on one day Thanks in advance for any help.

Russell

 

[vantilian]

Add ISBLANK? to INDEX function to return blank instead of 0

The problem detailed above, was resolved with the following formula. Much thanks to "just_jon" for sending in this formula:

=INDEX('Other Sheet'!B:B,MATCH(A1,'Other Sheet'!A:A,0),0)

However, I'm finding that when this formula encounters a blank cell in the "other spreadsheet" that it is looking to, it returns a zero into the Summary Spreadsheet where I have the formula. Is there a way to add in what I assume would be an ISBLANK function within this formula to return a "blank" instead of a "0".

Thanks for looking at this problem. I am very thankful for any help provided.

Russell

 

[vantilian]

I believe I have resolved this by searching the MrExcel board on ISBLANK and INDEX. Found a lot of suggestions that I believe will work. Thank you.