merging multiple duplicate rows on large files

[wrlc]

Hello I'm trying to run the script below on the data shown in the "raw" sheet of the attached workbook. When I run it on just the first 5-10 rows, it works just fine as shown in the "intended" sheet. When I try to run it on the full 30,000, I get a "run time error 1004" and this line is flagged in the debugger: Cells(1, "H").Resize(nIndex, 2) = sNames. Can anyone figure out why the script is dying when run on the 30,000 rows? Thanks for any help!

Please Login or Register  to view this content.

[AB33]

I have adjusted the code and it will not error, but I am not sure on the out put.

[wrlc]

This is much better, but you are right that the output is a definitely a bit strange. It seems it is munging some of the cell data together rather than keeping each cell a separate entity. Multiple barcodes are being included in the same cell separated by commas.

[AB33]

Need to show your desired out as it is not clear on the attached.

[wrlc]

Need to show your desired out as it is not clear on the attached.

Sure thing. Thanks again for your help. So the data would start out like this:

Barcode	12345
Permanent	auc
Temporary	auc
Barcode	12346
Permanent	auz
Temporary	auz
Barcode	12347
Permanent	aur
Temporary	auc

And be transformed into this:

Barcode	12345	12346	12347
Permanent	auc	auz	aur
Temporary	auc	auz	auc

Or even more ideal (I'm just going to transpose it anyway):

Barcode	Permanent	Temporary
12345	auc	auc
12346	auz	auz
12347	aur	auc

I have been able to accomplish this task using a Wend, While script, but the transform takes forever on a set of 30,000. The base script that I included in the xlsm files above works so much faster. I just need the output to display properly.

[AB33]

I assumed you wanted the items on a separate columns. It is difficult to see output on text format.
The output is on column C, so as to see the data, but can easily changed.

[wrlc]

Thanks so much. This is perfect output and perfect script. I really appreciate the help. So much faster and more efficient than what I was using.