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.
[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.