Splitting mutiple rows with delimited (but matching columns) into new unique rows

M

mavwayne1

New Member
Joined
Nov 27, 2015
Messages
3
Good evening

I have some Rows which are delimited in a number of columns by "," they all match in sequence
so the first value matches the first value in another column etc.
I need to split this into individual rows, the new row has the existing data and a line for each value with no separator, perhaps AN EXAMPLE OF DATA WILL HELP

This is what I am looking for a new line for each entry

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169, bgcolor: transparent"]AS-N-XG-20201-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52, bgcolor: transparent"]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl43106, width: 73, bgcolor: transparent"]C168
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl43106, width: 71, bgcolor: transparent"]FA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 66"]
<tbody>[TR]
[TD="class: xl43106, width: 88, bgcolor: transparent"]C
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]N
[/TD]
[TD]
[TABLE="width: 191"]
<tbody>[TR]
[TD="width: 255, bgcolor: transparent"]2153-1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143, bgcolor: transparent"]Open
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 392, bgcolor: transparent"]Created
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 236"]
<tbody>[TR]
[TD="width: 314, bgcolor: transparent"]New revision(s)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 93"]
<tbody>[TR]
[TD="width: 124, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 86"]
<tbody>[TR]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169"]AS-N-XG-20204-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52"]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl43106, width: 73"]C168
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl43106, width: 71"]XG
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 66"]
<tbody>[TR]
[TD="class: xl43106, width: 88"]D
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl43106, width: 85"]N
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 191"]
<tbody>[TR]
[TD="width: 255"]2137-3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143"]Issued
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 392"]DCN Ready for Issuing
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 236"]
<tbody>[TR]
[TD="width: 314"]New revision(s)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 86"]
<tbody>[TR]
[TD="width: 115"]Yes
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169"]AS-N-XG-20204-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52"]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]C168
[/TD]
[TD]XG
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl43106, width: 85"]D
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]R
[/TD]
[TD]1153-3
[/TD]
[TD]Issued
[/TD]
[TD]DCN Issued
[/TD]
[TD]New revision(s)
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]AS-N-XG-35541-02
[/TD]
[TD]1
[/TD]
[TD]C168
[/TD]
[TD]XG
[/TD]
[TD]C
[/TD]
[TD]N
[/TD]
[TD]2137-2
[/TD]
[TD]Open
[/TD]
[TD]DCN Ready for Issuing
[/TD]
[TD]Markup(s) on existing revision
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]AS-N-XG-35541-02
[/TD]
[TD]1
[/TD]
[TD]C168
[/TD]
[TD]XG
[/TD]
[TD]C
[/TD]
[TD]N
[/TD]
[TD]1841-8
[/TD]
[TD]Open
[/TD]
[TD]DCN Issued
[/TD]
[TD]Markup(s) on existing revision
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AS-N-XG-35541-02
[/TD]
[TD]1
[/TD]
[TD]C168
[/TD]
[TD]XG
[/TD]
[TD]C
[/TD]
[TD]N
[/TD]
[TD]1153-1
[/TD]
[TD]Open
[/TD]
[TD]DCN Issued
[/TD]
[TD]Markup(s) on existing revision
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AS-N-XG-35542-02
[/TD]
[TD]1
[/TD]
[TD]C168
[/TD]
[TD]XG
[/TD]
[TD]D
[/TD]
[TD]N
[/TD]
[TD]2137-2
[/TD]
[TD]Open
[/TD]
[TD]DCN Ready for Issuing
[/TD]
[TD]Markup(s) on existing revision
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]AS-N-XG-35542-02
[/TD]
[TD]1
[/TD]
[TD]C168
[/TD]
[TD]XG
[/TD]
[TD]D
[/TD]
[TD]N
[/TD]
[TD]1841-8
[/TD]
[TD]Open
[/TD]
[TD]DCN Issued
[/TD]
[TD]Markup(s) on existing revision
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



The comma always matches the data in each column so first data separated by comma in column G matches First data separated by a comma in column I etc.
if no comma separation it just copies the data
if it helps I only have comma separations in columns F,G,I,J,K,L
(K & L may be problematic as it can be no data but the "," is still there as the data was compiled

I have so many examples of splitting it by one column but not by a few

So if anybody fancies a challenge, can anybody help me, I presume this can only be done with VBA, it might take quite a bit more work by formula's
Please ignore the bad formatting of the table, I need a better way of uploading data

Original data below
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169, bgcolor: transparent"]AS-N-XG-20201-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52, bgcolor: transparent"]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl43106, width: 73, bgcolor: transparent"]C168
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl43106, width: 71, bgcolor: transparent"]FA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 66"]
<tbody>[TR]
[TD="class: xl43106, width: 88, bgcolor: transparent"]C
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl43106, width: 85, bgcolor: transparent"]N
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 191"]
<tbody>[TR]
[TD="width: 255, bgcolor: transparent"]2153-1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143, bgcolor: transparent"]Open
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 392, bgcolor: transparent"]Created
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 236"]
<tbody>[TR]
[TD="width: 314, bgcolor: transparent"]New revision(s)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 93"]
<tbody>[TR]
[TD="width: 124, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 86"]
<tbody>[TR]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169"]AS-N-XG-20204-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52"]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl43106, width: 73"]C168
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl43106, width: 71"]XG
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 66"]
<tbody>[TR]
[TD="class: xl43106, width: 88"]D
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl43106, width: 85"]N, R
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 191"]
<tbody>[TR]
[TD="width: 255"]2137-3, 1153-3
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143"]Issued
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143"]
[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 392"]DCN Ready for Issuing, DCN Issued
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 236"]
<tbody>[TR]
[TD="width: 314"]New revision(s), New revision(s)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 93"]
<tbody>[TR]
[TD="width: 124"],
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 86"]
<tbody>[TR]
[TD="width: 115"]Yes,Yes
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169, bgcolor: transparent"]AS-N-XG-35541-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52, bgcolor: transparent"]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl43106, width: 73, bgcolor: transparent"]C168
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl43106, width: 71, bgcolor: transparent"]XG
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 66"]
<tbody>[TR]
[TD="class: xl43106, width: 88, bgcolor: transparent"]C
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl43106, width: 85, bgcolor: transparent"]N, N, R
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 191"]
<tbody>[TR]
[TD="width: 255, bgcolor: transparent"]2137-2, 1841-8, 1153-1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143, bgcolor: transparent"]Open
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 392, bgcolor: transparent"]DCN Ready for Issuing, DCN Issued, DCN Issued
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 236"]
<tbody>[TR]
[TD="width: 314, bgcolor: transparent"]Markup(s) on existing revision, Markup(s) on existing revision, Markup(s) on existing revision
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 93"]
<tbody>[TR]
[TD="width: 124, bgcolor: transparent"],Yes,Yes
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 86"]
<tbody>[TR]
[TD="width: 115, bgcolor: transparent"]Yes,,
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 127"]
<tbody>[TR]
[TD="width: 169"]AS-N-XG-35542-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 39"]
<tbody>[TR]
[TD="class: xl43106, width: 52"]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl43106, width: 73"]C168
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl43106, width: 71"]XG
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 66"]
<tbody>[TR]
[TD="class: xl43106, width: 88"]D
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl43106, width: 85"]N, N
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 191"]
<tbody>[TR]
[TD="width: 255"]2137-2, 1841-8
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 107"]
<tbody>[TR]
[TD="width: 143"]Open
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 294"]
<tbody>[TR]
[TD="width: 392"]DCN Ready for Issuing, DCN Issued
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 236"]
<tbody>[TR]
[TD="width: 314"]Markup(s) on existing revision, Markup(s) on existing revision
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 93"]
<tbody>[TR]
[TD="width: 124"],Yes
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 86"]
<tbody>[TR]
[TD="width: 115"]Yes,
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be really appreciated
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You must log in or register to reply here.

Similar threads

A
  • Solved
filter duplicates 7000 rows for 1000 names and insert new column to calculation
Replies
5
Views
492
Alaa mg
A
J
  • Question Question
Lookup a Column Range by Matching Heading
Replies
3
Views
395
jcoeng
J
F
  • Question Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
892
KRice
K
D
  • Question Question
Copy/Pasting rows of a table to specific cells of a separate worksheet template and exporting as a new file.
Replies
0
Views
208
DjentChicken
D
E
  • Solved
Comparing two comma-delimited lists and getting unique values
Replies
7
Views
995
Fluff
Fluff

Forum statistics

Threads
1,223,671
Messages
6,173,725
Members
452,529
Latest member
jpaxonreyes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top