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 -
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-
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 |