Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort numeric range and insert gaps
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort numeric range and insert gaps
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort numeric range and insert gaps
On Jan 31, 7:07 pm, Teethless mama
wrote: 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 ;) " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert a formula based on a range | Excel Discussion (Misc queries) | |||
sorting data on protected worksheet | Excel Discussion (Misc queries) | |||
Changing the range of a table sort | New Users to Excel | |||
Auto insert a range | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |