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 |
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 |
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 |
All times are GMT +1. The time now is 10:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com