How to take data from columns in some rows and insert in new row?

**janschepens** · 04-24-2012, 12:59 PM

I've got a big table that looks like this:

	A	B	C
1	USA	English	Spanish
2	England	English
3	Canada	English	French

Now whenever there is any data present in column C I need to copy the row's content into a new row below, and insert the data in column B. So it looks like this:

	A	B	C
1	USA	English
2	USA	Spanish
3	England	English
4	Canada	English
5	Canada	French

I have been messing around but can't find a good way to accomplish this. Would anyone please help me?

**arlu1201** · 04-24-2012, 02:10 PM

I have got a VBA code for you.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.

**tkowal** · 04-24-2012, 02:28 PM

This seems to work:

Insert this macro into a new module and run the macro CleanData

Please Login or Register  to view this content.

**janschepens** · 04-24-2012, 03:38 PM

Wow! Many thanks for your reply! Although I used to program in my teens I'm afraid I can't tweak your scripts so that they fit the actual worksheet I'm working with. The example I gave was simplified (as I thought the answer was simple as well).

The actual table has data in columns A-Q. The columns with data I need to move to a new row (if applicable) are J, K & L. Every time there is something in either of these columns each cell needs a new row with column A copied to the new row. So if there is data in column J then 1 new row is needed, if there is data in J & K then 2 new rows are needed, etc.

I tried modifying both your scripts but without success. Would give me a hint what variables to change for this?

Again, a big thank you!

**tkowal** · 04-24-2012, 03:47 PM

What about columns B-I and M-Q do they need to be copied down as well?

Does the data in J-L need to be cleared?

A small sample workbook with the raw data and another sheet of what you want it to look like would be of great help in assisting you.

**tkowal** · 04-24-2012, 04:41 PM

Took a guess on what you wanted. The code is not very efficient but works. I attached a work book with the macro ...
FunkyCopyDown.xlsm

Please Login or Register  to view this content.

**janschepens** · 04-24-2012, 04:56 PM

This seems to solve my troubles, wow. Many thanks for your swift help! I wouldn't mind sending you money for a beer if you have a way to donate to you.

Cheers, and thank you!

I will test this on my live data tomorrow and report back.

**janschepens** · 04-25-2012, 04:55 AM

Originally Posted by janschepens

I will test this on my live data tomorrow and report back.

This works just as I want it to, thank you again!

**tkowal** · 04-25-2012, 10:07 AM

I am glad.... just bear in mind that I took a brute force approach to your problem. The code is done without any finesse or thought of efficiency.

Enjoy!