Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel sorting 1

Status
Not open for further replies.

Andrzejek

Programmer
Jan 10, 2006
8,489
5
38
US
If I have data in two columns (Number and Letter), how can I sort it to get this order:

[pre]
Number Letter
1
2 [blue]
3
3 A
3 B[/blue]
4
4 A
5
5 B
[/pre]
Number without the Letter would be first, then A and B, as:[blue][pre]
3
3 A
3 B[/pre][/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
You need a helper column.

With your data in a structured table, add the helper column with this formula: =IF(ISBLANK([@letter]),0,CODE([@letter]))

Then:
Filter on number to show only 3
Sort on the helper column smallest to largest.

[pre]number letter helper
3 0
3 A 65
3 B 66
[/pre]

Alternately, you could use conditional formatting to color the letter cells blank and not blank, then sort by color, then by value.
 
Since these are my helper columns anyway, what I did is have a 1 in a Letter column:

[pre]
Number Letter
1 [red]1[/red]
2 [red]1[/red]
3 [red]1[/red]
3 A
3 B
4 [red]1[/red]
4 A
5 [red]1[/red]
5 B[/pre]

since 1 is prior to A when sorting :)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top