Sorting Cell References
Posted by JAF on September 27, 2001 4:01 AM
Hiya
I have a macro that generates a list of cell references within a worksheet that contain certain values.
The output might be:
D4
A10
A4
E23
AG12
A2
If I sort this coulmn of cell references it returns:
A10
A2
A4
AG12
D14
E23
What I WANT it to return is:
A2
A4
A10
D14
E23
AG12
Any suggestions as to how I can do this? The actual cells that are output are different each time.
JAF
Posted by Eric on September 27, 2001 5:18 AM
This one takes 3 columns
If your addresses are in col a starting in a2, then put this in b2
=IF(ISNUMBER(MID(A2,2,1)+0),1,2)
to count whether there are 1 or 2 letters in the column part
put this in c2
=IF(ISNUMBER(MID(A2,2,1)+0),LEFT(A2,1),LEFT(A2,2))
to extract just the column part of the address
put this in d2
=IF(ISNUMBER(MID(A2,2,1)+0),RIGHT(A2,LEN(A2)-1)+0,RIGHT(A2,LEN(A2)-2)+0)
to find the row#
copy these down as needed, select col a:d and go data-->sort and sort by column b ascending, then by column c ascending, then by column d ascending
HTH
Posted by or... on September 27, 2001 8:01 AM
In one column say B, use
=COLUMN(INDIRECT(A1))
and c use
=ROW(INDIRECT(A1))
and sort by B then C.
Posted by Eric on September 27, 2001 8:44 AM
better idea here! thx (NT)
