Help! Need to move data from one cell in one row into several rows - & copy rest of row

Hi,

I've registered in this forum because I'm looking for help with a specific Excel issue. I publish a green building mag, and we run an enquiry system for readers on our website. This is the link: http://passivehouseplus.ie/form/1-enquiries

As you can see, the idea is that readers tell us some info about themselves, including what types of products/services they'd like to receive information about, and we pass their details on to advertisers offering those products/services.

When someone fills out a form, we get data in Excel on their enquiry. It's all in one row - name, contact details, and then the products/services they've enquired about are included in one cell, separated by commas.
So for instance, suppose a listing currently came in as follows (column headings in brackets:

(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation, solar panels, windows

Essentially I want it to run like this:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) solar panels
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) windows

Anyone know any way of automating this?

I'd really appreciate any help. We're a small business looking to make a positive contribution to the world.

Cheers,

Jeff
--

Thanks so much. I'll try those suggestions and let you know how I get on.

Hi Stanley,

I'm the original posters brother. We tried the above but to no avail, so if you wouldn't mind I've attached a sample workbook.

The first three entries are how the data is presented to us by RSForm.
From row 10 is how we want it to look, with all the entries in the categories field extrapolated from one cell into multiple cells, duplicating the rest of the row each time. This will then allow us to sort all users by category, eg, 'all users who chose Thermal Blocks'.

We then need to create new worksheets for each category, but thats a challenge for another day!

Thank you very much in advance for any help you can give.
Dudley

This should do the job.

Please Login or Register  to view this content.

Thanks so much, Stanley, that worked perfectly! You've saved me *many* hours of cutting and pasting misery!

Would it then be possible to run a similar macro to create seperate sheets with all the results from each category?
i.e. A sheet for everyone who chose airtightness, a sheet for everyone who chose Combined Heat and Power

................

Hi again guys,

I overlooked another trick we need to get done in that original sheet at the same time as the fix asked for. Is it possible to run two macros at the same time?

There is a column called ProjectImperatives and like the Categories column it returns multiple answers from a choice of 8 in a single cell.
We need a new colum for each of those choices stating "Yes" or "No" depending on whether the data is in that cell.

So, in the new sheet we need the ProjectImperatives column to go, and be replaced by the following 8:

Certified passive
Indoor air quality
Low running costs
Low allergy & healthy building
Low environmental impact
Natural building
Near passive/low energy
Other (please state)

I've attached a sample again, with the before in one sheet and the after in another, as requested.

In advance, thank you hugely if you can help!

Stan, Jindon,

Thanks so much for your thoughts so far. You've been really helpful. Any help with this other issue would be amazing.

If you ever need to pick my brains on advice regarding energy efficient building and upgrading just DM me.

Cheers,

Jeff

Hi Stan,

Apologies - I gather it was your suggestions that worked. So credit where it's due - thanks! I've been up the walls busy, and I didn't take the time to deal with this.
Thanks to jindon too for trying to help.

Stan,

If it's not terribly cheeky, do you have any ideas on how to resolve the problem raised by the corpo above? I spent hours the other night manually moving that info into different columns. The problem was complicated by the fact that there's hidden line breaks in the project imperatives column (column N), and when I tried to follow the typical instructions on how to remove those breaks (including the Alt 0010 command) I struggled to get it to work.

Any ideas would be greatly appreciated.

I should say that we'll happily set you up with a free digital subscription to the mag if it interests you - or a friend.

Thanks,

Jeff

Thanks Stanley, will do that.

Jindon, to try and make things as straightforward as possible, here's our request all in one on post, hope it makes some sense!


Step 1:

There is a column called ProjectImperatives and like the Categories column it returns multiple answers from a choice of 8 in a single cell.
We need a new colum for each of those choices stating "Yes" or "No" depending on whether the data is in that cell.

So, in the new sheet we need the ProjectImperatives column to go, and be replaced by the following 8:

Certified passive
Indoor air quality
Low running costs
Low allergy & healthy building
Low environmental impact
Natural building
Near passive/low energy
Other (please state)


Step 2:

The original request, where the data from the categories is extracted and duplicates the row for every instance of that data. When I run jindon's code nothing happens, that I can see.


Step 3:
This would just be a handy bonus, as manually doing it isn't that time consuming, but still it would be nice! We would like a new worksheet created for each category.
So a Solar thermal sheet, a Heat pumps sheet etc.
If this complicates the above two steps then it doesn't matter at all!

Thanks again for all the help.

Originally Posted by the corpo

Step 2:

The original request, where the data from the categories is extracted and duplicates the row for every instance of that data. When I run jindon's code nothing happens, that I can see.

If you tried the file in my post #8 and it does nothing then I can not help you more.
You should ask somebody else.

Hi Jindon, thanks for getting back to us.

When I run the macro using the same steps Stanley suggested:

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

the worksheet loses most of the data and bolds what is left. Should I run it differently, and does it matter what version of Excel I'm using?

Hello,

I have a similar problem to the original post. Mine is a little different though, I'm trying to take 1 row with multiple column info and create multiple rows showing the info in 1 column. Hard to explain, here is an example. Please let me know if you can help, thank you!Capture.JPG

Hi again Jindon,
Apologies, I was doing it wrong! Working perfectly now.

Could either of you possibly look at that second request, to expand the ProjectImperatives category?

Thanks again!

Hi again guys,

Been using both bits of code very successfully for months now, which has been brilliant, but I've changed the workbook slightly, and haven't been able to adapt the code accordingly. I've tried!

All I've done is add some extra columns before the column which your macro had been running on (what was 'name' is now 'first name', 'last name' etc,). It was column 15, now it is 19. Thought I could get away with just changing that value, but it's not working.

Is there any chance you could have a look at the macro again for us?? Thanks in advance....Enquiries.csv