Excel macro - change column data to multiple rows

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jean299

Thread Starter
Joined
Apr 19, 2011
Messages
3
In Excel 2007, 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/amount in each category occupies its own row, as follows:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
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


I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help
jeannie
 
Joined
Jul 1, 2005
Messages
8,546
I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

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


HTH :) (welcome to the board)
 

jean299

Thread Starter
Joined
Apr 19, 2011
Messages
3
Brilliant!!! Works like a charm as long as I put the data on a seaparate spreadsheet with only the data, no headers, no other unnecessary columns, just the 14 I need. Thanks so much Bomb #21!

Here is a bit more information for others who might like to try this solution.

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 2 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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top