Separate comma-delimited values to rows

**sporto** · 10-22-2009, 02:20 PM

I have tried doing this myself, cloning parts and pieces from others, but can not get it to work right.

I have an excel sheet I create with lots of inventory items distributed amongst several locations. The first column is a location field where several 1-2 digit location codes are seperated by commas. I would love to get the macro to copy each row, once for each location code, onto a new tab as a new longer list. Because the amount of data per row varies, I need the entire row to be copied to the new tab.

EX:
GOING FROM THIS...
LOC ITEM etc...
1, 2, 5 ITEM1 moredata...
1, 5, 10 ITEM2 moredata2...

TO THIS ON NEW TAB...
LOC ITEM etc...
1 ITEM1 moredata...
2 ITEM1 moredata...
5 ITEM1 moredata...
1 ITEM2 moredata2...
5 ITEM2 moredata2...
10 ITEM2 moredata2...

**jaslake** · 10-22-2009, 07:57 PM

Hi Sporto
Try the attached and see if it does what you want. Let me know if you have issues.
Hope this helps.
John

PS: I re-read your post and see you want this copied to a new tab. I'll modify the procedure to do that but check the attached book and see if it does what you want.

**jaslake** · 10-22-2009, 09:10 PM

Hi Sporto
The procedure in this book will add a new sheet as you requested. Let me know of any issues.
John

**sporto** · 10-22-2009, 09:21 PM

When I loaded the first file with lots of data, something wierd happens to the data past column D. It might be because there is data that will span dozens of columns, not just 4-5. If it copies the entire row width of data to a new tab, then that would be excellent. (PS - Thank you very much for helping me with this problem.)

**sporto** · 10-22-2009, 09:28 PM

Tried the second file... Everything splits up good and copies to new tab except Column "E" is empty from top to bottom... Odd. I dont know if there is a way to copy a solid row no matter how many columns it spans, or if it needs to detect how wide the row's worth of data goes. Worse case, maybe something that allows to say what last row is if it cannot detect it itself. (PS - Thanks as always for working on this. )

Attached second file (Rev 1) with sample of possible real data. When run, column "E" data disappears on newly created Tab.

**jaslake** · 10-22-2009, 09:47 PM

Hey Sporto
The procedures are built on your sample file. Column E was empty in your sample so i used it for other "stuff" and then I cleared my other "stuff" here

Please Login or Register  to view this content.

and here

Please Login or Register  to view this content.

So, to make the procedure work, I'll probably need a version of your "real" data (proprietary info removed) so I can accommodate the procedure to your "real" data. Can you provide this?
John

**jaslake** · 10-22-2009, 10:42 PM

Hi Sean
Take a look at this and see if it works. As always, let me know of issues.
John

**mikerickson** · 10-23-2009, 12:42 AM

This routine worked for me.

Please Login or Register  to view this content.

**sporto** · 10-23-2009, 01:58 AM

John, you're the man. Works perfectly no matter how much data I put into the page. Thanks for the last-minute entry there Mike. That file did not clone all data properly if there are more columns though.

Love this site and all the people here. Thanks again.

~Sean Ohlrich

**mikerickson** · 10-23-2009, 02:41 AM

To handle a varying number of columns (if the max number of columns is in the top row) change this first section

Please Login or Register  to view this content.

**sporto** · 10-23-2009, 09:50 AM

Wonderful work. Thanks again for the update on the file!

**hamex** · 09-09-2010, 12:43 PM

hi john and hello every one . after one whole day googling i found this website, register and download the rev1.1 file , everything is ok except getting error on below yellow line :

Sub SplitCells()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Sheets("DATA1").Range("B" & Col).Select
For i = 1 To Selection.Rows.Count
Dim splitValues As Variant
splitValues = Split(Selection.Rows(i).Value, ",")
Selection.Rows(i).Resize(UBound(splitValues) - LBound(splitValues) + 1).Value = Application.Transpose(splitValues)
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

can you help me with this ?
thanks

**NBVC** · 09-09-2010, 12:52 PM

Welcome to the forum,

Please read ALL the forum rules before posting... you have already broken 2 of those rules.

**ranispain** · 02-22-2013, 10:00 AM

Hi Everyone, it's my first time on the forum, hope i don't break any rules!
I need exactly what the sporto rev1.xlsm files does but when i run it on my data which has (1807 rows originally) i get error Run-time error "13" Type mismatch but only after it has already successfully processed about 150 rows. I am using excel 2010.

Any help would be much appreciated.

Thanks!
Ranpal

**AB33** · 02-22-2013, 10:06 AM

Ranpal,

hope i don't break any rules.

You have.
You can not post on someone's thread. You need to start your own thread.

Separate comma-delimited values to rows

LinkBack

Thread Tools

Rate This Thread

Display

Separate comma-delimited values to rows

Re: Seperate comma-delimited values in cell to new rows on seperate tab

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Re: Separate comma-delimited values to rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions