# Transpose Vertical Data Horizontally

• Is it possible to transpose data entries, currently organised in a single column, into field entries at the top of a spreadsheet and for all the corresponding data values in the adjacent column to align accordingly below.

For clairty, the image below visually represents the problem.

Thanks

Ben

• Re: Transposing Vertical Data Entries Into Horizontal Field Entries

Ben

As well as visually showing us the problem, could you attach a worksheet with actual data?.:)

Boo!:yikes:

• Re: Transpose Vertical Data Horizontally

hi socktrot1984 (Ben)

These are the types of things you would think that Microsoft would get right - the code is reasonable simpe

make sure you place the cursor on the first entry

HTH

GB

But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

• Re: Transpose Vertical Data Horizontally

Hi,

In G1 and across,

=INDEX(\$E\$2:\$E\$8,COLUMNS(\$G\$1:G\$1))

In G2 and copied down and across,

=IF(MOD(ROWS(\$A\$2:\$A2)-ROW(\$A\$2)+1,7)=0,INDEX(\$F2:\$F\$22,MATCH(G\$1,\$E2:\$E\$22,0)),"")

HTH

• Re: Transpose Vertical Data Horizontally

Hi

Thanks a lot for the help.

As requested I have attached a sample of the actual data.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Thanks for this, it works in principle on the spreadsheet, however i have encountered a few more problems. In some cases each job number has a variable number of cost types, so over time the data entries that are now aligned with the vertical fields are out of sync with the job numbers. Is there anyway of rectifying the problem, perhaps by including a function in the formula that accounts for the number of cost types each job number has?

I have included an updated sample of the spreadsheet to illustrate the point.

## Files

• Re: Transpose Vertical Data Horizontally

Ben,

Thanks for your email, I needed to attach the file attached and could not see how that was done directly...

Please look at the attachment, if you need further info, don't hesitate to email again.

Cheers,

Graham

## Files

But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

• Re: Transpose Vertical Data Horizontally

Hi,

In E2 and copied down & across,

=IF(COUNTIF(\$A\$2:\$A2,\$A2)=1,INDEX(\$D2:\$D\$9948,MATCH(E\$1,\$C2:\$C\$9948,0)),"")

HTH

• Re: Transpose Vertical Data Horizontally

Thats great, the code does exactly what I need!

In addition I would like to be able move the rows with numerical values into a new tab without having to manually transfer them one by one. Is there anything I can do?

The image below may help to visually represent what i mean. I would like only the rows in orange to be copied accross into a new tab.

• Re: Transpose Vertical Data Horizontally

Hi,

Do a total on the last column of each row and custom filter the column 'Does not equal to 0' and copy paste the data into a new tab.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!