• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX LARGE

B

BBKP

New Member
Dear all

I have already posted this problem to LinkedIn Excel Black belts group for discussion. Just thought this group may have another perspective.

PROBLEM:I need to extract department names in descending order using the INDEX,LARGE,MATCH formula combo based on cost values.

For a group of departments with similar cost i.e. without unique values only the first department name is extracted. The subsequent department names are not extracted as their cost value are not unique. Please kindly advise the best formula solution for this management reporting problem.
 
BBKP said:
Dear all

I have already posted this problem to LinkedIn Excel Black belts group for discussion. Just thought this group may have another perspective.

PROBLEM:I need to extract department names in descending order using the INDEX,LARGE,MATCH formula combo based on cost values.

For a group of departments with similar cost i.e. without unique values only the first department name is extracted. The subsequent department names are not extracted as their cost value are not unique. Please kindly advise the best formula solution for this management reporting problem.
Click to expand...

I'd make the amounts unique. Perhaps in a separate column "=Round(X,2)+row(x)/1000" where x is the cell address value you are sorting on.
 
Hi Naraya

Thank you very much to Naraya and all those who have replied to the thread for your time & effort.

Naraya that was quite some EXCEL GYMNASTICS. I am sure you are one of the best!!

Please kindly give a brief writeup/description of your process of solving this problem.

I have noted you have used array formulas as part of the solution? will this not take a lot of computer processor resources in a huge workbook?

This writeup/description should help me look good when I go back to the LinkedIn Blackbelts group with the solution!!

Kind regards
 
Here is one more idea. The helper cells are marked in yellow on the input sheet. They build up a unique big number for your data. And then your formula basically remains the same.

There are no array formulas. See if it works as you need.
 

Attachments

Thank you very much to Shriva & Haseeb.

I have noted that you guys can slaughter anything Excel that is thrown at you.

I will need one week to digest all the solutions that you have given me. There after I think I will be able to start flaunting my new found knowledge.

Looking forward to working with you in the future for any other problems.

Kind regards
 
You must log in or register to reply here.
Back
Top