split cell data into multiple rows and keep associated data

**aeb430** · 02-23-2015, 10:31 PM

I have an sheet that contains a list of groups and group members. Each row contains one group name in A1 and group members in B1. The members are delimited by carriage return and I know how to use text2columns with CRLF as a delimiter, but I need the data in rows.

My end result is to have each group member listed on a separate row with the group name and I'm hoping this can be done with a macro.

Added a sample sheet

**davesexcel** · 02-24-2015, 03:04 AM

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**aeb430** · 02-24-2015, 09:32 AM

Thanks for the help. I have attached a sample sheet

**apo** · 02-25-2015, 02:27 AM

Hi..

I don't know how many rows you're going to have.. but in any case..this does it nice and fast...

Please Login or Register  to view this content.

**snb** · 02-25-2015, 06:39 AM

Please Login or Register  to view this content.

**aeb430** · 02-26-2015, 03:00 PM

Thanks for the help on this. One thing I should have mentioned is that while my sample data contains only three values in cells I need to split, the actual data file contains many values (over 900 in one of the cells). Is there any way to have the code split all the values regardless of how many exist in each cell?

Thanks,
aeb430

**apo** · 02-26-2015, 07:22 PM

Hi..

I am pretty sure the problem with mine was this line:

Please Login or Register  to view this content.

With so many values in 1 cell.. I was exceeding the 256 character limit that Transpose could handle for 1 element of an array.. I think that's how to put it.. if I am wrong.. someone please let me know..

Replacing that with:

Please Login or Register  to view this content.

And then Redimming the Upper Bound of the 1st Dimension of the y array to the value of b got me around that.

Replace y with b in a couple of places (upper bound of "i" loop and "If k>" test) and it works.

Please Login or Register  to view this content.

I must point out that snb's code doesn't have this same issue.. and works for me as it is.. although i didn't test it on cells with over 900 values.. (maybe 150+).. but it shouldn't make a difference..

I have attached a Workbook showing snb's and my code working.. check B2 on Sheet1(thats where i added more values)..

**aeb430** · 03-04-2015, 06:49 PM

awesome, thanks guys!

split cell data into multiple rows and keep associated data

LinkBack

Thread Tools

Rate This Thread

Display

split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Re: split cell data into multiple rows and keep associated data

Thread Information

Users Browsing this Thread

Similar Threads

Split one row with data sets into multiple rows with one data set

[SOLVED] Split a cell of data into multiple rows

Split a row of data into multiple rows

Row split in to multiple rows based on cell data

Split cell data into multiple new rows and copy other column values

Bookmarks

Bookmarks

Posting Permissions