Transposing data from one big list into a table

**kolson** · 02-09-2010, 05:18 PM

I received from a colleague information in a Word doc that is arranged as:

Style Number
Fit Note
Fit Note

Style Number2
Fit Note
Fit Note

etc.

I need this information in horizontal table form to look like:

Style Number Fit Note Fit Note
Style Number 2 Fit Note Fit Note

Transpose doesn't solve my problem because it puts all the information in one row. Can anyone help me to figure out how to transpose this data into a table? I am assuming it would have something to do with arrays and transposing, or a macro, but am just not sure how to go about this. I run into this issue all the time.

Thanks!

**StephenR** · 02-10-2010, 07:56 AM

Can you attach a sample workbook showing exactly how the data appears when you receive it?

**kolson** · 02-10-2010, 01:31 PM

Originally Posted by StephenR

Can you attach a sample workbook showing exactly how the data appears when you receive it?

Please see attached worksheet.

Thanks for your help.

**StephenR** · 02-10-2010, 02:06 PM

Try this. Results on sheet2.

Code:

Sub x()

Dim r As Range

Application.ScreenUpdating = False

With Sheet1
    For Each r In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
        r.Copy
        Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial Transpose:=True
    Next r
End With

Application.ScreenUpdating = True

End Sub

**kolson** · 02-10-2010, 06:03 PM

That is amazing, thank you so much! That just cut my workload by a day.

I am a beginner with Macros, so a co-worker helped me to run it. For other beginners, here are his instructions:

1. Make sure you have a backup copy of your excel file
2. In Excel, hit Alt + F11 to open the Macro Editor
3. Double Click on ThisWorkbook
4. Paste in the macro
5. Click any line in the macro code
6. Hit F5 to run the macro
7. The results are on Sheet2