Create a new row/s for a multiple value/s in a cell

[htou]

Hello folks,

Looking for a solution to the following via a script or excel formulas/functions etc and im a complete excel noob.

I have a worksheet that contains about 2,300 lines/rows of property data. The data in Column H is my issue. What is consistent in this column is the comma symbol. If there is a comma present, then there is more than one value. I need each value after the comma to have its own row after the subject/original cell.

Once a new row is created for the value/s, the data in the row containing the multiple value are duplicated to the empty cells of the newly created row. Hope that makes sense, hopefully my sample below paints a better picture.

Column H, cells containing multiple values represented by a comma -

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Ref	Type	LAD	Tenure Ref	LG	DO	Reg	Lot_Plan	Area (ha)
2	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	15PT41,19PT278,20PT279,42PT279,38PT280,14PT41	2923.36
3	17645108	GHPL	37	3240	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6PLA4011	558.466
4	17645109	GHPL	37	3241	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	13TT228,17TT228	2577.089
5	17645113	GHPL	37	3245	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	5BU26,2BU17	11029.66
6	17645114	GHPL	37	3246	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	1BU17	6623.616
7	17645115	GHPL	37	3250	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	21DSN114	1079.904
8	17645116	GHPL	37	3251	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	12DSN647,6DSN647	4752.489
9	17645125	GHPL	37	3269	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	27DSN836447	4311.353
10	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	57PT25,55PT25,56PT25,58PT25	1572.988
11	17645128	GHPL	37	3275	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	69DSN144	1200.222
12	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	8PT346,6PT23,7PT347	1948.911
Sheet1

What it should look like after a solution is deployed, multiple values that were in specific cells in Column H now have a new row and information duplicated into the empty fields-

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Ref	Type	LAD	Tenure Ref	LG	DO	Reg	Lot_Plan	Area (ha)
2	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	15PT41	2923.36
3	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	19PT278	2923.36
4	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	20PT279	2923.36
5	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	42PT279	2923.36
6	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	38PT280	2923.36
7	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	14PT41	2923.36
8	17645108	GHPL	37	3240	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6PLA4011	558.466
9	17645109	GHPL	37	3241	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	13TT228	2577.089
10	17645109	GHPL	37	3241	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	17TT228	2577.089
11	17645113	GHPL	37	3245	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	5BU26	11029.66
12	17645113	GHPL	37	3245	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	2BU17	11029.66
13	17645114	GHPL	37	3246	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	1BU17	6623.616
14	17645115	GHPL	37	3250	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	21DSN114	1079.904
15	17645116	GHPL	37	3251	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	12DSN647	4752.489
16	17645116	GHPL	37	3251	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6DSN647	4752.489
17	17645125	GHPL	37	3269	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	27DSN836447	4311.353
18	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	57PT25	1572.988
19	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	55PT25	1572.988
20	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	56PT25	1572.988
21	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	58PT25	1572.988
22	17645128	GHPL	37	3275	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	69DSN144	1200.222
23	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	8PT346	1948.911
24	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6PT23	1948.911
25	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	7PT347	1948.911
Sheet2

 

[tzw1378]

The creation of your macro could be tedious but I think it's possible. A little pseudo-code to begin:

1) Need to count the number of commas in each H column cell. The syntax below is admittedly "rough".

Sub CountCommas()
    For Each rRow In iTotalHRows
        For iChar = 1 To Len(rRow)
            If Mid(rRow, iChar, 1) = "," Then
                iCount = iCount + 1
            End If
        Next iChar
        If iCount = 0 Then
        Else
            'Insert number of rows equal to iCount
        End If
    Next rRow
End Sub

2) Once number of commas known, insert number of rows below equal to the number of commas.
3) Parse out the words in between the commas so they can then be inserted into the new (now blank) rows.
4) Copy down all the duplicate data.

This will get you started. Happy to continue if need be, just write back.

 

[hiker95]

htou,

Sample worksheets:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Ref	Type	LAD	Tenure Ref	LG	DO	Reg	Lot_Plan	Area (ha)
2	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	15PT41,19PT278,20PT279,42PT279,38PT280,14PT41	2923.36
3	17645108	GHPL	37	3240	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6PLA4011	558.466
4	17645109	GHPL	37	3241	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	13TT228,17TT228	2577.089
5	17645113	GHPL	37	3245	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	5BU26,2BU17	11029.66
6	17645114	GHPL	37	3246	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	1BU17	6623.616
7	17645115	GHPL	37	3250	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	21DSN114	1079.904
8	17645116	GHPL	37	3251	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	12DSN647,6DSN647	4752.489
9	17645125	GHPL	37	3269	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	27DSN836447	4311.353
10	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	57PT25,55PT25,56PT25,58PT25	1572.988
11	17645128	GHPL	37	3275	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	69DSN144	1200.222
12	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	8PT346,6PT23,7PT347	1948.911
13
Sheet1

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Ref	Type	LAD	Tenure Ref	LG	DO	Reg	Lot_Plan	Area (ha)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sheet2

After the macro in Sheet2:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Ref	Type	LAD	Tenure Ref	LG	DO	Reg	Lot_Plan	Area (ha)
2	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	15PT41	2923.36
3	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	19PT278	2923.36
4	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	20PT279	2923.36
5	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	42PT279	2923.36
6	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	38PT280	2923.36
7	17645107	GHPL	37	3239	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	14PT41	2923.36
8	17645108	GHPL	37	3240	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6PLA4011	558.466
9	17645109	GHPL	37	3241	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	13TT228	2577.089
10	17645109	GHPL	37	3241	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	17TT228	2577.089
11	17645113	GHPL	37	3245	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	5BU26	11029.66
12	17645113	GHPL	37	3245	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	2BU17	11029.66
13	17645114	GHPL	37	3246	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	1BU17	6623.616
14	17645115	GHPL	37	3250	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	21DSN114	1079.904
15	17645116	GHPL	37	3251	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	12DSN647	4752.489
16	17645116	GHPL	37	3251	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6DSN647	4752.489
17	17645125	GHPL	37	3269	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	27DSN836447	4311.353
18	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	57PT25	1572.988
19	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	55PT25	1572.988
20	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	56PT25	1572.988
21	17645127	GHPL	37	3274	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	58PT25	1572.988
22	17645128	GHPL	37	3275	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	69DSN144	1200.222
23	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	8PT346	1948.911
24	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	6PT23	1948.911
25	17645130	GHPL	37	3279	CENTRAL HIGHLANDS RC	EMERALD	CENTRAL WEST	7PT347	1948.911
26
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Expand_Lot_Plan()
' hiker95, 12/03/2014, ME821931
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, nr As Long
Dim s, i As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.Range("A2", w2.Range("A2").End(xlDown)).Resize(, 9).ClearContents
nr = 1
With w1
  For Each c In .Range("H2", .Range("H" & Rows.Count).End(xlUp))
    If InStr(c, ",") Then
      nr = nr + 1
      s = Split(c, ",")
      w2.Cells(nr, 1).Resize(UBound(s) + 1, 7).Value = .Cells(c.Row, 1).Resize(, 7).Value
      w2.Cells(nr, 8).Resize(UBound(s) + 1).Value = Application.Transpose(s)
      w2.Cells(nr, 9).Resize(UBound(s) + 1).Value = .Cells(c.Row, 9).Value
      nr = nr + UBound(s)
    Else
      nr = nr + 1
      w2.Cells(nr, 1).Resize(, 9).Value = .Cells(c.Row, 1).Resize(, 9).Value
    End If
  Next c
End With
With w2
  .UsedRange.Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Expand_Lot_Plan macro.

 

[htou]

@tzw1378 thanks for the suggestion, im assuming the syntax is inserted into a new Module for the sheet? I tried this and i couldn't see any changes. More than likely im executing it wrong.

@hiker95 thank you. Your solution worked perfectly on the sample. Ill deploy it onto the larger set of data when am at work today.

Thank you again to you both for the help, much appreciated.

 

[hiker95]

htou,

@hiker95 thank you. Your solution worked perfectly on the sample.

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

Ill deploy it onto the larger set of data when am at work today.

If the macro does not work correctly on your larger set of data, then:

We will have to see your actual raw data.

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.