Convert Columns to Rows

1.  Copy data in chunks (for example, all the dates)
2.  paste special, select transpose check box, click ok
3.  Ctrl+select all Scores, copy, paste special, transpose, ok
4.  Ctrl+select all N-values, copy, paste special, transpose, ok

Will that work for you?

Kyle

Hi, RecipeDan.

As it stands, it's very straightforward. However, I'm assuming that reality is more complicated - lots more columns and/or rows, perhaps? So, how many of each?

Thanks,
Brian.

@Brian: Yes its a little more complicated. I have 24 columns (12 months + 12 N Values) and the number of rows will very based on content.

@Kyle: I am looking for a macro that the users can run. I am working with users who have a very basic knowledge of Excel. The similiar the better.

RecipeDan,

OK, let's start with the attached. The macro is...

Option Explicit

Sub Reformat_Data()
Dim xInput As Worksheet
Dim xOutput As Worksheet
Dim xLast_Row As Long
Dim xOut_Row As Long
Dim i As Long, j As Long

Set xInput = Sheets("Input")

xLast_Row = xInput.Range("A1").SpecialCells(xlLastCell).Row
If xLast_Row < 3 Then
    MsgBox ("No Data found. Run cancelled.")
    Exit Sub
End If

Application.ScreenUpdating = False
    Set xOutput = Sheets.Add
    
    xOutput.Range("A1:D1") = Array("Row", "MthYr", "Score", "N")
    xOut_Row = 1
    
    With xInput
        For i = 3 To xLast_Row
            For j = 2 To 24 Step 2
                xOut_Row = xOut_Row + 1
                xOutput.Cells(xOut_Row, 1) = .Cells(i, 1)
                xOutput.Cells(xOut_Row, 2) = .Cells(1, j)
                xOutput.Cells(xOut_Row, 3) = .Cells(i, j)
                xOutput.Cells(xOut_Row, 4) = .Cells(i, j + 1)
            Next
        Next
    
    End With
    
    xOutput.Columns("B:B").NumberFormat = "mmm-yyyy"
    
Application.ScreenUpdating = True
    
End Sub

Select allOpen in new window

Regards,
Brian.
Test-V3.xlsm

Hi Brian:

It looks good. It is exactly what I am looking for. A couple questions, do I have to put the letters Column A? If I don't have 24 columns, do I change the 24 to how ever many columns I have?

Thanks
Dan

Hi Brain:

What if I have 6 months of data (12 columns)? Also I thought of something else past data from prior years has fields before the actual data. I attached an example.

I am trying to think of all scenarios while I have this question open.

Dan

here is the example
TestBook1.xlsx

RecipeDan,

What if I have 6 months of data (12 columns)?

Then you use 11. However, this has been overtaken by ...

past data from prior years has fields before the actual data.

It depends what you want to do with the extra fields - simply ignore them or include them as I did with "Row" in V3?

I am trying to think of all scenarios while I have this question open.

I understand why you think that's useful but it tends to waste both our time! Even if you find you've forgotten something then
(a) Post in the closed question. (Not relevant for all Experts, but I keep an eye on "my" closed questions for at least a couple of weeks.)
(b) Ask a new question, including a link to the closed one.

Regards,
Brian.

OK sounds good..Thank you for your help

RecipeDan,

Thanks, but what about columns A and B?!

Regards,
Brian.