Split ranges(001-005) but keep the associated column

[norriz]

I have table with a range of numbers with the associated name(below), need to find a way to split ex:001-005 to 001 and table1, 002 and table1, 003 and table1. Can this be done in access or excel. Help needed urgently. Thank you

Number	Name
001-005	table1
006-010	table2

I'd like my final data to look like this-
Number Name
001 Table1
002 Table1

 

[kweaver]

That doesn't clarify what you're looking for as solution.
Is that long string of numbers in ONE cell and then you want them broken out into multiple rows?

 

[norriz]

sample.xlsx
	A	B	C	D	E	F
1	Range	Number			Range	Number
2	001-999	5	same		001	5
3	001-999	6			002	5
4	301-303, 311, 399	7			003	5
5	169-179, 195-196, 254	8
6	298, 308-309	9
7	307, 373-374	10
8	350-352, 354-359, 362	11
9	360-361, 363-364, 367-368	12
10	430-433, 437-438, 456-458	13
11	460-462, 469, 472-474, 478, 479	14
12	150-154, 156, 160-165, 260, 265, 439	16
13	304, 310, 312, 318-319	17
14	370-372, 384, 385, 421-422	18
15	375, 380-383, 386, 388, 723-724	19
16	242, 376-379, 407-409, 417-418, 425-426	20
17	620, 622, 628-631, 633-634, 636-639	21
18	003, 010-043, 045, 048, 050-067, 068-079, 085-089, 100-149, 167, 200-212, 214-241, 243-253, 255-259, 261-264, 266-268, 270-297, 299, 313-317, 320-342, 344, 346-347, 349, 365-366, 369, 387, 389-397, 398, 411-412, 415-416, 700-701, 703-708, 710-714, 716-722, 725-729, 750, 754-759	22
19	300, 305, 306	23
20	180-188	24
21	189-192, 194	25
Sheet1

 

[norriz]

Ive added the spreadsheet I am working on . I will like to breakdown the range 001-999 but still make sure they are still aligned to the number column. As you see there are ranges and single number combos. Thank you

 

[norriz]

That doesn't clarify what you're looking for as solution.
Is that long string of numbers in ONE cell and then you want them broken out into multiple rows?

That is correct, break them into multiple rows of single digits but keep the columns with them. Thanks

 

[kweaver]

Sorry I'm not getting it. If A2 is, as you show, 001-999, then you want column E to have the numbers from 001 to 999 but with column F having a 5?
Then, if A3 has that same range again, you want another 100 rows in E but with F having a 6?

Ultimately, you might have 1000s of rows??

 

[Peter_SSs]

Give this macro a try with a copy of your workbook.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the SplitEm macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub SplitEm()
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, j As Long, k As Long, num As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value2
  ReDim b(1 To Rows.Count, 1 To 2)
  For i = 1 To UBound(a)
    num = a(i, 2)
    For Each itm In Split(Replace(a(i, 1), " ", ""), ",")
      For j = Split(itm, "-")(0) To Split(itm & "-" & itm, "-")(1)
        k = k + 1
        b(k, 1) = j: b(k, 2) = num
      Next j
    Next itm
  Next i
  Range("E2:F2").Resize(k).Value = b
End Sub

 

[norriz]

Sorry I'm not getting it. If A2 is, as you show, 001-999, then you want column E to have the numbers from 001 to 999 but with column F having a 5?
Then, if A3 has that same range again, you want another 100 rows in E but with F having a 6?

Ultimately, you might have 1000s of rows??

Kweaver, that is exactly what I was looking for. thank you

 

[norriz]

Give this macro a try with a copy of your workbook.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the SplitEm macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub SplitEm()
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, j As Long, k As Long, num As Long
 
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value2
  ReDim b(1 To Rows.Count, 1 To 2)
  For i = 1 To UBound(a)
    num = a(i, 2)
    For Each itm In Split(Replace(a(i, 1), " ", ""), ",")
      For j = Split(itm, "-")(0) To Split(itm & "-" & itm, "-")(1)
        k = k + 1
        b(k, 1) = j: b(k, 2) = num
      Next j
    Next itm
  Next i
  Range("E2:F2").Resize(k).Value = b
End Sub

Peter_SSs, thanks for sharing this code. Unfortunately my excel is not giving vba option. Can I put this in access as well.

 

[kweaver]

That's a great approach, Paul...but I got an error on the line just above "End Sub"

Range("E2:F2").Resize(k).Value = b

My columns are only A and B. Am I missing something here?

MergeCells05202021.xlsm
	A	B
1	Range	Number
2	002-009	5
3	007-010	6
4	143	7
5	501-512	8
6	600-606	8
SheetA

 

[Fluff]

That's a great approach, Paul...

Who's Paul.