Converting horizontal data to vertical data with repeating data

[Traceelewis71]

I currently have a report of 8000 plus employees who have 12 rows of data each. each row of data has 3 columns that are represented for each month of the year. This is how my current reporting feature creates the report. the software I need to upload this data into is in the format below this chart.

YEAR MONTH LINE 14 LINE 15 LINE 16 STATUS EE ID 2017 1 1C 165.13 2H F A02209 2017 2 1C 166.08 2H F A02209 2017 3 1C 62.34 2H F A02209 2017 4 1C 0 2H F A02209 2017 5 1C 0 2H F A02209 2017 6 1C 0 2H F A02209 2017 7 1H 2A F A02209 2017 8 1H 2A F A02209 2017 9 1H 2A F A02209 2017 10 1H 2B F A02209 2017 11 1H 2B P A02209 2017 12 1H 2B P A02209 <tbody> </tbody>

<tbody>
</tbody>

I need this data to now be in this format

EE ID MONTH 1 LINE 14 MONTH 1 LINE 15 MONTH 1 LINE 16 MONTH 2 LINE 14 MONTH 2 LINE 15 MONTH 2 LINE 16 MONTH 3 LINE 14 MONTH 3 LINE 15 MONTH 3 LINE 16 MONTH 4 LINE 14 MONTH 4 LINE 15 MONTH 4 LINE 16 MONTH 1 LINE 14 MONTH 5 LINE 15 MONTH 5 LINE 16 MONTH 6 LINE 14 MONTH 6 LINE 15 MONTH 6 LINE 16 MONTH 7 LINE 14 MONTH 7 LINE 15 MONTH 7 LINE 16 MONTH 8 LINE 14 MONTH 8 LINE 15 MONTH 8 LINE 16 MONTH 9 LINE 14 MONTH 9 LINE 15 MONTH 9 LINE 16 MONTH 10 LINE 14 MONTH 10 LINE 15 MONTH 10 LINE 16 MONTH 11 LINE 14 MONTH 11 LINE 15 MONTH 11 LINE 16 MONTH 12 LINE 14 MONTH 12 LINE 15 MONTH 12 LINE 16 A02209 1C 165.13 2H 1C 166.08 2H 1C 62.34 2H 1C 0 2H 1C 0 2H 1C 0 2H 1H 2A 1H 2A 1H 2A 1H 2A 1H 2A 1H 2A B14114 1C 159.84 2H 1C 163.09 2H 1C 179.43 2H 1C 179.56 2H 1C 154.74 2H 1C 179.93 2H 1C 174.07 2H 1C 175.32 2H 1C 189.66 2H 1C 167.59 2H 1C 178.18 2H 1C 182.55 2H F13830 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C 1E 0 2C H14072 1C 172.91 2H 1C 155.61 2H 1C 159.6 2H 1C 177.06 2H 1C 106.98 2H 1H 2A 1H 2A 1H 2A 1H 2A 1H 2A 1H 2A 1H 2A <tbody> </tbody>

<tbody>
</tbody>

I am currently keying the data as I can't figure out a formula to get this data converted. This will take me weeks to do and leaves too much room for error. I've tried an index match formula but that's not working. I don't even know where to begin. Should I use access or SQL. I don't think SumIF formula will work because I don't want the employees' data totaled up.

 

[tonyyy]

Traceelewis71,

You might consider a macro approach...

Sub TransposeData_1062902()
Application.ScreenUpdating = False
Dim arr1 As Variant, arr2 As Variant
Dim LastRow As Long, r1 As Long, c1 As Long, r2 As Long, c2 As Long
Dim i As Long, j As Long, k As Long, n As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
arr1 = Range("A2:G" & LastRow).Value
ReDim arr2(1 To UBound(arr1) / 12, 1 To 37)
r2 = 1
c2 = 2

''''Transpose data
For r1 = LBound(arr1) To UBound(arr1)
    For c1 = 3 To 5
        arr2(r2, 1) = arr1(r1, 7)
        arr2(r2, c2) = arr1(r1, c1)
        c2 = c2 + 1
        If c1 Mod 5 = 0 Then GoTo nxt1
    Next c1
nxt1:
    If r1 Mod 12 = 0 Then
        r2 = r2 + 1
        c2 = 2
    End If
Next r1

''''Add new worksheet, paste transposed data
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Range("A2:AK" & UBound(arr1) / 12 + 1).Value = arr2

''''Add headers
ActiveSheet.Cells(1, 1).Value = "EE ID"
i = 2
n = 1
Do While i < 37
    j = 14
    k = 1
    Do Until k = 4
        ActiveSheet.Cells(1, i) = "Month " & n & " Line " & j
        j = j + 1
        i = i + 1
        k = k + 1
    Loop
    n = n + 1
Loop
ActiveSheet.Rows("1:1").WrapText = True
End Sub

If you're not familiar with macros, please see the Adding Code to an Excel Workbook tutorial on the Contextures website, and in particular, the section titled Copy Excel VBA Code to a Regular Module.

Briefly, this macro copies the existing data into an array (an array is a special memory allocation) then line by line transposes the data into your requested format. A new sheet is added and the reformatted data is pasted, then headers are added.

Cheers,

tonyyy

 

[Traceelewis71]

THANK YOU SO MUCH!!! That worked perfectly! I'm going to find a class on Macros

 

[tonyyy]

You're very welcome, Traceelewis71...