pivot table in reverse

[Wim]

I' ve got a spreadsheet with events as row headings, people's names as
column headings and dates in the cells. E.g.
Person 1 Person 2
Birthday 01/07/61 05/10/67
Anniversary 10/08/90 10/11/92

(Actually the list is much longer....)

Now I want to make a new list list converting the above data to [dates
(in column 1; event in column 2; person in column 3]. Sort of pivot
tables in reverse......!!

How to?

Wim

[broro183]

Hi,

I may not be reading your post correctly but I think searching for "Transpose" in the Help files may point you in the right direction.
Also, with the flexibility of pivot tables it is possible that these may be able to do what you want as well.

If I have the layout correct, a straight out formulae approach could use a new sheet with:
cell A1 =OFFSET('Look up Sheet'!A$3,0,ROW(A1)-1,1,1)
cell B1 =OFFSET('Look up Sheet'!A$2,0,ROW(A1)-1,1,1)
cell C1 =OFFSET('Look up Sheet'!A$1,0,ROW(A1)-1,1,1)
and these cells copied down for as many rows as needed.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[Debra Dalgleish]

To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm


Wim wrote:
> I' ve got a spreadsheet with events as row headings, people's names as
> column headings and dates in the cells. E.g.
> Person 1 Person 2
> Birthday 01/07/61 05/10/67
> Anniversary 10/08/90 10/11/92
>
> (Actually the list is much longer....)
>
> Now I want to make a new list list converting the above data to [dates
> (in column 1; event in column 2; person in column 3]. Sort of pivot
> tables in reverse......!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Wim]

Thanks a million for the greatr suggestions. Esp. the pivot table
suggestion from Debra is great.
Wim

[broro183]

Thanks for the feedback, & thanks also to Debra - that link's now in my "may come in handy" list.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...