Index / Match? Formula Help Please

[Joe Gieder]

First off let me appologize for the lengthy topic and
thank you for the help.

I have these formulas:
Initiates first:
=IF(ROW(2:2)<=ROUND(0.75*COUNT('Priced BOM'!
$T$3:$T$63),0),LARGE('Priced BOM'!$T$3:$T$63,ROW(1:1)),"")
gets extended cost

Initiates second:
=IF(F51="","",OFFSET('Priced BOM'!$T$3,MATCH(F51,'Priced
BOM'!$T$3:$T$63,0)-1,-9,1,1))
gets part number

and the results I get are:
Part Number Qty Nomenclature & Unit Cost Ext Cost
column "F"
PFSC35-38ASEB 4 DZUS FASTENER $4.25 $17.00
CM-SCE-1/4-4H-9 10 MARKER,CABLE $1.62 $16.20
NAS1473C3 1 NUTPLATE $2.60 $15.60
MS21059L4K 7 NUTPLATE $0.90 $15.30
MS25274-3 2 END CAP, $0.40 $15.20
MS27039-4-14 7 SCREW $0.11 $15.07
MS24693C28 8 SCREW $0.03 $15.00
MS24693C28 8 SCREW $0.03 $15.00
MS24693C28 8 SCREW $0.03 $15.00
NAS1149CN816R 8 WASHER $0.25 $12.50
NAS1149CN816R 8 WASHER $0.25 $12.50
MS21044N06 4 NUT $0.10 $10.00
MS21044N06 4 NUT $0.10 $10.00

The problem I have is that if the extended is the same
for several different part numbers I always get the first
match. Is there a way if the extended cost is the same
for several different part numbers I can get all the
different ones and not always have the same? I know with
Match and using 0 it finds the first one that's an exact
match and stops there, not what I need. I can't sort my
source information because I'm always adding to it. Is
there any hope.

Thanks in advance for your help
Joe

 

[macropod]

Hi Joe,

Suppose you have list of parts in A2:A15, and prices in B2:B15, and you want
to rank the parts by price. What you could do is put:
=LARGE(B$2:B$15,ROW()-1)
in D2, and:
=IF(D2=D1,INDEX(A$2:A$15,MATCH(D2,OFFSET(B$2,MATCH(E1,A$2:A$15,0),0,ROW(B$15
)-MATCH(E1,A$2:A$15,0),1),0)+MATCH(E1,A$2:A$15,0)),INDEX(A$2:A$15,MATCH(D2,B
$2:B$15,0)))
in E2 and copy both formulae down to row 15, or less rows if you only want
to find out, say, the top 5.

This will report each successive record that matches a duplicated price.
I'll let you figure out how it works but, basically, it checks column D for
a duplicate and, if one is found, finds the corresponding row and searches
only from there to the end of the range for the next match.

I'm not sure if this is quite what you're after, but it's probably close
enough to get you going.

Cheers