Macro to add preexisting column value into newly created row

[farratn]

I have this existing macro that is splitting the values from column J and inserting each individual value separated by commas into the rows below it. With the code I have and the image below (small sample size as an example, but my data set is significantly larger), new rows will be created to allow "abc,def" to split and "def" be on its own line and "zzz, ppp" to be split and "ppp" to be on its own line but I would like to have the macro be modified to have "123" be included in the newly created row where "def" is now and the same for "456" for where "ppp" will be placed on A5.

Sub SplitValues()
       Dim cell as Range
       Dim values() as String
       Dim i as Long
       For each cell in Range("J1:J100")
                If cell.Value <> "" and InStr(cell.Value, ",") > 0 Then
                     values = Split(cell.Value, ",'")
                     cell.Value = Trim(values(0))
                     For i = 1 To UBound(values)
                             cell.Offset(i,0).EntireRow.Insert
                             cell.Offset(i,0).Value = Trim(values(i))
                     Next i
                 End if
          Next cell
End sub

 

[alansidman]

An alternative to VBA is Power Query which is part of your version of Excel but called Get and Transform Data and found on the Data Tab

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

Book2
	A	B	C	D	E
1	Column1	Column2		Column1	Column2
2	123	abc, def		123	abc
3	456	zzz, ppp		123	def
4				456	zzz
5				456	ppp
Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[farratn]

I'll have to take a look but forgot to mention this is on a company workstation. I assume it should be still available to use.

 

[alansidman]

I assume it should be still available to use

I can not see a reason that it should not be available to use.

 

[farratn]

I can not see a reason that it should not be available to use.

It's incredibly frustrating (to put it mildly), in the sense that how long I spent trying to develop that original macro (and it achieved its original purpose), to see how straightforward the Power Query was and enhances on top of the macro and does what I need it to do. It's also exciting. Perhaps I should have asked the question before I developed the macro but that clearly achieved the same objective and at least moving forward I can use this instead to clean up the data set I'm working on.

Long story short: thank you.

 

[alansidman]

Thanks for your feedback. Good Luck.