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

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
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:


ItemChoices
AAAA101010101010, 2020202044, 21203991911 | 23923277723
BBBB552532535232, 73746463434, 34934838434 | 4545787343
CCCC766626222 | 4972329832




End Result Needed:
AAAA101010101010
AAAA2020202044
AAAA21203991911
AAAA23923277723
BBBB552532535232
BBBB73746463434
BBBB34934838434
BBBB4545787343
CCCC766626222
CCCC4972329832
 
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...
VBA Code:
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?
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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:
ItemItem detailItem DetailItem Agg
AAAAsqsqsqplpl1111111, 22222222, 3333333333 | 23232323232
BBBBdededededkmkm77766666, 939393939, 323111333 | 9900091112
CCCCfvfvfvfvfvfjnjn1111323111 | 22221110002, 36533999933 | 2344443232
DDDDbgbgbgbgbhbhb33232333, 22222222, 44098762 | 52222222


And this would be a new intended after:

ItemItem detailItem DetailItem Agg
AAAAsqsqsqplpl
1111111​
AAAAsqsqsqplpl
22222222​
AAAAsqsqsqplpl
3333333333​
AAAAsqsqsqplpl
23232323232​
BBBBdededededkmkm
77766666​
BBBBdededededkmkm
939393939​
BBBBdededededkmkm
323111333​
BBBBdededededkmkm
9900091112​
CCCCfvfvfvfvfvfjnjn
1111323111​
CCCCfvfvfvfvfvfjnjn
22221110002​
CCCCfvfvfvfvfvfjnjn
36533999933​
CCCCfvfvfvfvfvfjnjn
2344443232​
DDDDbgbgbgbgbhbhb
33232333​
DDDDbgbgbgbgbhbhb
22222222​
DDDDbgbgbgbgbhbhb
44098762​
DDDDbgbgbgbgbhbhb
52222222​
 
Upvote 0
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)?
 
Upvote 0
Hi
What about
VBA Code:
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
 
Upvote 0
Same method but
VBA Code:
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
 
Upvote 0
Even
VBA Code:
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
 
Upvote 0
Here is another way to write a macro for this...
VBA Code:
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.
 
Upvote 0
formula for One row may you help to join multi Rows

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

Cell Formulas
RangeFormula
A8A8=SUBSTITUTE(""&SUBSTITUTE(SUBSTITUTE(";"&D2,",",";"),"|",";"),";",";"&TEXTJOIN(",",1,A2:C2)&",")
A10:J19A10=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.
 
Upvote 0
What did I miss below?

This my guideline formula
Excel Formula:
=SUBSTITUTE("," & SUBSTITUTE(SUBSTITUTE(D2, ",", ";,"), "|", ";,"), ",", TEXTJOIN(",", 1,A2:C2) & ",")

dddd (version 1).xlsb
ABCDEFGHIJKLMNO
1ItemItem detailItem DetailItem Agg
2AAAAsqsqsqplpl1111111, 22222222, 3333333333 | 23232323232
3BBBBGHGHNYNY1111111, 22222222, 3333333333 | 23232323232
4CCCCGHGHKTKT1111111, 22222222, 3333333333 | 23232323232
5
6
7
8
9Test
10AAAAsqsqsqplpl222222221111111, 22222222, 3333333333 | 23232323232AAAAAAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
11AAAAsqsqsqplpl222222221111111, 22222222, 3333333333 | 23232323232AAAAAAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
12AAAAsqsqsqplpl222222221111111, 22222222, 3333333333 | 23232323232AAAAAAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
13AAAAsqsqsqplpl222222221111111, 22222222, 3333333333 | 23232323232AAAAAAAA,sqsqsq,plpl,1111111;AAAA,sqsqsq,plpl, 22222222;AAAA,sqsqsq,plpl, 3333333333 ;AAAA,sqsqsq,plpl, 23232323232
14BBBBGHGHNYNY33333333331111111, 22222222, 3333333333 | 23232323232BBBBBBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
15BBBBGHGHNYNY33333333331111111, 22222222, 3333333333 | 23232323232BBBBBBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
16BBBBGHGHNYNY33333333331111111, 22222222, 3333333333 | 23232323232BBBBBBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
17BBBBGHGHNYNY33333333331111111, 22222222, 3333333333 | 23232323232BBBBBBBB,GHGH,NYNY,1111111;BBBB,GHGH,NYNY, 22222222;BBBB,GHGH,NYNY, 3333333333 ;BBBB,GHGH,NYNY, 23232323232
18CCCCGHGHKTKT232323232321111111, 22222222, 3333333333 | 23232323232CCCCCCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
19CCCCGHGHKTKT232323232321111111, 22222222, 3333333333 | 23232323232CCCCCCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
20CCCCGHGHKTKT232323232321111111, 22222222, 3333333333 | 23232323232CCCCCCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
21CCCCGHGHKTKT232323232321111111, 22222222, 3333333333 | 23232323232CCCCCCCC,GHGH,KTKT,1111111;CCCC,GHGH,KTKT, 22222222;CCCC,GHGH,KTKT, 3333333333 ;CCCC,GHGH,KTKT, 23232323232
Sheet3
Cell Formulas
RangeFormula
B10:E21B10=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:M21M10=INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,4))
N10:N21N10=OFFSET(INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$10)+1,4)/4)+1,1)),0,0,1,3)
O10:O21O10=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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top