How to convert column A to header

S

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
 
Sort by date Sort by votes
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).

NameHOME-SecAdmin
Source dirE:\Stage\intranet\SecAdmin
Target dirD:\intranet\SecAdmin
EdgeWEBHOMEPROD
NameHOME-SRCB
Source dirE:\Stage\fpse\ITSC-CIO\CSMT\SRCB
Target dirD:\fpse\ITSC-CIO\CSMT\SRCB
EdgeWEBHOMEPROD
NameHOME-WebComponents
Source dirE:\Stage\intranet\GeneralWeb\WebComponents
Target dirD:\intranet\GeneralWeb\WebComponents
EdgeWEBHOMEPROD
NameHOME-AMS
Source dirE:\Stage\intranet\Admin\AMS
Target dirD:\intranet\Admin\AMS
EdgeWEBHOMEPROD
NameIIS Logfiles-P01
Source dirE:\Logfiles\W3SVC1
Target dirD:\save\IIS_Logfiles
EdgeHQABQDTOZWEBP01
NameIIS Logfiles-P02
Source dirE:\Logfiles\W3SVC1
Target dirD:\save\IIS_Logfiles
EdgeHQABQDTOZWEBP02
NameHOME-LRP
Source dirE:\Stage\intranet\Admin\LRP
Target dirD:\intranet\Admin\LRP
EdgeWEBHOMEPROD
NameHOME-Synch_LRP
Source dirD:\intranet\Admin\LRP\docs
Target dirE:\Stage\intranet\Admin\LRP\docs
EdgeWEBHOMEPROD
NameHOME-Synch_MORE_DOCS
Source dirD:\intranet\GeneralWeb\WebApps\MORE\DOCS
Target dirE:\Stage\intranet\GeneralWeb\WebApps\MORE\DOCS
EdgeWEBHOMEPROD
NameHOME-INTRANET
Source dirE:\Stage\intranet
Target dirD:\intranet
EdgeWEBHOMEPROD
NameHOME-CHS
Source dirE:\Stage\intranet\Admin\CHS
Target dirD:\intranet\Admin\CHS
EdgeWEBHOMEPROD
NameHOME-PHXAO
Source dirE:\Stage\intranet\Phoenix
Target dirD:\intranet\phoenix
EdgeWEBHOMEPROD

<colgroup><col span="2"></colgroup><tbody>
</tbody>

<colgroup><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
NameHOME-SecAdmin
Source dirE:\Stage\intranet\SecAdmin
Target dirD:\intranet\SecAdmin
EdgeWEBHOMEPROD
NameHOME-SRCB
Source dirE:\Stage\fpse\ITSC-CIO\CSMT\SRCB
Target dirD:\fpse\ITSC-CIO\CSMT\SRCB
EdgeWEBHOMEPROD
NameHOME-WebComponents
Source dirE:\Stage\intranet\GeneralWeb\WebComponents
Target dirD:\intranet\GeneralWeb\WebComponents
EdgeWEBHOMEPROD
NameHOME-AMS
Source dirE:\Stage\intranet\Admin\AMS
Target dirD:\intranet\Admin\AMS
EdgeWEBHOMEPROD
NameIIS Logfiles-P01
Source dirE:\Logfiles\W3SVC1
Target dirD:\save\IIS_Logfiles
EdgeHQABQDTOZWEBP01
NameIIS Logfiles-P02
Source dirE:\Logfiles\W3SVC1
Target dirD:\save\IIS_Logfiles
EdgeHQABQDTOZWEBP02
NameHOME-LRP
Source dirE:\Stage\intranet\Admin\LRP
Target dirD:\intranet\Admin\LRP
EdgeWEBHOMEPROD
NameHOME-Synch_LRP
Source dirD:\intranet\Admin\LRP\docs
Target dirE:\Stage\intranet\Admin\LRP\docs
EdgeWEBHOMEPROD
NameHOME-Synch_MORE_DOCS
Source dirD:\intranet\GeneralWeb\WebApps\MORE\DOCS
Target dirE:\Stage\intranet\GeneralWeb\WebApps\MORE\DOCS
EdgeWEBHOMEPROD
NameHOME-INTRANET
Source dirE:\Stage\intranet
Target dirD:\intranet
EdgeWEBHOMEPROD
NameHOME-CHS
Source dirE:\Stage\intranet\Admin\CHS
Target dirD:\intranet\Admin\CHS
EdgeWEBHOMEPROD
NameHOME-PHXAO
Source dirE:\Stage\intranet\Phoenix
Target dirD:\intranet\phoenix
EdgeWEBHOMEPROD
NameWWW-FE-EHSC
Source dirE:\Stage\internet\ehsc
Target dirNone
EdgesHQABQDTOZWEBP01, HQABQDTOZWEBP02
NameHOME-Plugins
Source dirE:\Stage\intranet\plugins
Target dirD:\intranet\plugins
EdgeWEBHOMEPROD
NameHOME-DES
Source dirE:\Stage\intranet\OtherPrgms\DES
Target dirD:\intranet\OtherPrgms\DES
EdgeWEBHOMEPROD
NameHOME-FLSA_Training
Source dirE:\Stage\intranet\flsa_training
Target dirD:\intranet\flsa_training
EdgeWEBHOMEPROD
NameHOME-OTPUM
Source dirE:\Stage\intranet\Admin\otpum
Target dirD:\intranet\Admin\otpum
EdgeWEBHOMEPROD
NameHOME-Supervisor101
Source dirE:\Stage\intranet\supervisor101
Target dirD:\intranet\Supervisor101
EdgeWEBHOMEPROD
NameHOME-Bemidji
Source dirE:\Stage\intranet\Facilities\Areas\Bemidji
Target dirD:\intranet\Facilities\Areas\Bemidji
EdgeWEBHOMEPROD
NameHOME-Security
Source dirE:\Stage\intranet\security
Target dirD:\intranet\security
EdgeWEBHOMEPROD
NameHOME-OITComponents
Source dirE:\Stage\intranet\ITSC-Cio\OITComponents
Target dirD:\intranet\ITSC-Cio\OITComponents
EdgeWEBHOMEPROD
NameHOME-OIT_TFS
Source dirE:\Stage\intranet\oittfs
Target dirD:\intranet\oittfs
EdgeWEBHOMEPROD
NameHOME-Telecom
Source dirE:\Stage\intranet\ITSC-Cio\telecom
Target dirD:\intranet\ITSC-Cio\telecom
EdgeWEBHOMEPROD
Name_HOME-INTRANET
Source dirE:\Stage\intranet
Target dirD:\intranet
EdgeWEBHOMEPROD
NameHOME-TPICPSA
Source dirE:\Stage\intranet\OtherPrgms\IHPES\ORAP\TPICPSA
Target dirD:\intranet\OtherPrgms\IHPES\ORAP\TPICPSA
EdgeWEBHOMEPROD
NameHOME-ROBOTS_TXT
Source dirE:\Stage\intranet
Target dirD:\intranet
EdgeWEBHOMEPROD
NameHOME-ABQ
Source dirE:\Stage\intranet\Facilities\Areas\albuquerque
Target dirD:\intranet\Facilities\Areas\albuquerque
EdgeWEBHOMEPROD
NameHOME-IRT
Source dirE:\Stage\intranet\ITSC-Cio\Security\IRT
Target dirD:\intranet\ITSC-Cio\Security\IRT
EdgeWEBHOMEPROD
NameHOME-ITAC
Source dirE:\Stage\intranet\ITAC
Target dirD:\intranet\ITAC
EdgeWEBHOMEPROD
NameHOME-3PBAR
Source dirE:\Stage\intranet\3pbar
Target dirD:\intranet\3pbar
EdgeWEBHOMEPROD
NameWebcident
Source dirE:\Stage\webcident
Target dirD:\webcident
EdgeWEBHOMEPROD
NameHOME-GDM
Source dirE:\Stage\intranet\NDW\GDM
Target dirD:\intranet\NDW\GDM
EdgeWEBHOMEPROD
NameHOME-GeneralWeb
Source dirE:\Stage\intranet\GeneralWeb
Target dirD:\intranet\GeneralWeb
EdgeWEBHOMEPROD
NameHOME-Conference_Room
Source dirE:\Stage\intranet\Admin\ConferenceRoomScheduler
Target dirD:\intranet\Admin\ConferenceRoomScheduler
EdgeWEBHOMEPROD
NameHOME-HSPD12
Source dirE:\Stage\intranet\HSPD12
Target dirD:\intranet\HSPD12
EdgeWEBHOMEPROD

<colgroup><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
75
myall_blues
myall_blues
gheyman
  • Solved
Comparing two columns
Replies
6
Views
161
KRice
K
C
  • Question
Macro that changes cell after each copy and paste
2
Replies
11
Views
429
PeteWright
PeteWright
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
2K
Kevin_Li
Kevin_Li
E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
2K
excelnewb221
E

Forum statistics

Threads
1,215,374
Messages
6,124,571
Members
449,173
Latest member
Kon123

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