Sorting

[vwchurch1955]

I have 3 columns of single digits and I want to do a multi-row sort. I
want to sort them in such a way that the smallest digit is in the first
column except if 2 digits match. If 2 digits match I want the matching
digits to be in the first 2 columns.

Example

Before sort After sort
978 789
434 443
201 012
811 118
645 456
737 773

Can this be done?
Thank you for your help.

Joe

 

[Peo Sjoblom]

It's not as easy as you might think, one possible way is to use help rows
with formulas
Assume you numbers start in A2 going to C2

in an empty row you can put this formula and copy first across 3 columns and
then down

=IF(SUMPRODUCT(($A2:$C2<>"")/COUNTIF($A2:$C2,$A2:$C2&""))=3,SMALL($A2:$C2,CO
LUMN(A2)),INDEX($A2:$C2,,MATCH(LARGE(COUNTIF($A2:$C2,$A2:$C2),COLUMN(A2)),CO
UNTIF($A2:$C2,$A2:$C2),0)))

it is an array formula and has to be entered with ctrl + shift & enter

sample can be email upon request

I am sure it could be simplified but this worked for all situations I could
come up with using numbers.
It requires some amendments if it was to work with letters,,




--

Regards,

Peo Sjoblom

I have 3 columns of single digits and I want to do a multi-row sort. I
want to sort them in such a way that the smallest digit is in the first
column except if 2 digits match. If 2 digits match I want the matching
digits to be in the first 2 columns.

Example

Before sort After sort
978 789
434 443
201 012
811 118
645 456
737 773

Can this be done?
Thank you for your help.

Joe



------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[CLR]

You could do a Text to Columns, to spread your three digets into columns A,
B, and C, then in column D you could put this formula and copy down.......

=IF(LARGE(A1:C1,1)=LARGE(A1:C1,2),(LARGE(A1:C1,1)&LARGE(A1:C1,2)&SMALL(A1:C1
,1)),SMALL(A1:C1,1)&SMALL(A1:C1,2)&LARGE(A1:C1,1))

Note, this will turn your digets into text, which is necessary if you're to
preserve the leading Zeros which will result, otherwise you can add a *1 to
the end of the formula to go back to numbers.

Vaya con Dios,
Chuck, CABGx3

I have 3 columns of single digits and I want to do a multi-row sort. I
want to sort them in such a way that the smallest digit is in the first
column except if 2 digits match. If 2 digits match I want the matching
digits to be in the first 2 columns.

Example

Before sort After sort
978 789
434 443
201 012
811 118
645 456
737 773

Can this be done?
Thank you for your help.

Joe



------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements