How to find top 30 numbers in the attached File

[vmohan1978]

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

 

[herofox]

HELLO,you can to Use this in Column C

=LARGE(Sales,A4)

AND This in Column B =IFERROR(INDEX('C&I Works'!$D$10:$D$884,MATCH($C4,Sales,0)),"")

 

[vletm]

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.

 

[Peter Bartholomew]

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.