Copying rows based on multiple values in a cell

**mruetz** · 01-19-2018, 06:01 PM

Here is the problem, I am trying to solve:

1. I have a extracted spreadsheet where each row is a complete record but there are various columns with multiple values (comma separated)
2. I would like to explode the various values by creating new rows, one for each value (I have sample data attached)

Does anyone have any ideas if this is possible through Excel commands or is this something for VBA Macros?

Any ideas or help on how to proceed would be helpful.

Many thanks, Matt

**xladept** · 01-19-2018, 07:00 PM

Inelegant but functional

Please Login or Register  to view this content.

**sintek** · 01-20-2018, 03:56 AM

@ xladept
Edit...Just a heads Up
Have a look at your code...Renders inaccurate result for item 4

Please Login or Register  to view this content.

**xladept** · 01-20-2018, 01:25 PM

@ Sintek - What are you getting with my code? I just reran it on the sample and the results are exactly the desired results. I then ran it with your version that applies just to sheet1 and got the expected results

**sintek** · 01-20-2018, 01:40 PM

After running code, there are only 2 instances of Item 4
See upload highlighted areas...

**xladept** · 01-20-2018, 01:43 PM

Here's what I got with your sheet1:

Data Range

	A	B	C
4	Item #	Description	Type Codes
5	1	Tall Bookshelf	Pine
6	1	Tall Bookshelf	Bookshelf
7	2	Medium Bookshelf	Walnut
8	2	Medium Bookshelf	Bookshelf
9	3	Dining Table	Walnut
10	3	Dining Table	Table
11	4	Child Wooden Chair	Pine
12	4	Child Wooden Chair	Chair
13	4	Child Wooden Chair	Child
14	5	Pink Couch	Couch

**sintek** · 01-20-2018, 01:52 PM

results.gif

Am i losing my mind lol...The above is what i get after running your code. What am i doing wrong

Herewith result required...
Expected-result.gif

Edit..
Excel versions perhaps rendering different results?

**xladept** · 01-20-2018, 02:06 PM

It seems that the entire row insertion is unreliable with just the 3 fields defined. The program seems to be stable when just inserting the cells and this version works every time:

Please Login or Register  to view this content.

**sintek** · 01-20-2018, 02:11 PM

Now that test fine...So i wasn't losing my mind...Thanks xladept...

**xladept** · 01-20-2018, 02:27 PM

Thank you!

Sintek.

**xladept** · 01-20-2018, 03:19 PM

If you need the entire row to be inserted this, finally, works:

Please Login or Register  to view this content.

**sintek** · 01-21-2018, 02:26 AM

Seems we've hijacked OP's thread lol...

**xladept** · 01-21-2018, 01:13 PM

But we've left OP with a working solution thanks to you!

Copying rows based on multiple values in a cell

LinkBack

Thread Tools

Rate This Thread

Display

Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Re: Copying rows based on multiple values in a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Insert Multiple rows based on the values in the cell

delete rows based on multiple cell values

Excel 2007 : Coping Rows from multiple workbooks based on cell values

hide/unhide rows based on multiple cell values

Filter rows based on cell values in multiple columns

Help: Code to hide multiple rows based on different cell values

copy rows based on multiple cell values

Bookmarks

Bookmarks

Posting Permissions