Convert Bar and Comma Separated Values into a single row with one record per cell...

[mnty89]

I have example data that is structured as the below with aggregated values on the end in one cell. I'm hoping to split this out into each record if it is split with a comma, space or pipe. The intended output of it would be to use vba to show it the column with the separated values and then have it output in a separate column with one each. I found an example of something very similar online but it did not work on my 50,000+ record set. Some of the rows contain aggregated records of up to 200 as well.


Starting Example:

Item	Choices
AAAA	101010101010, 2020202044, 21203991911 | 23923277723
BBBB	552532535232, 73746463434, 34934838434 | 4545787343
CCCC	766626222 | 4972329832

End Result Needed:

AAAA	101010101010
AAAA	2020202044
AAAA	21203991911
AAAA	23923277723
BBBB	552532535232
BBBB	73746463434
BBBB	34934838434
BBBB	4545787343
CCCC	766626222
CCCC	4972329832

 

[Rick Rothstein]

A couple of questions...

1) When you say "...if it is split with a comma, space or pipe", do you mean sometimes there is only a space (no comma or pipe) between the numbers (none of your examples show this, hence the question)?

2) Where is the output to go... replace the original data in place or to a new pair of columns?

 

[sandy666]

Item	Choices		Item	Choices
AAAA	101010101010, 2020202044, 21203991911 | 23923277723		AAAA	101010101010
BBBB	552532535232, 73746463434, 34934838434 | 4545787343		AAAA	2020202044
CCCC	766626222 | 4972329832		AAAA	21203991911
			AAAA	23923277723
			BBBB	552532535232
			BBBB	73746463434
			BBBB	34934838434
			BBBB	4545787343
			CCCC	766626222
			CCCC	4972329832

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Choices", Splitter.SplitTextByAnyDelimiter({"|",","}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Choices"),
    Trim = Table.TransformColumns(Split,{{"Choices", Text.Trim, type text}})
in
    Trim

 

[mnty89]

A couple of questions...

1) When you say "...if it is split with a comma, space or pipe", do you mean sometimes there is only a space (no comma or pipe) between the numbers (none of your examples show this, hence the question)?

2) Where is the output to go... replace the original data in place or to a new pair of columns?

Apologies for neglecting to define those:

1) there should actually be no instance of a space as the sole delimiter, but I would just want the space removed if it was left remaining so it only held the value and not the trailing space
2) A new pair of columns would be ideal, so if those examples were in A, B the outputs would populate in C, D

 

[mnty89]

Item	Choices		Item	Choices
AAAA	101010101010, 2020202044, 21203991911 | 23923277723		AAAA	101010101010
BBBB	552532535232, 73746463434, 34934838434 | 4545787343		AAAA	2020202044
CCCC	766626222 | 4972329832		AAAA	21203991911
			AAAA	23923277723
			BBBB	552532535232
			BBBB	73746463434
			BBBB	34934838434
			BBBB	4545787343
			CCCC	766626222
			CCCC	4972329832

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Choices", Splitter.SplitTextByAnyDelimiter({"|",","}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Choices"),
    Trim = Table.TransformColumns(Split,{{"Choices", Text.Trim, type text}})
in
    Trim

Unfortunately I think PQ is going to be lost on the end user here.

 

[sandy666]

Unfortunately I think PQ is going to be lost on the end user here.

Because?

 

[mnty89]

Because?

A handoff to someone temping to do a task but they have extremely minimal background. Juice not worth the squeeze kind of deal

 

[Rick Rothstein]

Here is a macro that you can consider...

Sub Test()
  Dim Combined As Variant
  Combined = Application.Transpose(Split(Join(Application.Transpose(Evaluate("IF({1},A2:A4&""#""&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2:B4,"","","" ""),""|"","" "")),"" "","" ""&A2:A4&""#""))")))))
  Range("C2").Resize(UBound(Combined)) = Combined
  Columns("C").TextToColumns , xlDelimited, , , False, False, False, False, True, "#", FieldInfo:=Array(Array(1, 1), Array(2, 2))
End Sub

NOTE: I outputted Column D as Text because some of your numbers are long and were displayed in exponential form and I am anticipating some of your numbers could possibly have leading zeros.

 

[Rick Rothstein]

Here is a macro that you can consider...

Sub Test()
  Dim Combined As Variant
  Combined = Application.Transpose(Split(Join(Application.Transpose(Evaluate("IF({1},A2:A4&""#""&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2:B4,"","","" ""),""|"","" "")),"" "","" ""&A2:A4&""#""))")))))
  Range("C2").Resize(UBound(Combined)) = Combined
  Columns("C").TextToColumns , xlDelimited, , , False, False, False, False, True, "#", FieldInfo:=Array(Array(1, 1), Array(2, 2))
End Sub

NOTE: I outputted Column D as Text because some of your numbers are long and were displayed in exponential form and I am anticipating some of your numbers could possibly have leading zeros.

Whoops! I forgot to remove my test range address and generalize the solution for any number of cells (less that 65,500 or so maximum) in Column A. Here is the generalized solution...

Sub Test()
  Dim LastRow As Long, Combined As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Combined = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF({1},A2:A@&""#""&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2:B@,"","","" ""),""|"","" "")),"" "","" ""&A2:A@&""#""))", "@", LastRow))))))
  Range("C2").Resize(UBound(Combined)) = Combined
  Columns("C").TextToColumns , xlDelimited, , , False, False, False, False, True, "#", FieldInfo:=Array(Array(1, 1), Array(2, 2))
End Sub

 

[sandy666]

A handoff to someone temping to do a task but they have extremely minimal background. Juice not worth the squeeze kind of deal

still don't understand
enough do that once and later just paste new data then refresh

but ok, your choice
have a nice day