Hi, I've a dataset that looks like this(demo only, I've more columns in it) TitleMrMrsNameJohnAliceSurnameAdamsAdamsAddress3z4zPhone123456 What I want to do is to pivot the data in a way that my new headers are the values from first column and the rest follows the suit. Exactly how it looks below. TitleNameSurnameAddressPhoneMrJohnAdams3z123MrsAliceAdams4z456 I tried using Cross Tab to do this but it works partially as it only allows me to select one column for values hence I lose all the info in the third column. Is there a way to do this, bearing in mind that the dataset is much larger and there are more columns. Thanks

Pivoting the data

Hi,

I've a dataset that looks like this(demo only, I've more columns in it)

Title	Mr	Mrs
Name	John	Alice
Surname	Adams	Adams
Address	3z	4z
Phone	123	456

What I want to do is to pivot the data in a way that my new headers are the values from first column and the rest follows the suit. Exactly how it looks below.

Title	Name	Surname	Address	Phone
Mr	John	Adams	3z	123
Mrs	Alice	Adams	4z	456

I tried using Cross Tab to do this but it works partially as it only allows me to select one column for values hence I lose all the info in the third column.

Is there a way to do this, bearing in mind that the dataset is much larger and there are more columns.

Thanks

Preparation

Accepted answers

JohnJPS

This question is actually fairly common so I went ahead and created a macro that "totally transposes" the data, all rows to columns and vice versa; with the option to do that shift into column headers. Hopefully you or anyone else might find it handy. (Attached).

EDIT: altered to use user-specified column name for the input column to transpose into column headers.

TotallyTranspose.yxmc

All comments

JohnJPS

Hi Pav,

Try a Transpose first, Key Field your first column, all other fields as Data Fields. Then do the Cross Tab, again with your first column as the only Group Data field, and then "Name" as New Column header and "Value" as "Values.

Hope that helps!

Pav

Hi John,

Unfortunately this didn't work for me, it produces the exact same output as input i.e. the table looks exactly the same prior to using the transponse and cross tab tools.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703