Help reformatting spreadsheet: Transpose?

N

nairbjr

New Member
Joined
Dec 12, 2013
Messages
6
Hello,

I need some help reformatting some files I've been receiving. The files need to be "transposed"? in such a way to be uploaded by one of the systems I use but it's been taking me forever to do this manually and I'm hoping there's some type of simpler solution. I have been using a series of macros to handle other tasks so I would prefer a similar solution, if possible.

An item of note is that the input that I received is not always 100% the same. For example, We always receive the member ID, however, we only occasionally receive that members name and source/identifier. So this solution should account for the potential to have blank columns.

I've tried pivot tables, and being creative with the Paste Special Transpose feature, but no luck in getting close to what I need.

Input:
[TABLE="class: grid, width: 711"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Source[/TD]
[TD]AAAA[/TD]
[TD]BBBB[/TD]
[TD]CCCC[/TD]
[TD]DDDD[/TD]
[TD]EEEE[/TD]
[TD]FFFF[/TD]
[TD]GGGG[/TD]
[TD]HHHH[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]333333333[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]444444444[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]555555555[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]666666666[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]777777777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]888888888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]999999999[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Output: I only showed the first two members, but the pattern would continue for the remainder. The output was sorted by Member ID, then Project.
[TABLE="class: grid, width: 219"]
<tbody>[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]AAAA[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]BBBB[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]CCCC[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]DDDD[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]FFFF[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]GGGG[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]HHHH[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]AAAA[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]BBBB[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]CCCC[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]DDDD[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]EEEE[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]FFFF[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]GGGG[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]HHHH[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAAA[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]BBBB[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]CCCC[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]DDDD[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]EEEE[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]FFFF[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]GGGG[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]HHHH[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
BS
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
care to share your email address so I can send the sample file to you?


nairbjr said:
Hello,

I need some help reformatting some files I've been receiving. The files need to be "transposed"? in such a way to be uploaded by one of the systems I use but it's been taking me forever to do this manually and I'm hoping there's some type of simpler solution. I have been using a series of macros to handle other tasks so I would prefer a similar solution, if possible.

An item of note is that the input that I received is not always 100% the same. For example, We always receive the member ID, however, we only occasionally receive that members name and source/identifier. So this solution should account for the potential to have blank columns.

I've tried pivot tables, and being creative with the Paste Special Transpose feature, but no luck in getting close to what I need.

Input:
[TABLE="class: grid, width: 711"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Source[/TD]
[TD]AAAA[/TD]
[TD]BBBB[/TD]
[TD]CCCC[/TD]
[TD]DDDD[/TD]
[TD]EEEE[/TD]
[TD]FFFF[/TD]
[TD]GGGG[/TD]
[TD]HHHH[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]333333333[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]444444444[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]555555555[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]666666666[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]777777777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]888888888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]999999999[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Output: I only showed the first two members, but the pattern would continue for the remainder. The output was sorted by Member ID, then Project.
[TABLE="class: grid, width: 219"]
<tbody>[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]AAAA[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]BBBB[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]CCCC[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]DDDD[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]FFFF[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]GGGG[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]HHHH[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]AAAA[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]BBBB[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]CCCC[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]DDDD[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]EEEE[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]FFFF[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]GGGG[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]111111111[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]HHHH[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAAA[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]BBBB[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]CCCC[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]DDDD[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]EEEE[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]FFFF[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]GGGG[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]222222222[/TD]
[TD][/TD]
[TD][/TD]
[TD]HHHH[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
BS
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Match Formula with Datedif
Replies
3
Views
418
AlanY
A
E
  • Question Question
If statement
Replies
4
Views
107
xlhelp15
xlhelp15
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
248
dshtesy
D
S
  • Solved
SumIf problem
Replies
2
Views
317
sharshra
S
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
263
alessiaexcels
A

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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