using Large function to sort a column of numbers

[grantjw]

I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.

I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.

I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?

Name Net Gross All Sort SortA SortB SortC SortD A 508 -200.129 101.685 C 107 -34.5861 73.982 D 241 -64.784 65.606 A 449 -171.537 51.4022 B 445 65.606 -13.6608 A 411 -157.414 -15.9449 D 251 -44.4871 -16.5229 B 359 -22.8478 -19.5838 C 96 -27.9752 -22.8478 A 397 101.685 -23.3072 A 402 -152.452 -23.8842 C 89 -23.3072 -27.9752 A 422 -141.114 -33.4352 B 444 51.4022 -34.5861 D 233 -33.4352 -42.8249 A 450 -91.0501 -44.4871 C 84 -16.5229 -64.784 A 510 73.982 -91.0501 C 85 -19.5838 -141.114 B 454 -13.6608 -152.452 B 408 -23.8842 -157.414 C 86 -15.9449 -171.537 D 229 -42.8249 -200.129 <COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 48pt" span=5 width=64><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[pplstuff]

After pasting in your data to A1, and changing "SortX" to "X"

F2:
=IF(ROWS($F$1:F1)>COUNTIF($A$2:$A$24,F$1),"",INDEX($C$1:$C$24,LARGE(IF($A$1:$A$24=F$1,ROW($C$1:$C$24)),ROWS($F$1:F1))))
Confirm with control + shift + enter

 

[shg]

Why not just sort by Name and then by Gross?

 

[grantjw]

Thanks for the quick reply. I did what you recommended. I got results close to what I expected. for sort on "A" I expected : 101.685, 73.982, -91.0501 .... Did I do something wrong?

					A	B	C	D
A	508	-200.129		101.685	73.982	-23.8842	-15.9449	-42.8249
C	107	-34.5861		73.982	-91.0501	-13.6608	-19.5838	-33.4352
D	241	-64.784		65.606	-141.114	51.4022	-16.5229	-44.4871
A	449	-171.537		51.4022	-152.452	-22.8478	-23.3072	-64.784
B	445	65.606		-13.6608	101.685	65.606	-27.9752
A	411	-157.414		-15.9449	-157.414		-34.5861
D	251	-44.4871		-16.5229	-171.537
B	359	-22.8478		-19.5838	-200.129
C	96	-27.9752		-22.8478
A	397	101.685		-23.3072
A	402	-152.452		-23.8842
C	89	-23.3072		-27.9752
A	422	-141.114		-33.4352
B	444	51.4022		-34.5861
D	233	-33.4352		-42.8249
A	450	-91.0501		-44.4871
C	84	-16.5229		-64.784
A	510	73.982		-91.0501
C	85	-19.5838		-141.114
B	454	-13.6608		-152.452
B	408	-23.8842		-157.414
C	86	-15.9449		-171.537
D	229	-42.8249		-200.129

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" span=4 width=73><TBODY>
</TBODY>

 

[pplstuff]

Apologies, I was in a hurry when I wrote that.

This should do the trick, one caveat, this will not handle duplicate (ex. if there are two A 100s)

=IF(ROWS($F$1:F1)>COUNTIF($A$2:$A$24,F$1),"",INDEX($C$2:$C$24,MATCH(LARGE(IF($A$2:$A$24=F$1,$C$2:$C$24),ROWS($F$1:F1)),$C$2:$C$24,0)))
confirm with control + shift + enter

 

[grantjw]

SHG, Was looking for a function to do this automatically. My data will be changinig each month did not want user to do sorting or changing the spreadsheet.

 

[grantjw]

That did it. Thanks!