Macro Help - Seperating data in a cell split with a comma

[Auto667]

Hey Everyone,

I have a very large data set (20,000 rows roughly) which I am currently working through that needs to be in a specific format. The data is currently in the following format:

SMM02	04/08/2020​	01:12:16​	Item 1
SMM02	04/08/2020​	00:10:27​	Item 1
SMM02	04/08/2020​	04:05:24​	Item 1,Item 2
SMM02	04/08/2020​	04:14:06​	Item 1
SMM02	22/07/2020​	02:54:43​	Item 2,Item 3
SMM02	04/08/2020​	01:31:38​	Item 2,Item 3, Item 1

and I need to get it into the below format:

SMM02	04/08/2020​	01:12:16​	Item 1
SMM02	04/08/2020​	00:10:27​	Item 1
SMM02	04/08/2020​	04:05:24​	Item 1
SMM02	04/08/2020​	04:05:24​	Item 2
SMM02	04/08/2020​	04:14:06​	Item 1
SMM02	22/07/2020​	02:54:43​	Item 2
SMM02	22/07/2020​	02:54:43​	Item 3
SMM02	04/08/2020​	01:31:38​	Item 2
SMM02	04/08/2020​	01:31:38​	Item 3
SMM02	04/08/2020​	01:31:38​	Item 1

It has been a long time since I have done any VBA programming so my current workflow is: insert a new row, copy and paste the data in the first three columns, copy and pasting the 'Item 1' from a previous entry and then copy and pasting 'Item 2' over the cell that has the two items seperated by a comma.

As you can imaging this is taking a long time. I tired to record a macro but it didn't even give me agreat starting point to try amend something.

The number of items the last column can hold can be up to four.

Any help would really be appreciated.

Thanks in advanced.

Auto

 

[alansidman]

I used Power Query/Get and Transform found on the Data Tab

Book6
	A	B	C	D
1	Column1	Column2	Column3	Column4
2	SMM02	04/08/2020	01:12:16	Item 1
3	SMM02	04/08/2020	00:10:27	Item 1
4	SMM02	04/08/2020	04:05:24	Item 1
5	SMM02	04/08/2020	04:05:24	Item 2
6	SMM02	04/08/2020	04:14:06	Item 1
7	SMM02	22/07/2020	02:54:43	Item 2
8	SMM02	22/07/2020	02:54:43	Item 3
9	SMM02	04/08/2020	01:31:38	Item 2
10	SMM02	04/08/2020	01:31:38	Item 3
11	SMM02	04/08/2020	01:31:38	Item 1
Sheet2

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column4", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column4")
in
    #"Split Column by Delimiter"

 

[Auto667]

I used Power Query/Get and Transform found on the Data Tab

Book6
	A	B	C	D
1	Column1	Column2	Column3	Column4
2	SMM02	04/08/2020	01:12:16	Item 1
3	SMM02	04/08/2020	00:10:27	Item 1
4	SMM02	04/08/2020	04:05:24	Item 1
5	SMM02	04/08/2020	04:05:24	Item 2
6	SMM02	04/08/2020	04:14:06	Item 1
7	SMM02	22/07/2020	02:54:43	Item 2
8	SMM02	22/07/2020	02:54:43	Item 3
9	SMM02	04/08/2020	01:31:38	Item 2
10	SMM02	04/08/2020	01:31:38	Item 3
11	SMM02	04/08/2020	01:31:38	Item 1
Sheet2

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column4", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column4")
in
    #"Split Column by Delimiter"

Thanks this is perfect!! Really appreciate the quick help :D