LARGE with IF Statement?

[aeslabby]

Hi there!

I need to create a "Top 5" list based on two columns. The example is as attached (the real list is approx 22 000 lines):

I need to find the top 5 largest quantites (column A) for each flag catagory (column D) and include the part number and description. I have tried a pivot table and had some issues. Also tried a LARGE(IF...) type formula and still no luck. Does anyone have any ideas of what is the best way to do this??? I have a very short timeline and am struggling!

Thanks!

[bill74]

Hi Could you not just try a quick macro to do this for you. Are columns A and D already sorted to matcheach other.

If so Right click and View Code for Worksheet tab and copy this into it hit save. Go back to your spreadsheet and hit tool/macro/macros/run

Please Login or Register  to view this content.

KR

[NBVC]

bill74,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[bill74]

Sorry, will do so from now on. :-)

KR

Bill

[aeslabby]

I guess I am a bit confused as to what the lines in your macro refer to. I am not great with that aspect of Excel, so apologies for the beginner like questions! Can you provide a bit more detail please?

Thanks!

[NBVC]

Here is a possibility....


You would have to download and install a free addin called Morefunc.xll from here: http://download.cnet.com/Morefunc/30...-10423159.html to use the EVAL() function that automatically inserts and evaluated the condition from column D

[aeslabby]

I unfortunately can't as I am at work and we are blocked from downloading addins.

Thanks anyways!

[NBVC]

Hmm... I wonder...

That addin allows one to embed the functions into the workbook so others don't need to download...

Can you check the attached and see if you get results or NAME! errors...

If you get results, you now have the Morefunc.xll embedded in the workbook....

but you will need to transfer your original stuff here for you to use the functioins..

[NBVC]

Well, i guess i didn't think about it, but if you can't download the addins, you probably can't download the workbook samples from here....

[martindwilson]

i dont quite get it ,you only have 3 conditions

<=5 >14 <=14
if so you can use a large if array with index/match

[NBVC]

Martin,

As we have learned, hopefully, it is better not to assume to simplistic, I didn't want to assume that there was only those 3 variations and so I based my solution on the fact that they can change on the fly and have indeed as many conditions as there are lines.

[martindwilson]

well at least it may prompt a little clarification

[aeslabby]

Thanks everyone!