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

 

[Rick Rothstein]

For the layout you showed... put the headers in cells B1:F1, then put this formula in cell B2 and copy it across to F2, then copy B2:F2 down...

=IFERROR(INDEX($B$1:$B$8,4*ROWS($2:2)-3+COLUMNS($B:B)-1),"")

 

[stevec883]

Thanks Rick --- I'm pretty new to excel, so having trouble implementing. I have data in B column, so wouldn't I start my headers in "C"? This is what I'm trying to achieve --- The original "A" column repeats, so that will be my header in result.

[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]C:\log1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\Res1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBServ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]C:\Tom's file[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\Different[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]Hostname[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 300, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Source dir[/TD]
[TD]Target dir[/TD]
[TD]Edge[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]C:\log1[/TD]
[TD]D:\Res1[/TD]
[TD]WEBServ[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]C:\Tom's File[/TD]
[TD]D:\Different[/TD]
[TD]Hostname[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 74.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD]C:\log1[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Rick Rothstein]

Thanks Rick --- I'm pretty new to excel, so having trouble implementing. I have data in B column, so wouldn't I start my headers in "C"? This is what I'm trying to achieve --- The original "A" column repeats, so that will be my header in result.

[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]C:\log1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\Res1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]WEBServ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Source dir[/TD]
[TD]C:\Tom's file[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target dir[/TD]
[TD]D:\Different[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Edge[/TD]
[TD]Hostname[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 300, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Source dir[/TD]
[TD]Target dir[/TD]
[TD]Edge[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]C:\log1[/TD]
[TD]D:\Res1[/TD]
[TD]WEBServ[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]C:\Tom's File[/TD]
[TD]D:\Different[/TD]
[TD]Hostname[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 74.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD]C:\log1[/TD]
[/TR]
</tbody>[/TABLE]

I wrote B1 and B2, but they should have been C1 and C2... sorry. However, my formula won't work if what I highlighted in red above is actually in your data (you did not show intermediate headers in your original message. So, before continuing... do you in fact have intermediate headers between each person's data (as shown)?

 

[stevec883]

I wrote B1 and B2, but they should have been C1 and C2... sorry. However, my formula won't work if what I highlighted in red above is actually in your data (you did not show intermediate headers in your original message. So, before continuing... do you in fact have intermediate headers between each person's data (as shown)?

No worries Rick! --- there are no headers. Column A is just 4 text data cells repeating. Basically, just trying to Transpose 4 rows at a time all the way down.

 

[Rick Rothstein]

No worries Rick! --- there are no headers. Column A is just 4 text data cells repeating. Basically, just trying to Transpose 4 rows at a time all the way down.

Then my (corrected) formula should work for you. Were you able to correct for the typos in my originally posted formula or are you still having a problem?

 

[stevec883]

Sorry, I'm pretty new to excel. Do I actually change the formula or the placement of the formula to C1?

 

[Rick Rothstein]

Sorry, I'm pretty new to excel. Do I actually change the formula or the placement of the formula to C1?

Manually put the four vertical headers (range A1:A4) into cells C1:F1, then put this formula in cell C2...

=IFERROR(INDEX($B$1:$B$8,4*ROWS($2:2)-3+COLUMNS($B:B)-1),"")

and copy it across to cell F2, then select cells C2:F2 and copy it down.

 

[stevec883]

It's not working, but it's probably me and not the formula. I call the top row a header, but I just typed text in and froze the row (so maybe not a true "header"). Here is a screenshot, my original columns are A and B, and the desired output is C, D, E, and F. Also, it is only the output being transposed to rows that I am concerned with (ie: I can add headers in later and fancy it up). Here is a screenshot: https://ibb.co/jjSP6c

 

[Rick Rothstein]

It's not working, but it's probably me and not the formula. I call the top row a header, but I just typed text in and froze the row (so maybe not a true "header"). Here is a screenshot, my original columns are A and B, and the desired output is C, D, E, and F. Also, it is only the output being transposed to rows that I am concerned with (ie: I can add headers in later and fancy it up). Here is a screenshot: https://ibb.co/jjSP6c

That setup is what I assumed when I developed my formula originally. Screenshots (images) are not very useful as we cannot copy/paste the data from them in our Excel sheets in order to work with them. Since your data is so small, why don't you just copy/paste the data from Columns A and B into a reply here and I will create a workbook with my formula in place (that I expect will work) and post it at DropBox so you will be able to download it and see whether is works for you also.