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

 

[mnty89]

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

This is amazing. I haven’t had a chance to come back on until tonight to tell you how useful this was. As a follow up, there was a reason to add additional other data to the table where now there are three columns before the one with the aggregated values. Is there an east adjustment to your code that would do the same, but account for that?

 

[Rick Rothstein]

What do you mean by 3 columns? One Item and two Choices? Two Items and one Choice? Three Choices?

It would help if you showed a "Starting" sample and an "End Result" for your three column request so we can see what you actually want instead of trying to guess.

 

[mnty89]

What do you mean by 3 columns? One Item and two Choices? Two Items and one Choice? Three Choices?

It would help if you showed a "Starting" sample and an "End Result" for your three column request so we can see what you actually want instead of trying to guess.

You're right sorry, sometimes I'm too in my own head!

Here would be a new before:

Item	Item detail	Item Detail	Item Agg
AAAA	sqsqsq	plpl	1111111, 22222222, 3333333333 | 23232323232
BBBB	dedededed	kmkm	77766666, 939393939, 323111333 | 9900091112
CCCC	fvfvfvfvfvf	jnjn	1111323111 | 22221110002, 36533999933 | 2344443232
DDDD	bgbgbgbgb	hbhb	33232333, 22222222, 44098762 | 52222222

And this would be a new intended after:

Item	Item detail	Item Detail	Item Agg
AAAA	sqsqsq	plpl	1111111​
AAAA	sqsqsq	plpl	22222222​
AAAA	sqsqsq	plpl	3333333333​
AAAA	sqsqsq	plpl	23232323232​
BBBB	dedededed	kmkm	77766666​
BBBB	dedededed	kmkm	939393939​
BBBB	dedededed	kmkm	323111333​
BBBB	dedededed	kmkm	9900091112​
CCCC	fvfvfvfvfvf	jnjn	1111323111​
CCCC	fvfvfvfvfvf	jnjn	22221110002​
CCCC	fvfvfvfvfvf	jnjn	36533999933​
CCCC	fvfvfvfvfvf	jnjn	2344443232​
DDDD	bgbgbgbgb	hbhb	33232333​
DDDD	bgbgbgbgb	hbhb	22222222​
DDDD	bgbgbgbgb	hbhb	44098762​
DDDD	bgbgbgbgb	hbhb	52222222​

 

[Rick Rothstein]

Okay, that is different than I was imagining. The solution kind of depends on the structure of your data (the Evaluate function as limitations on how large its text argument can be after substitution are made for the cells' contents). Some questions...
1) About how much text maximum could be in your "Item" column?
2) About how much text maximum could be in any one cell in your either of your "Item detail" columns?
3) In your "Item Agg" column... are there always exactly four delimited number in each cell (like your example shows)?

 

[mohadin]

Hi
What about

Sub test()
    Dim a, x As Variant
    Dim i As Long
    a = Range("a2").Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, 4)
    For i = 1 To UBound(a)
        x = Split(Replace(a(i, 4), "|", ","), ",")
        Cells(i + l, 6).Resize(UBound(x) + 1) = a(i, 1)
        Cells(i + l, 6).Offset(, 1).Resize(UBound(x) + 1) = a(i, 2)
        Cells(i + l, 6).Offset(, 2).Resize(UBound(x) + 1) = a(i, 3)
        Cells(i + l, 6).Offset(, 3).Resize(UBound(x) + 1) = Application.Transpose(x)
        l = l + UBound(x)
    Next
End Sub

 

[mohadin]

Same method but

Sub test()
    Dim a, x As Variant
    Dim i, ii, l As Long
    a = Range("a2").Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, 4)
    For i = 1 To UBound(a, 1)
        x = Split(Replace(a(i, 4), "|", ","), ",")
        For ii = 1 To UBound(a, 2) - 1
            Cells(i + l, 6).Offset(, ii - 1).Resize(UBound(x) + 1) = a(i, ii)
        Next
        Cells(i + l, 6).Offset(, 3).Resize(UBound(x) + 1) = Application.Transpose(x)
        l = l + UBound(x)
    Next
End Sub

 

[mohadin]

Even

Sub test()
    Dim x As Variant
    Dim i, ii, l, lr, lc As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To lr
        x = Split(Replace(Cells(i, 4), "|", ","), ",")
        For ii = 1 To lc - 1
            Cells(i + l, 6).Offset(, ii - 1).Resize(UBound(x) + 1) = Cells(i, ii)
        Next
        Cells(i + l, 6).Offset(, 3).Resize(UBound(x) + 1) = Application.Transpose(x)
        l = l + UBound(x)
    Next
End Sub

 

[Rick Rothstein]

Here is another way to write a macro for this...

Sub ItemAgg()
  Dim R As Long, N As Long, Data As Variant, Arr As Variant
  Data = Range("A1").CurrentRegion
  For R = 1 To UBound(Data)
    Arr = Split(Replace(Data(R, 4), " |", ","), ", ")
    Cells(Rows.Count, "I").End(xlUp).Offset(1).Resize(UBound(Arr) + 1) = Application.Transpose(Split(Replace(Data(R, 4), " |", ","), ", "))
    Cells(Rows.Count, "F").End(xlUp).Offset(1).Resize(UBound(Arr) + 1, 3) = Array(Data(R, 1), Data(R, 2), Data(R, 3))
  Next
End Sub

Note: It might be possible to write a more compact, maybe even non-looping macro depending on the answers you give to my questions in Message #14.

 

[Dossfm0q]

formula for One row may you help to join multi Rows

=SUBSTITUTE(""&SUBSTITUTE(SUBSTITUTE(";"&D2,",",";"),"|",";"),";",";"&TEXTJOIN(",",1,A2:C2)&",")

dddd.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Item	Item detail	Item Detail	Item Agg
2	AAAA	sqsqsq	plpl	1111111, 22222222, 3333333333 | 23232323232
3
4
5
6
7
8	;AAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
9
10
11	AAAA	sqsqsq	plpl	1111111
12	AAAA	sqsqsq	plpl	22222222
13	AAAA	sqsqsq	plpl	3333333333
14	AAAA	sqsqsq	plpl	23232323232
15
16
17
18
19
Sheet3

Cell Formulas
Range	Formula
A8	A8	=SUBSTITUTE(""&SUBSTITUTE(SUBSTITUTE(";"&D2,",",";"),"|",";"),";",";"&TEXTJOIN(",",1,A2:C2)&",")
A10:J19	A10	=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(""&SUBSTITUTE(SUBSTITUTE(";"&D2,",",";"),"|",";"),";",";"&TEXTJOIN(",",1,A2:C2)&","),";",REPT(" ",999)),(ROW()-ROW(A$10)+1)*999-998,999)),",",REPT(" ",999)),(COLUMN()-COLUMN(A$10)+1)*999-998,999))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dossfm0q]

What did I miss below?

This my guideline formula

=SUBSTITUTE("," & SUBSTITUTE(SUBSTITUTE(D2, ",", ";,"), "|", ";,"), ",", TEXTJOIN(",", 1,A2:C2) & ",")

dddd (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item	Item detail	Item Detail	Item Agg
2	AAAA	sqsqsq	plpl	1111111, 22222222, 3333333333 | 23232323232
3	BBBB	GHGH	NYNY	1111111, 22222222, 3333333333 | 23232323232
4	CCCC	GHGH	KTKT	1111111, 22222222, 3333333333 | 23232323232
5
6
7
8
9													Test
10		AAAA	sqsqsq	plpl	22222222								1111111, 22222222, 3333333333 | 23232323232	AAAA	AAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
11		AAAA	sqsqsq	plpl	22222222								1111111, 22222222, 3333333333 | 23232323232	AAAA	AAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
12		AAAA	sqsqsq	plpl	22222222								1111111, 22222222, 3333333333 | 23232323232	AAAA	AAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
13		AAAA	sqsqsq	plpl	22222222								1111111, 22222222, 3333333333 | 23232323232	AAAA	AAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
14		BBBB	GHGH	NYNY	3333333333								1111111, 22222222, 3333333333 | 23232323232	BBBB	BBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
15		BBBB	GHGH	NYNY	3333333333								1111111, 22222222, 3333333333 | 23232323232	BBBB	BBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
16		BBBB	GHGH	NYNY	3333333333								1111111, 22222222, 3333333333 | 23232323232	BBBB	BBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
17		BBBB	GHGH	NYNY	3333333333								1111111, 22222222, 3333333333 | 23232323232	BBBB	BBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
18		CCCC	GHGH	KTKT	23232323232								1111111, 22222222, 3333333333 | 23232323232	CCCC	CCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
19		CCCC	GHGH	KTKT	23232323232								1111111, 22222222, 3333333333 | 23232323232	CCCC	CCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
20		CCCC	GHGH	KTKT	23232323232								1111111, 22222222, 3333333333 | 23232323232	CCCC	CCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
21		CCCC	GHGH	KTKT	23232323232								1111111, 22222222, 3333333333 | 23232323232	CCCC	CCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
Sheet3

Cell Formulas
Range	Formula
B10:E21	B10	=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE("," & SUBSTITUTE(SUBSTITUTE(INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,4)), ",", ";,"), "|", ";,"), ",", TEXTJOIN(",", 1,OFFSET(INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,1)),0,0,1,3)) & ","),";",REPT(" ",999)),((CEILING(ROW()-ROW($B$10)+1,4)/4)+1)*999-998,999)),",",REPT(" ",999)),(COLUMN()-COLUMN(B$10)+1)*999-998,999))
M10:M21	M10	=INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,4))
N10:N21	N10	=OFFSET(INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,1)),0,0,1,3)
O10:O21	O10	=SUBSTITUTE("," & SUBSTITUTE(SUBSTITUTE(INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,4)), ",", ";,"), "|", ";,"), ",", TEXTJOIN(",", 1,OFFSET(INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,1)),0,0,1,3)) & ",")
Press CTRL+SHIFT+ENTER to enter array formulas.