Converting horizontal data to vertical data with repeating data

T

Traceelewis71

New Member
Joined
Dec 5, 2017
Messages
12
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 770"]
<tbody>[TR]
[TD]YEAR[/TD]
[TD]MONTH[/TD]
[TD]LINE 14[/TD]
[TD]LINE 15[/TD]
[TD]LINE 16[/TD]
[TD]STATUS[/TD]
[TD]EE ID[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]1[/TD]
[TD]1C[/TD]
[TD="align: right"]165.13[/TD]
[TD]2H[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]2[/TD]
[TD]1C[/TD]
[TD="align: right"]166.08[/TD]
[TD]2H[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]3[/TD]
[TD]1C[/TD]
[TD="align: right"]62.34[/TD]
[TD]2H[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]4[/TD]
[TD]1C[/TD]
[TD="align: right"]0[/TD]
[TD]2H[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]5[/TD]
[TD]1C[/TD]
[TD="align: right"]0[/TD]
[TD]2H[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]6[/TD]
[TD]1C[/TD]
[TD="align: right"]0[/TD]
[TD]2H[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]7[/TD]
[TD]1H[/TD]
[TD][/TD]
[TD]2A[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]8[/TD]
[TD]1H[/TD]
[TD][/TD]
[TD]2A[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]9[/TD]
[TD]1H[/TD]
[TD][/TD]
[TD]2A[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]10[/TD]
[TD]1H[/TD]
[TD][/TD]
[TD]2B[/TD]
[TD]F[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]11[/TD]
[TD]1H[/TD]
[TD][/TD]
[TD]2B[/TD]
[TD]P[/TD]
[TD]A02209[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]12[/TD]
[TD]1H[/TD]
[TD][/TD]
[TD]2B[/TD]
[TD]P[/TD]
[TD]A02209[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I need this data to now be in this format

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

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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Traceelewis71,

You might consider a macro approach...

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

charlesstricklin
  • Solved
How to convert rows of times in Xh Ym format into usable numbers?
Replies
2
Views
478
charlesstricklin
charlesstricklin
C
  • Solved
Can I search multiple columns for cells that contain a specific number plus other random text?
Replies
3
Views
34
Fluff
Fluff
D
  • Solved
Column Data to Rows
Replies
5
Views
440
Fluff
Fluff
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
287
ItalianPlatinum
I
Lacan
  • Solved
Convert Months Spanish Language to Portuguese and do Lookups + Sum?
Replies
2
Views
190
Lacan
Lacan

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top