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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
demeeder said:
My apologies Peter. Please let me know if the linked files answers your questions. I included a couple notes for more clarity (I hope). The VBA should be included as well. https://www.dropbox.com/s/v8rkiuyaw7l2s0c/data cleanup-1.xlsx?dl=0
Click to expand...

In the example from your link, you wrote "Ideally, each column for a given row will have the same number of commas". You use of the word "ideally" muddies up the water somewhat. Are you saying that it is possible for a cell within a given row to have more or less commas than all the rest of the cells in that row? If so, how should it be handled?
 
Upvote 0
Rick Rothstein said:
In the example from your link, you wrote "Ideally, each column for a given row will have the same number of commas". You use of the word "ideally" muddies up the water somewhat. Are you saying that it is possible for a cell within a given row to have more or less commas than all the rest of the cells in that row? If so, how should it be handled?
Click to expand...

Rick, as we clean up the data we hope to find any entrythat is missing information. That said,I do not want the macro to not run if there is a delta. Currently I believe the missing comma isrepresented by a blank cell in the last entry. When I see the blank I will be queued into further clean-up to get theinformation added.



For cells that are originally blank, I would want to keepblank in the parsing.


Does that make sense?
 
Upvote 0
demeeder said:
Rick, as we clean up the data we hope to find any entrythat is missing information. That said,I do not want the macro to not run if there is a delta. Currently I believe the missing comma isrepresented by a blank cell in the last entry. When I see the blank I will be queued into further clean-up to get theinformation added.



For cells that are originally blank, I would want to keepblank in the parsing.


Does that make sense?
Click to expand...

I wasn't asking about a blank cell, rather, I wanted to know whether it might be possible for there to be for a given row, say, three items per cell except for one or more cells in that row that could maybe contain two or four items and, if so, what should be done with them.
 
Upvote 0
Rick Rothstein said:
I wasn't asking about a blank cell, rather, I wanted to know whether it might be possible for there to be for a given row, say, three items per cell except for one or more cells in that row that could maybe contain two or four items and, if so, what should be done with them.
Click to expand...

ahh.. yes, it is possible although not desired. I would like the VBA to default to parsing to the highest number and simply leaving blanks for the missing entries. I think it already does that. Ultimately any blanks that occur will queue me to find the missing data and run the script again.

Does that answer your question?

Thanks.

Dave
 
Upvote 0
demeeder said:
Please let me know if the linked files answers your questions.
Click to expand...
No it doesn't.

1. In post 19 you said you got an error when there was a blank cell in the data and you showed an image of that. I asked for some actual data like that and the expected results. The data you provided has no blank cells at all in the range B2:Q5 so I still have no idea what results you expect when there is a blank cell in the original data.

2. Further, in your expected results you have blank cells at W8 and W9. Why are they blank? From what I can see W8 should contain "hgf" abd W9 should contain "23". Please clarify.
 
Upvote 0
Peter, please forgive me for being so unclear. I have added a second sheet that is more simple and hopefully gives you a better understanding of what I am looking for. Color coded for additional clarity (I hope). still working with 17 columns of data, just represented by the three on Sheet 2. (Updated link: https://www.dropbox.com/s/kbi7chqurwx8dsn/data cleanup-1.xlsm?dl=0)

As for the W8 and W9... you are correct, they should have been filled in. Not sure why they weren't but when I re-ran the VBA, they filled in correctly.

Regarding Sheet 1. Assuming you are able to manipulate the data, you can see where I was getting the errors if you have blank cells in D2:Q3 (notice I have a blank in O4 and the associated AG9:AG10 are blank). For some reason if I were to put a blank cell anywhere in D2:Q3, I get a run error.

Regarding how the current VBA handles a row that may have columns with different number of commas, it appears that it does not account for an "increase" in commas as it moves down the line. If a row starts with two commas, it will not account for a larger number in a follow-on column (see E5 vs. W10, rather what should also be in W11).

Dave
 
Upvote 0
With your last data file, it still seems to have a slight error in Sheet1. Cell E5 contains 2 values so the results section should have 2 rows for ID4 shouldn't it?

I think this should get you pretty close. It assumes only one column before the group of columns with delimited data.
This is for the layout of Sheet1 in your sample file. But if the number of delimited columns changes, you should only need to amend the two red column letters.
This code should allow for any number of commas (increase or decrease) as well as blank cells.

Code: 
Sub Rearrange_v3()
  Dim a As Variant, b As Variant, bits As Variant
  Dim i As Long, j As Long, k As Long, cols As Long, mx As Long, x As Long
  Dim suff As String

  a = Range("A2:Q" & Range("A" & Rows.Count).End(xlUp).Row).Value
  cols = UBound(a, 2) - 1
  suff = String(cols, ",")
  ReDim b(1 To Rows.Count, 1 To cols + 1)
  ReDim bits(1 To cols)
  For i = 1 To UBound(a)
    mx = 0
    For j = 1 To cols
      If UBound(Split(a(i, j + 1), ",")) + 1 > mx Then mx = UBound(Split(a(i, j + 1), ",")) + 1
      bits(j) = Split(a(i, j + 1) & suff, ",")
    Next j
    For x = 1 To mx
      k = k + 1
      b(k, 1) = a(i, 1)
      For j = 1 To cols
        b(k, j + 1) = bits(j)(x - 1)
      Next j
    Next x
  Next i
  Range("S2").Resize(k, cols + 1).Value = b
End Sub

My sample data (cols A:Q) & results of code (S:AI)

<div class="cms_table">
Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1column 1column 2column 3column 4column 5column 6column 7column 8column 9column 10column 11column 12column 13column 14column 15column 16
21sd,grt,df34,cve,32dsf,sdf,sdfsdfa,tyd,sersda,qew,sdfsdf,asd,asdasd,cvd,aersedf,hdf,earsderewr,tsdvd,adswrtwsd,hrwe,23sdf,asd,saduiyw,fsed,hvrrerrare,csde,werqeqrwr,cxewqr,ewqeqrewf,we2,cer342dsfqe4r,3431,ce231sd34dsfsdfasdasdfasdsedfsderewrwrtwsdsdfuiywerrareeqrwreqrewfdsfqe4r
32sda,qew,sdfdss,ds,sd342,ef,34sedf,hdf,earsderewr,tsdvd,adswrtwsd,hrwe,23sdf,asd,saduiyw,fsed,hvrrerrare,csde,werqeqrwr,cxewqr,ewqeqrewf,we2,cer342dsfqe4r,3431,ce23sda,qew,sdfasd,ewr,adsasd,cvd,aersedf,hdf,ear1grtcvesdftydqewasdcvdhdftsdvdhrweasdfsedcsdecxewqrwe23431
4323,4523,23de,343hgf,23er,65456fe,234f34,4575235,cfe4r,g445x34,12c345,23d34,d3212123ed,365f3242,4534dc,32wdc21df32sdfsersdfasdaerearads23sadhvrrwerqewqcer342ce23
54weh2334jk,23324k34ekqerkdfhgqsj23jqw2sdadss342sedfsderewrwrtwsdsdfuiywerrareeqrwreqrewfdsfqe4rsdaasdasdsedf
62qewdsefhdftsdvdhrweasdfsedcsdecxewqrwe23431qewewrcvdhdf
72sdfsd34earads23sadhvrrwerqewqcer342ce23sdfadsaerear
832323dehgferfef342354rx34c345d34123ed324234dc
93452334323654562344575cfeg4451223d3212365f4532wdc2
104weh2334jk324k34ekqerkdfhgqsj23jqw
11423
Sheet1

</div>
 
Upvote 0
Peter_SSs said:
With your last data file, it still seems to have a slight error in Sheet1. Cell E5 contains 2 values so the results section should have 2 rows for ID4 shouldn't it?
Click to expand...

You are correct. It should. I did not alter the results of running the macro on Sheet1 so that you could see what was happening. Sheet2 represented my intended outcome. You nailed it!!

Your updated script appears to be working perfectly, so far. Much leaner and has provided the expected output with various changes to the data. Assuming no hiccups, this has the potential to save many hours in looking for what data needs to be cleaned up.

Thank you!!!

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
241
Excel Jason
E
Balcomie
  • Solved
Formula For Gematria
Replies
3
Views
81
StephenCrump
S
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
312
Peter_SSs
Peter_SSs
R
  • Solved
Cleaner VBA Code
Replies
4
Views
283
Joe4
Joe4
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
116
Fluff
Fluff

Forum statistics

Threads
1,224,829
Messages
6,181,224
Members
453,025
Latest member
Hannah_Pham93

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