Function to grab top 2 of each category in spreadsheet

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

New Member
Joined
Mar 3, 2002
Messages
44
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

New Member
Joined
Mar 3, 2002
Messages
44
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

New Member
Joined
Mar 3, 2002
Messages
44
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.
 

Forum statistics

Threads
1,147,846
Messages
5,743,518
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top