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

 

[Dossfm0q]

Below macro to set up long Formula array

Sub Separated_Values()

Application.ScreenUpdating = False
Dim Rng1 As Range, Rng2 As Range
Dim Adrs1 As String, Adrs2 As String
Dim MID1 As String, Mid1A As String, Mid2 As String, StrtNum1 As String, StrtNum2 As String, NumChr1 As String, NumChr2 As String, FndStrtNum1 As String, FndStrtNum2 As String


Set Rng1 = Range("A2:C4") '<<<<<
Set Rng2 = Range("D2:D4") '<<<<<

Adrs1 = Rng1.Address(True, True)
Adrs2 = Rng2.Address(True, True)

MID1 = "SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Mid1A,""|"","",""),"";"","",""),"","","";,"")"
Mid1A = "MID("";""&TEXTJOIN("";"",1," & Adrs2 & ")&"";"",StrtNum1,NumChr1)"
StrtNum1 = "FIND(""|"",SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"",""|"",TRANSPOSE(ROW(INDIRECT(""1:""&LEN("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"")-LEN(SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"",""""))-1)))))"

FndStrtNum1 = "TRANSPOSE(ROW(INDIRECT(""2:""&LEN("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"")-LEN(SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"","""")))))"
NumChr1 = "FIND(""|"",SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"",""|"",FndStrtNum1))-StrtNum1"

Mid2 = "MID(""""&TEXTJOIN("","",1," & Adrs1 & ")&"","",StrtNum2,NumChr2)"
StrtNum2 = "TRANSPOSE(FIND(""|"",SUBSTITUTE("",""&TEXTJOIN("","",1," & Adrs1 & ")&"","","","",""|"",(ROW(INDIRECT(""1:""&ROWS(" & Adrs1 & ")))-1)*COLUMNS(" & Adrs1 & ")+1),(ROW(INDIRECT(""1:""&ROWS(" & Adrs1 & ")))-1)*COLUMNS(" & Adrs1 & ")+1))"
FndStrtNum2 = "(ROW(INDIRECT(""2:""&ROWS(" & Adrs1 & ")+1))-1)*COLUMNS(" & Adrs1 & ")+1"
NumChr2 = "TRANSPOSE(FIND(""|"",SUBSTITUTE("",""&TEXTJOIN("","",1," & Adrs1 & ")&"","","","",""|"",FndStrtNum2),FndStrtNum2))-StrtNum2"

Rws = [LEN(SUBSTITUTE(SUBSTITUTE(";"&TEXTJOIN(";",1,$D$2:$D$4)&"",",",";"),"|",";"))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(";"&TEXTJOIN(";",1,$D$2:$D$4)&"",",",";"),"|",";"),";",""))]
Rw = Rng1.Row + Rng1.Rows.Count + 3
cl = Rng1.Column
With Cells(Rw, cl).Resize(Rws, Rng1.Columns.Count + 1)

.FormulaArray = "=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(SUBSTITUTE(MID1,"","",MID2)),"";"","""",1),"";"",REPT("" "",999)),(ROW()-ROW(A$8)+1)*999-998,999)),"","",REPT("" "",999)),(COLUMN()-COLUMN(A$8)+1)*999-998,999))"
.Replace "MID1", MID1
.Replace "Mid1A", Mid1A
.Replace "StrtNum1", StrtNum1
.Replace "NumChr1", NumChr1
.Replace "FndStrtNum1", ndStrtNum1
.Replace "StrtNum1", StrtNum1

.Replace "MID2", Mid2
.Replace "StrtNum2", StrtNum2
.Replace "NumChr2", NumChr2
.Replace "FndStrtNum2", FndStrtNum2
.Replace "StrtNum2", StrtNum2

Application.ScreenUpdating = True

End With

End Sub

select 13 Rows and 4 Columns then Press CTRL+SHIFT+ENTER to enter array formulas.

nnnnn.xlsm
	A	B	C	D
1	Item	Item detail	Item Detail	Item Agg
2	AAAA	sqsqsq	plpl	1111111, 22222222, 3333333333 | 23232323232 | 777
3	BBBB	GHGH	NYNY	1111111, 22222222, 3333333333 | 23232323232
4	CCCC	GHGH	KTKT	1111111, 22222222, 3333333333 | 23232323232
5
6
7	Item	Item detail	Item Detail	Item Agg
8	AAAA	sqsqsq	plpl	1111111
9	AAAA	sqsqsq	plpl	22222222
10	AAAA	sqsqsq	plpl	3333333333
11	AAAA	sqsqsq	plpl	23232323232
12	AAAA	sqsqsq	plpl	777
13	BBBB	GHGH	NYNY	1111111
14	BBBB	GHGH	NYNY	22222222
15	BBBB	GHGH	NYNY	3333333333
16	BBBB	GHGH	NYNY	23232323232
17	CCCC	GHGH	KTKT	1111111
18	CCCC	GHGH	KTKT	22222222
19	CCCC	GHGH	KTKT	3333333333
20	CCCC	GHGH	KTKT	23232323232
21
Sheet1

Cell Formulas
Range	Formula
A8:D20	A8	=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(";"&TEXTJOIN(";",1,$D$2:$D$4)&";",FIND("|",SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";")-LEN(SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";",""))-1))))),FIND("|",SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","|",TRANSPOSE(ROW(INDIRECT("2:"&LEN(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";")-LEN(SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","")))))))-FIND("|",SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";")-LEN(SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";",""))-1)))))),"|",","),";",","),",",";,"),",",MID(""&TEXTJOIN(",",1,$A$2:$C$4)&",",TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$2:$C$4)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1),(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1)),TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$2:$C$4)&",",",","|",(ROW(INDIRECT("2:"&ROWS($A$2:$C$4)+1))-1)*COLUMNS($A$2:$C$4)+1),(ROW(INDIRECT("2:"&ROWS($A$2:$C$4)+1))-1)*COLUMNS($A$2:$C$4)+1))-TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$2:$C$4)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1),(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1))))),";","",1),";",REPT(" ",999)),(ROW()-ROW(A$8)+1)*999-998,999)),",",REPT(" ",999)),(COLUMN()-COLUMN(A$8)+1)*999-998,999))
Press CTRL+SHIFT+ENTER to enter array formulas.