• 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.

How to find top 30 numbers in the attached File

All,

I am preparing the Pareto Analysis and need to identify the highest 30-40 items in the attached file in the required format of Output.

Please let me know is there any formula or advanced filter to be used.

Regards,

Mohan
 

Attachments

  • BOQ-top 30 items.xlsx
    245.8 KB · Views: 7
HELLO,you can to Use this in Column C
Code:
=LARGE(Sales,A4)
AND This in Column B =IFERROR(INDEX('C&I Works'!$D$10:$D$884,MATCH($C4,Sales,0)),"")
 

Attachments

  • BOQ-top 30 items1.xlsx
    274.4 KB · Views: 5
vmohan1978
You have there some merged range ... yes.
But those top 30 or 40 could filter eg like below.
...after that copy & paste needed columns for output.
Screenshot 2019-12-01 at 20.15.30.png
 
I used a modern Excel function SORTBY but the merged cells and other irregularities in the table layout create problems for analysis.
= IFERROR( INDEX( SORTBY( IF( {0,1}, AMOUNT, DESCRIPTION ), AMOUNT,-1 ), SEQUENCE(30), {1,2} ), "" )
IF
extracts a two column array from the data, SORTBY sorts it descending, INDEX returns the top 30 rows of the sorted array.

The blue text are defined names but that is simply because the direct references are meaningless to me.
 
Back
Top