can I do this with a Pivot Table??

[JMR]

I have a table where the columns are different shifts and the rows are
names of people that are available to work that particular shift.

eg:

1/1 7-3 2/3 3-11 3/15 3-11
Joe Joe
Mary Mary
Dave Dave



I'd like to be able to reformat this table so that it's organized by
person rather than shift, so I'd have


Joe Mary
1/1 7-3 1/1 7-3
2/3 3-11 3/15 3-11


I've been playing around with the PivotTable Wizard but it doesn't seem
like it will do what I want--is this possible with a PivotTable? If not,
is ther another way to do this?

thanks--

 

[Frank Kabel]

Hi
try the following on your second sheet. Assumption: You have entered
row 1 (the row with the names) manually.
Put the following formula in A2 (enter this as array formula with
CTRL+SHIFT+ENTER)):
=INDEX('sheet1'!$A$1:$X$1,1,SMALL(IF(OFFSET('sheet1'!$A$2:$X$2,ROW()-2,
0)=A$1,COLUMN('sheet1'!$A$1:$X$1)),COLUMN()))
and copy this formula