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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
433
charlesstricklin
charlesstricklin
D
  • Solved
Column Data to Rows
Replies
5
Views
436
Fluff
Fluff
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I
Lacan
  • Solved
Convert Months Spanish Language to Portuguese and do Lookups + Sum?
Replies
2
Views
187
Lacan
Lacan
M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
263
mike4777
M

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

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