Excel macro - change column data to multiple rows

NB: I am using Excel 2007

jean299,

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

Here is a sample. Note I had to cut off the 12th column as the HTMLMaker would only allow 13 columns and I have 14. The 12th column is just like the eleven before it.Excel 2007

I got this macro from TechGuy forums and it seems to work quite 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

jean299,

Can you give us a screenshot of just column N?

Is Fund actually in cell A1 or A2?

Your To this does not correspond to the raw data in the From this.

A screenshot of just the raw data would help (without the From this).

And, another screenshot of just the results (from the raw data) would help.

The example data above is just a demo of how the data is set out. The numbers don't actually match in the 'From this' and 'To this' sections. If I could get the full set of columns into the HTML Maker, I could show you from columns A to S how the macro works. It is brilliant. 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 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.

jean299,


Sample raw data on worksheet Sheet1 before the macro:







After the macro on a new worksheet Results (just a small sample for brevity):







Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


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.




Before you run the macro, save your workbook, Save As, a macro enabled workbook.


Then run the ReorgData macro.

jean299,

Be back in a little while with the code for 5. Voila! The result will appear in 4 columns, P:S, starting on row 1.

Aha! I see! So this is another macro that does the same thing, I guess. But it looks as though you don't have to remove the column headings. Is that right? And you have the results appear on a new sheet, which is good. It really doesn't matter where the result appears and probably a new sheet is best.