using Large function to sort a column of numbers

grantjw

New Member
Joined
Dec 17, 2012
Messages
12
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?

NameNetGrossAll SortSortASortBSortCSortD
A508-200.129101.685
C107-34.586173.982
D241-64.78465.606
A449-171.53751.4022
B44565.606-13.6608
A411-157.414-15.9449
D251-44.4871-16.5229
B359-22.8478-19.5838
C96-27.9752-22.8478
A397101.685-23.3072
A402-152.452-23.8842
C89-23.3072-27.9752
A422-141.114-33.4352
B44451.4022-34.5861
D233-33.4352-42.8249
A450-91.0501-44.4871
C84-16.5229-64.784
A51073.982-91.0501
C85-19.5838-141.114
B454-13.6608-152.452
B408-23.8842-157.414
C86-15.9449-171.537
D229-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

Well-known Member
Joined
Mar 9, 2012
Messages
951
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,721
Office Version
2010
Platform
Windows
Why not just sort by Name and then by Gross?
 

grantjw

New Member
Joined
Dec 17, 2012
Messages
12
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?


ABCD
A508-200.129 101.68573.982-23.8842-15.9449-42.8249
C107-34.5861 73.982-91.0501-13.6608-19.5838-33.4352
D241-64.784 65.606-141.11451.4022-16.5229-44.4871
A449-171.537 51.4022-152.452-22.8478-23.3072-64.784
B44565.606 -13.6608101.68565.606-27.9752
A411-157.414 -15.9449-157.414 -34.5861
D251-44.4871 -16.5229-171.537
B359-22.8478 -19.5838-200.129
C96-27.9752 -22.8478
A397101.685 -23.3072
A402-152.452 -23.8842
C89-23.3072 -27.9752
A422-141.114 -33.4352
B44451.4022 -34.5861
D233-33.4352 -42.8249
A450-91.0501 -44.4871
C84-16.5229 -64.784
A51073.982 -91.0501
C85-19.5838 -141.114
B454-13.6608 -152.452
B408-23.8842 -157.414
C86-15.9449 -171.537
D229-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

Well-known Member
Joined
Mar 9, 2012
Messages
951
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

New Member
Joined
Dec 17, 2012
Messages
12
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.
 

Forum statistics

Threads
1,085,071
Messages
5,381,543
Members
401,744
Latest member
Schoen

Some videos you may like

This Week's Hot Topics

Top