How to convert column A to header

stevec883

New Member
Joined
Apr 3, 2018
Messages
8
Hello, I am trying to Transpose and entire worksheet with repeated values in column A (4 repeating columns) and I want to transpose this (corresponding text value in column B) to rows (of 4 cells). I have thousands, so can't repeatedly copy and paste using the Transpose paste feature. Is there an easy way to link the data in A to the text value in B, and they transpose it?

So

A1=one B1=apple
A2=two B2=orange
A3=thr B3=grape
A4=fou B4=fruit
A5=one B5=food
A6=two B6=drink
A7=thr B7=salad
A8=fou B8=bread

To this:
one two thr fou
apple orange grape fruit
food drink salad bread
 
I did not know we could do that here. Here is part of the sheet (some of the 4 rows get altered later, but I can fix that and then use the formula on those rows once it is clearned up).

[TABLE="width: 552"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]HOME-SecAdmin[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\SecAdmin[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\SecAdmin[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-SRCB[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\fpse\ITSC-CIO\CSMT\SRCB[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\fpse\ITSC-CIO\CSMT\SRCB[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-WebComponents[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\GeneralWeb\WebComponents[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\GeneralWeb\WebComponents[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-AMS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\AMS[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\AMS[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]IIS Logfiles-P01[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Logfiles\W3SVC1[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\save\IIS_Logfiles[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]HQABQDTOZWEBP01[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]IIS Logfiles-P02[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Logfiles\W3SVC1[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\save\IIS_Logfiles[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]HQABQDTOZWEBP02[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-LRP[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\LRP[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\LRP[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Synch_LRP[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]D:\intranet\Admin\LRP\docs[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]E:\Stage\intranet\Admin\LRP\docs[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Synch_MORE_DOCS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]D:\intranet\GeneralWeb\WebApps\MORE\DOCS[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]E:\Stage\intranet\GeneralWeb\WebApps\MORE\DOCS[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-INTRANET[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-CHS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\CHS[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\CHS[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-PHXAO[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Phoenix[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\phoenix[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 552"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
[TABLE="width: 552"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]HOME-SecAdmin[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\SecAdmin[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\SecAdmin[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-SRCB[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\fpse\ITSC-CIO\CSMT\SRCB[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\fpse\ITSC-CIO\CSMT\SRCB[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-WebComponents[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\GeneralWeb\WebComponents[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\GeneralWeb\WebComponents[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-AMS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\AMS[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\AMS[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]IIS Logfiles-P01[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Logfiles\W3SVC1[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\save\IIS_Logfiles[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]HQABQDTOZWEBP01[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]IIS Logfiles-P02[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Logfiles\W3SVC1[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\save\IIS_Logfiles[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]HQABQDTOZWEBP02[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-LRP[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\LRP[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\LRP[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Synch_LRP[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]D:\intranet\Admin\LRP\docs[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]E:\Stage\intranet\Admin\LRP\docs[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Synch_MORE_DOCS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]D:\intranet\GeneralWeb\WebApps\MORE\DOCS[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]E:\Stage\intranet\GeneralWeb\WebApps\MORE\DOCS[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-INTRANET[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-CHS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\CHS[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\CHS[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-PHXAO[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Phoenix[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\phoenix[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]WWW-FE-EHSC[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\internet\ehsc[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Edges[/TD]
[TD]HQABQDTOZWEBP01, HQABQDTOZWEBP02[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Plugins[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\plugins[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\plugins[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-DES[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\OtherPrgms\DES[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\OtherPrgms\DES[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-FLSA_Training[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\flsa_training[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\flsa_training[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-OTPUM[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\otpum[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\otpum[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Supervisor101[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\supervisor101[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Supervisor101[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Bemidji[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Facilities\Areas\Bemidji[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Facilities\Areas\Bemidji[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Security[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\security[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\security[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-OITComponents[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\ITSC-Cio\OITComponents[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\ITSC-Cio\OITComponents[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-OIT_TFS[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\oittfs[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\oittfs[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Telecom[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\ITSC-Cio\telecom[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\ITSC-Cio\telecom[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]_HOME-INTRANET[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-TPICPSA[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\OtherPrgms\IHPES\ORAP\TPICPSA[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\OtherPrgms\IHPES\ORAP\TPICPSA[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-ROBOTS_TXT[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-ABQ[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Facilities\Areas\albuquerque[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Facilities\Areas\albuquerque[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-IRT[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\ITSC-Cio\Security\IRT[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\ITSC-Cio\Security\IRT[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-ITAC[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\ITAC[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\ITAC[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-3PBAR[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\3pbar[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\3pbar[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Webcident[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\webcident[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\webcident[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-GDM[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\NDW\GDM[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\NDW\GDM[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-GeneralWeb[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\GeneralWeb[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\GeneralWeb[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-Conference_Room[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\Admin\ConferenceRoomScheduler[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\Admin\ConferenceRoomScheduler[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]HOME-HSPD12[/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]E:\Stage\intranet\HSPD12[/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\intranet\HSPD12[/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBHOMEPROD[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

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