How to convert column A to header

[stevec883]

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

 

[stevec883]

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]

 

[stevec883]

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

 

[stevec883]

.