Problems with duplicates nth largest formula

[cjmueller08]

Hello Gurus,
I have a customer database segmented and I'm using the large function to display top customers. I'm getting the top results fine but unfortunately there are duplicates and I'm having issues displaying the customer name next to the $ amounts. For the duplicates it's displaying the same name as the first value.

IAN TOWELL	$315
CITY OF MADISON	$304
AV CONSTRUCTION	$295
AV CONSTRUCTION	$295
AV CONSTRUCTION	$295
RESIDENTIAL DESIGN	$288

Formula for finding the top dollar amounts:
=LARGE(Segment!$Q$21:$Q$9045,ROW(S51)-ROW(S$12))

Formula for finding the customer name that corresponds to dollar amount:
=INDEX(Segment!$D$21:$D$9045,SMALL(IF(Segment!$Q$21:$Q$9045=S51,ROW(Segment!$Q$21:$Q$9045)-ROW(Segment!$Q$20)),COUNTIF($D$13:D51,D51)))

Thanks in advance for your help!

 

[Fluff]

What cells are those formulae in?

 

[cjmueller08]

IAN TOWELL R51	$315 S51
CITY OF MADISON R52	$304 S52
AV CONSTRUCTION R53	$295 S53
AV CONSTRUCTION R54	$295 S54
AV CONSTRUCTION R55	$295 S55
RESIDENTIAL DESIGN R56	$288 S56

Formula for finding the top dollar amounts: Formula S51
=LARGE(Segment!$Q$21:$Q$9045,ROW(S51)-ROW(S$12))

Formula for finding the customer name that corresponds to dollar amount: Formula for R51
=INDEX(Segment!$D$21:$D$9045,SMALL(IF(Segment!$Q$21:$Q$9045=S51,ROW(Segment!$Q$21:$Q$9045)-ROW(Segment!$Q$20)),COUNTIF($D$13:D51,D51)))

 

[Fluff]

Are those the first cells with those formulae? or do they start in S13 & R13?

 

[mikerickson]

One way to do this would be to use LARGE on an array of value+row

=10000*MOD(LARGE(Segment!$D$21:$D$9045+ROW(Segment!$D$21:$D$9045)/10000,2), 1)

is the row number of the 2'nd largest value in your data set.

The name associated with that value would be

=INDEX(Segment!$C:$C, 10000*MOD(LARGE(Segment!$D$21:$D$9045+ROW(Segment!$D$21:$D$9045)/10000,2), 1), 1)

 

[cjmueller08]

Sorry, I'll just post a picture of the spreadsheet. Yellow highlighted area is the area I was talking about:

 

[Fluff]

Try

=INDEX(Segment!$D$21:$D$9045,SMALL(IF(Segment!$Q$21:$Q$9045=S51,ROW(Segment!$Q$21:$Q$9045)-ROW(Segment!$Q$20)),COUNTIF($S$13:S51,S51)))

 

[cjmueller08]

Thanks Fluff, that worked great. Silly oversight on my part.......

 

[Fluff]

You're welcome & thanks for the feedback.