ranking part numbers with cost (highest to lowest)

Originally Posted by managingcrap

Thanks for replying quickly. I just have a quick question. When I change cell D12, then, I get two of the same part numbers on cells H5 and H6. I used your sheet.

If you change value in D2 to create a duplicate, then yes. You will get two of the same part numbers as you should.

AlKey, I think mc wants to return both part numbers with duplicate amounts.

This is one way.

Array-enter this formula in F3 and fill down.

Formula:

 =IFERROR(INDEX($B$3:$B$15,SMALL(IF(G3=$D$3:$D$15,ROW($B$3:$B$15)-MIN(ROW($B$3:$B$15))+1),COUNTIF($G$3:G3,G3))),"") 

Then in H3 this array formula.

Formula:

 =IFERROR(INDEX($B$3:$B$15,SMALL(IF(I3=$D$3:$D$15,ROW($B$3:$B$15)-MIN(ROW($B$3:$B$15))+1),COUNTIF($I$3:I3,I3))),"") 

and fill down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.