Distribute Columns over Rows

**ziohausam** · 01-31-2009, 08:39 AM

Hello All,

I have an excel table that i need to change its view to be able to import in access.
The table is like this:

Name---p1----p2----p3
n1------2-----3-----4
n2------7-----4-----9
n3------6-----2-----7

I want to distribute all columns over row to be like this:

Name----price----Location
n1-------2-------p1
n1-------3-------p2
n1-------4-------p3
n2-------7-------p1
n2-------4-------p2
n2-------9-------p3
n3-------6-------p1
n3-------2-------p2
n3-------7-------p3

Of course the table is large. i tried to do this using past special to transpose the rows to columns, it took an hour from me to concert the table. is there a way or a macro or anything to do this?

Regards,
Hosam

**Bjornar** · 01-31-2009, 09:03 AM

Here is one trick to do this:

Select the menu: Data -> Pivot table and PivotChart Report
Select: Multiple Consolidation ranges
Click next
Select: I will create the page fields
Click next
Select the your range of data to transpose from and click add to add the range. Leave the number of page fields at 0.
then click next and the click finish

You now have a new sheet with a pivot table that looks like your source range

The trick is now to double click on the cell in the bottom right corner of the pivot table. With your example this cell displays 44.

You the get a new worksheet with the data formatted the way you wanted it to import in Access.

**ziohausam** · 01-31-2009, 09:10 AM

Originally Posted by Bjornar

Here is one trick to do this:

Select the menu: Data -> Pivot table and PivotChart Report
Select: Multiple Consolidation ranges
Click next
Select: I will create the page fields
Click next
Select the your range of data to transpose from and click add to add the range. Leave the number of page fields at 0.
then click next and the click finish

You now have a new sheet with a pivot table that looks like your source range

The trick is now to double click on the cell in the bottom right corner of the pivot table. With your example this cell displays 44.

You the get a new worksheet with the data formatted the way you wanted it to import in Access.

That's Amazing. it worked. Thank you very much