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
 

mnty89

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,917
Messages
5,703,166
Members
421,279
Latest member
emzy

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
Top