Parsing and combining delimited text

[demeeder]

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....

ID	LOC1	LOC2
1	a,b,c,d	d,e,f,a
2	d,t,f,r	a,b,c,d
3	a,c,f,v	r,t,e,r
4	r,t,y,w	d,c,b,a
1	a	d
1	b	e
1	c	f
1	d	a
2	d	a
2	t	b
2	f	c
2	r	d

<tbody>
</tbody>

Thanks!

Dave

 

[demeeder]

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

 

[Rick Rothstein]

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

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?

 

[demeeder]

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?

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?

 

[Rick Rothstein]

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?

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.

 

[demeeder]

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.

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

 

[Peter_SSs]

Please let me know if the linked files answers your questions.

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.

 

[demeeder]

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

 

[Peter_SSs]

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.

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1		column 1	column 2	column 3	column 4	column 5	column 6	column 7	column 8	column 9	column 10	column 11	column 12	column 13	column 14	column 15	column 16
2	1	sd,grt,df	34,cve,32	dsf,sdf,sdf	sdfa,tyd,ser	sda,qew,sdf	sdf,asd,asd	asd,cvd,aer	sedf,hdf,ear	sderewr,tsdvd,ads	wrtwsd,hrwe,23	sdf,asd,sad	uiyw,fsed,hvrr	errare,csde,werq	eqrwr,cxewqr,ewq	eqrewf,we2,cer342	dsfqe4r,3431,ce23		1	sd	34	dsf	sdfa	sda	sdf	asd	sedf	sderewr	wrtwsd	sdf	uiyw	errare	eqrwr	eqrewf	dsfqe4r
3	2	sda,qew,sdf	dss,ds,sd	342,ef,34	sedf,hdf,ear	sderewr,tsdvd,ads	wrtwsd,hrwe,23	sdf,asd,sad	uiyw,fsed,hvrr	errare,csde,werq	eqrwr,cxewqr,ewq	eqrewf,we2,cer342	dsfqe4r,3431,ce23	sda,qew,sdf	asd,ewr,ads	asd,cvd,aer	sedf,hdf,ear		1	grt	cve	sdf	tyd	qew	asd	cvd	hdf	tsdvd	hrwe	asd	fsed	csde	cxewqr	we2	3431
4	3	23,45	23,23	de,343	hgf,23	er,65456	fe,234	f34,4575	235,cfe	4r,g445	x34,12	c345,23	d34,d3212	123ed,365f		3242,45	34dc,32wdc2		1	df	32	sdf	ser	sdf	asd	aer	ear	ads	23	sad	hvrr	werq	ewq	cer342	ce23
5	4	we	h23	34	jk,23	324	k34	e	k	qer	k	df	hg	qsj	23	j	qw		2	sda	dss	342	sedf	sderewr	wrtwsd	sdf	uiyw	errare	eqrwr	eqrewf	dsfqe4r	sda	asd	asd	sedf
6																			2	qew	ds	ef	hdf	tsdvd	hrwe	asd	fsed	csde	cxewqr	we2	3431	qew	ewr	cvd	hdf
7																			2	sdf	sd	34	ear	ads	23	sad	hvrr	werq	ewq	cer342	ce23	sdf	ads	aer	ear
8																			3	23	23	de	hgf	er	fe	f34	235	4r	x34	c345	d34	123ed		3242	34dc
9																			3	45	23	343	23	65456	234	4575	cfe	g445	12	23	d3212	365f		45	32wdc2
10																			4	we	h23	34	jk	324	k34	e	k	qer	k	df	hg	qsj	23	j	qw
11																			4				23
Sheet1

</div>

 

[demeeder]

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?

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

 

[Peter_SSs]

You nailed it!!

Thank you!!!

Dave

You're welcome. Thanks for letting us know.