Articles

Excel macro - column data to multiple rows

April 18, 2011 at 19:40:51
Specs: Windows XP

I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item in each category occupies its own row, as follows:

Eg.

From this:

col1 col2 category1 category2 category3 etc...
aaa bbb $55
ccc ddd $44 $66


To this:

col1 col2 category amount
aaa bbb 2 $55
ccc ddd 1 $44
ccc ddd 3 $66

Can anyone assist?

Many thanks,
jeannie


See More: Excel macro - column data to multiple rows

Report •


#1
April 18, 2011 at 19:55:24

You could write a macro to do this, but if it's a one time excercise, it might be easier to do it manually. Turn on filters, one by one for each of the category columns; choose non-empty then hide non-relevant columns, copy and paste the results to a new sheet. Let me know if it requires further explanation (or a macro).

Report •

#2
April 18, 2011 at 21:26:16

Many thanks for your reply,

Yes, I have worked out a simple and tedious rountine for doing sort/filter, copy, paste. But this is something that needs to be done on many sheets on a frequent basis. So I do need an automatic way of doing the routine.

I figure a macro is required, but I do not know how to write it.

Just to clarify as the message above lost its spaces when it was saved. Here again is how I need the information changed:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc...
aaa------bbb-------------------------$55
ccc------ddd------$44---------------------------------$66


To this:

col1------col2------category------amount
aaa-------bbb------2-----------------$55
ccc-------ddd------1-----------------$44
ccc-------ddd------3-----------------$66


Report •

#3
April 18, 2011 at 22:27:30

OK, I don't mind helping. Do you have any experience writing macros? This one shouldn't be too hard, but it helps to know where we are starting from.

Report •

Related Solutions

#4
April 18, 2011 at 22:30:10

I have copied and pasted macros and then made adjustments to them. I've got the gist but have no idea how to start from scratch.

Report •

#5
April 18, 2011 at 23:04:11

I'll get back to you tomorrow.

Report •

#6
April 19, 2011 at 15:23:54

I got this macro from TechGuy forums and it does the job very well:
Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub


Report •

#7
April 19, 2011 at 16:46:37

Here is a bit more information if you want to do this yourself or analyse how the macro works.

The macro maps the data from column A:N for the source data and from column P:S for the resulting data.

It is written specifically for two columns of fixed reference data and 12 columns of data that will be transposed to two columns.

You need to setup your data correctly before running the macro.

1. Paste the data on a sheet with only the 14 required columns (columns A:N). That is two columns of fixed data and 12 columns of data to be transposed.

2. Delete the column headers so data starts on row 1 column A.

3. Save as a macro enabled workbook.

4. Open the sheet and run the macro

5. Voila! The result will appear in 4 columns, P:S, starting on row 1.


Report •

#8
April 19, 2011 at 17:12:37

Sub condenser()
Range("C2").Activate
Do until Activecell.offset(0, -2) = ""
currRow = Activecell.Row
Do until Activecell <> ""
Activecell.Offset(0, 1).Activate
Loop
Cells(currRow, 15) = Cells(1, Activecell.Column)
Cells(currRow, 16) = Activecell
Cells(currRow + 1, 3).Activate
Loop
End sub

Report •

#9
April 19, 2011 at 22:58:54

Thank you Mr Lobster! I will try your solution as well. I can see the two macros are different but don't know enough about macros to really analyse the difference.

Report •

#10
April 20, 2011 at 05:59:08

One of the main differences between the 2 macros is that the TechGuy's code does not Activate any cells, while Grok's does.

It is typically considered to be more efficient if the code does not Activate (or Select) the range. All of the actions are essentially done "in the background".

Granted, in simple macros such as these, the difference is probably negligible, but it's something to keep in mind.

P.S. When posting code in this forum, please try to use the pre tags as described in the How To accessible via in my signature line so that the code will retain it's formatting:

Sub condenser()
 Range("C2").Activate
    Do until Activecell.offset(0, -2) = ""
       currRow = Activecell.Row
          Do until Activecell <> ""
            Activecell.Offset(0, 1).Activate
          Loop
       Cells(currRow, 15) = Cells(1, Activecell.Column)
       Cells(currRow, 16) = Activecell
       Cells(currRow + 1, 3).Activate
     Loop
End sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
April 20, 2011 at 14:59:37

Aha! I see. The code as given to me was not in this format. But I can see how the format makes it a lot easier to understand. I will look out for the tags next time and keep this in mind. Thanks for the tip!

I used the Techguys code (http://forums.techguy.org/business-applications/992235-excel-macro-change-column-data.html#post7899723) as it was the first I received. I haven't had time to try the other option offered on this page. I also received another piece of much longer code that also handled the column headings for both the source and result and put the results on a new sheet, both of which were good refinements. That was from MrExcel.com (http://www.mrexcel.com/forum/showthread.php?t=544487).


Report •

#12
April 20, 2011 at 15:02:44

By the way, there are some bizarrre kontera links on individual words in my messages on this page. Does anyone know why? Is there something on my computer that is causing this or has a bug crept into the discussion board?

I actually reworded some posts and got rid of some links, but some are still there particularly the one in the code, which can't be reworded.

It also seems these links sometimes disappear. I have tried to get rid of them by rewording editable bits of text.

And now, after some research I have added kontera.com as blocked on the privacy tab in Internet Options. (I can't access the security tab here at work). I also changed the Hosts file. But neither of these fixes seemed to stop these annoying links.


Report •


Ask Question