Parsing and combining delimited text

D

demeeder

New Member
Joined
Apr 27, 2015
Messages
45
I think this is a text to columns and then something else. The final result of what I am trying to accomplish is represented below. Letters in LOC1 and LOC2 are not important... random and represent a location. First in cell LOC1 is associated with the first in LOC2... and so on....[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]LOC1[/TD]
[TD]LOC2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a,b,c,d[/TD]
[TD]d,e,f,a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]d,t,f,r[/TD]
[TD]a,b,c,d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a,c,f,v[/TD]
[TD]r,t,e,r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]r,t,y,w[/TD]
[TD]d,c,b,a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]b[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]c[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]t[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]f[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]r[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

Dave
 
Sort by date Sort by votes
Peter_SSs said:
OK, that was not included in the original description or sample data. ;)

Easy enough to account for that, but before doing so we must also deal with the following.
Tell us more about what you actually have and where. For example ..
- About how many rows of data will there be before the code is run?
- What would be the most (approximately will do) number of items in a cell in LOC1 or LOC2?
- How many columns are there in your actual data?
- Are those columns all required to be preserved with the associated rows? If not, which ones are required to be preserved?
- Which columns are ID, LOC1 and LOC2?
Click to expand...

Peter, I really appreciate your entertaining this question. I will have to look at the data set at work tomorrow to provide a more detailed response. Thanks again!

Dave
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Peter_SSs said:
OK, that was not included in the original description or sample data. ;)

Easy enough to account for that, but before doing so we must also deal with the following.
Tell us more about what you actually have and where. For example ..
- About how many rows of data will there be before the code is run?
- What would be the most (approximately will do) number of items in a cell in LOC1 or LOC2?
- How many columns are there in your actual data?
- Are those columns all required to be preserved with the associated rows? If not, which ones are required to be preserved?
- Which columns are ID, LOC1 and LOC2?
Click to expand...

Peter..
- there are about 150 rows in this particular data set, but future data sets could have more. The macro worked with this amount.
- the biggest cell has about 10
- I am good with just the three columns. However, if you were so inclined... there are 5 columns between ID (Column A) and LOC 1 (column G). I would need the associated data per ID# to pivot with the split. Again, that is a desire, not required. Otherwise, without the additional 5 columns, I am using A:C
- more importantly, I did notice that when there was a discrepancy between the number of items in LOC1 and LOC2, that it defaulted to the lowest. Any chance it can insert a blank cell for the missing entry?

Using Excel 2013. Of note, Excel for Mac seemed to allow for errant commas at the end of the string.... at work, excel 2013 results in an error. Not a big deal as it just forces me to clean up the data more.

Thank, again.
 
Upvote 0
demeeder said:
... there are 5 columns between ID (Column A) and LOC 1 (column G).
Click to expand...
Ok, so it seems data in columns A:H


demeeder said:
... when there was a discrepancy between the number of items in LOC1 and LOC2 ..
Click to expand...
This is new. :eek:
Remember that all we have to go on is what you tell us or show us. There has been no mention of this circumstance before and no evidence of it in the sample data.


demeeder said:
Excel for Mac seemed to ...
Click to expand...
I don't use a Mac so I can't comment on differences between Mac & PC versions.


demeeder said:
.. errant commas at the end of the string....
Click to expand...
Another possibility that hasn't been mentioned or shown, so I haven't addressed. ;)

See how this goes. If still problems, please provide a small but representative set of sample data and expected results together with any further clarification that you can give.
In this code I have written the results to columns J:Q but you can redirect as required.
Rich (BB code): 
Sub Rearrange_v2()
  Dim a As Variant, b As Variant, bits1 As Variant, bits2 As Variant
  Dim i As Long, j As Long, k As Long, n As Long, R As Long

  a = Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To Rows.Count, 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    bits1 = Split(a(i, 7) & ",", ",")
    bits2 = Split(a(i, 8) & ",", ",")
    n = UBound(bits1)
    If UBound(bits2) > n Then
      n = UBound(bits2)
      ReDim Preserve bits1(0 To n)
    Else
      ReDim Preserve bits2(0 To UBound(bits1))
    End If
    For j = 0 To n - 1
      R = R + 1
      b(R, 1) = a(i, 1): b(R, 7) = bits1(j): b(R, 8) = bits2(j)
      For k = 2 To 6
        b(R, k) = a(i, k)
      Next k
    Next j
  Next i
  Range("J1:Q1").Resize(R).Value = b
End Sub


Here is my sample data and results of the above code.

Book1
ABCDEFGHIJKLMNOPQ
1IDHdr 2Hdr 3Hdr 4Hdr 5Hdr 6LOC1LOC2IDHdr 2Hdr 3Hdr 4Hdr 5Hdr 6LOC1LOC2
21558645824a,b,c,dd,e,f,a1558645824ad
32571838343d,t,f,ra,b,c,d,e1558645824be
43877869420a,c,f,v,x,gr,t,e,r1558645824cf
542573260211558645824da
6520801191902571838343da
72571838343tb
82571838343fc
92571838343rd
102571838343e
113877869420ar
123877869420ct
133877869420fe
143877869420vr
153877869420x
163877869420g
174257326021
1852080119190
Sheet3
 
Upvote 0
demeeder said:
Using Excel 2013. Of note, Excel for Mac seemed to allow for errant commas at the end of the string.... at work, excel 2013 results in an error.
Click to expand...
What should happen with the "errant commas at the end of the string"... should they be ignored as if they weren't there or should a row be created for each one of them even though there is no text to display?
 
Upvote 0
Peter... yes...I hadn't realized this was an issue until I ran the code and noticed an issue with the results. The data shouldn't have different numbers of entries in LOC1 and LOC2, but in the event that it does (as I found out), I would want to see the blank. My work around was to do a separate formula to do the math prior ....=(LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2),",",""))+1)-(LEN(TRIM(C2))LEN(SUBSTITUTE(TRIM(C2),",",""))+1) to show me the error before running the macro. The reality is that when there is a mismatch, I don't know which one is actually missing, so it requires me going back and doing some research on the data.



As for the extra columns... that, too, was a new parameter based on my continuing to work with the data. I appreciate your patience.



I was not meaning to be coy or limited in info, but was offering what I had at the time. The other constraint is that the data set I am working with is not on a connected computer system, so I am probably being a bit lazy when putting together my examples.



Regardless, you and Rick have been amazing and your macro worked well (once I cleaned up extra commas and the like). I very much appreciate your time.



Rick....As for the extra comma... in reality, I shouldn't have them at all but was just pointing out that when I ran the code using Excel for Mac (I think 2016), it treated the comma with nothing after as a blank cell. On Windows via Excel 2013, it didn't let the macro run. Info offered only for your edification. Bottom line, I can clean up the data to prevent having too many variables written into the VBA.



Peter.. to satisfy my curiosity only and highlighting my lack of VBA understanding...if I was to want more columns between ID and LOC1... it is simply a matter of changing the "6" in "For k = 2 To 6" to the appropriate number?
 
Upvote 0
demeeder said:
.. your macro worked well (once I cleaned up extra commas and the like). I very much appreciate your time.
Click to expand...
You're welcome. :)


demeeder said:
...if I was to want more columns between ID and LOC1... it is simply a matter of changing the "6" in "For k = 2 To 6" to the appropriate number?
Click to expand...
No, I set out to write code to do the job requested, not to make it as easy as possible to modify. ;)
I might miss some but below are highlighted some of the things that would need adjustment. Hopefully you can work out what they need adjusting to.
Rich (BB code): 
Sub Rearrange_v2()
  Dim a As Variant, b As Variant, bits1 As Variant, bits2 As Variant
  Dim i As Long, j As Long, k As Long, n As Long, R As Long

  a = Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To Rows.Count, 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    bits1 = Split(a(i, 7) & ",", ",")
    bits2 = Split(a(i, 8) & ",", ",")
    n = UBound(bits1)
    If UBound(bits2) > n Then
      n = UBound(bits2)
      ReDim Preserve bits1(0 To n)
    Else
      ReDim Preserve bits2(0 To UBound(bits1))
    End If
    For j = 0 To n - 1
      R = R + 1
      b(R, 1) = a(i, 1): b(R, 7) = bits1(j): b(R, 8) = bits2(j)
      For k = 2 To 6
        b(R, k) = a(i, k)
      Next k
    Next j
  Next i
  Range("J1:Q1").Resize(R).Value = b
End Sub
 
Upvote 0
Peter_SSs said:
Cheers :biggrin:
Post back if you need further help though.
Click to expand...

Peter, we are back at it with some data clean-up and I think I have a 99% solution to our new parameters. Will you please review and show me how to account for an error I am getting?

New conditions.....
17 columns
column A is the unique ID
2-17 are comma delimited with the same number of entries per cell (or so we hope)

The below is probably the long way of doing it, but I based it off your earlier template.

the error I get is when there may be a blank cell. When I debug it sends me to the 4th line above "End Sub" and highlights the blank cell (i.e., if a cell in column G is blank it will highlight "b(R, 7) = bits6(j):")

Here is a pic of the sample data. Notice Column G is blank: https://www.dropbox.com/s/l8r0jo490hgpy0c/Screen Shot 2018-08-13 at 7.18.31 PM.png?dl=0


-----
Code: 
Sub Data_cleanup()
  Dim a As Variant, b As Variant, bits1 As Variant, bits2 As Variant, bits3 As Variant, bits4 As Variant, bits5 As Variant, bits6 As Variant, bits7 As Variant, bits8 As Variant, bits9 As Variant, bits10 As Variant, bits11 As Variant, bits12 As Variant, bits13 As Variant, bits14 As Variant, bits15 As Variant, bits16 As Variant, bits17 As Variant
  Dim i As Long, j As Long, k As Long, n As Long, R As Long


  a = Range("A1:Q" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To Rows.Count, 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    bits1 = Split(a(i, 2) & ",", ",")
    bits2 = Split(a(i, 3) & ",", ",")
    bits3 = Split(a(i, 4) & ",", ",")
    bits4 = Split(a(i, 5) & ",", ",")
    bits5 = Split(a(i, 6) & ",", ",")
    bits6 = Split(a(i, 7) & ",", ",")
    bits7 = Split(a(i, 8) & ",", ",")
    bits8 = Split(a(i, 9) & ",", ",")
    bits9 = Split(a(i, 10) & ",", ",")
    bits10 = Split(a(i, 11) & ",", ",")
    bits11 = Split(a(i, 12) & ",", ",")
    bits12 = Split(a(i, 13) & ",", ",")
    bits13 = Split(a(i, 14) & ",", ",")
    bits14 = Split(a(i, 15) & ",", ",")
    bits15 = Split(a(i, 16) & ",", ",")
    bits16 = Split(a(i, 17) & ",", ",")
    n = UBound(bits1)
    If UBound(bits2) > n Then
      n = UBound(bits2)
      ReDim Preserve bits1(0 To n)
    Else
      ReDim Preserve bits2(0 To UBound(bits1))
    End If
    For j = 0 To n - 1
      R = R + 1
      b(R, 1) = a(i, 1): b(R, 2) = bits1(j): b(R, 3) = bits2(j): b(R, 4) = bits3(j): b(R, 5) = bits4(j): b(R, 6) = bits5(j): b(R, 7) = bits6(j): b(R, 8) = bits7(j): b(R, 9) = bits8(j): b(R, 10) = bits9(j): b(R, 11) = bits10(j): b(R, 12) = bits11(j): b(R, 13) = bits12(j): b(R, 14) = bits13(j): b(R, 15) = bits14(j): b(R, 16) = bits15(j): b(R, 17) = bits16(j)
    Next j
  Next i
  Range("S1:AI1").Resize(R).Value = b
End Sub
 
Last edited by a moderator:
Upvote 0
Two things:
1. I cannot copy from an image to test with that data. Could you post an actual Excel with just that small sample data to Dropbox instead of just a picture?
2. Include in the sample file what you expect the results to look like given that there is a blank cell in the data and you haven't told/shown us what to do in that circumstance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

serge
  • Question Question
#N/A cell skip.
Replies
3
Views
273
Peter_SSs
Peter_SSs
R
  • Solved
Cleaner VBA Code
Replies
4
Views
277
Joe4
Joe4
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
110
Fluff
Fluff
Z
  • Question Question
How to automatically create word files conditionally based on excel sheet content?
Replies
3
Views
167
Joe4
Joe4
C
  • Solved
Counting commas between multiple specific strings of text in a single cell
2
Replies
10
Views
396
Country_Calc_2
C

Forum statistics

Threads
1,224,029
Messages
6,175,994
Members
452,694
Latest member
SaruwatariKaito

Share this page

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