Sort numeric range and insert gaps

[SteveKlett]

I have a collection of sequential numbers in excel, I would like to
sort them and have any gaps represented by an empty cell.
EG:
1, 3, 4, 5, 8, 9
<sorted with gaps>
[1]
[ ]
[3]
[4]
[5]
[ ]
[ ]
[8]
[9]

I can't for the life of me find a way to do this. Is it possible? I
will need to do this often with different worksheets, i would rather
not have to create VB to do it, but if that's the only option...

Thanks for any help,
Steve

 

[Guest]

Let's say:
A1:A6 =1,3,4,5,8,9
create a helper column B
B1 =1
B2 =2
select B1 & B2 and drag down to B9 (you will have 1,2,3,4,.....9)
in C1 =IF(ISNUMBER(MATCH(B1,$A$1:$A$6,0)),B1,"")
copy down from C1 to C9

 

[SteveKlett]

Let's say:
A1:A6 =1,3,4,5,8,9
create a helper column B
B1 =1
B2 =2
select B1 & B2 and drag down to B9 (you will have 1,2,3,4,.....9)
in C1 =IF(ISNUMBER(MATCH(B1,$A$1:$A$6,0)),B1,"")
copy down from C1 to C9

Very nice! Thank you for the help and the sample!
Have a good one,
Steve

PS: Great nickname, cracked me up

I have a collection of sequential numbers in excel, I would like to
sort them and have any gaps represented by an empty cell.
EG:
1, 3, 4, 5, 8, 9
<sorted with gaps>
[1]
[ ]
[3]
[4]
[5]
[ ]
[ ]
[8]
[9]

I can't for the life of me find a way to do this. Is it possible? I
will need to do this often with different worksheets, i would rather
not have to create VB to do it, but if that's the only option...

Thanks for any help,
Steve