Assumptions:

Sheet1!A1:C5 contains your source data

Sheet2!A2:A3 contains 15/7 and 16/7

Sheet2!B1:E1 contains E, W, R, and R

Formula:

Sheet2!B2, copied across and down:

=INDEX(Sheet1!$A$2:$A$5,SMALL(IF(INDEX(Sheet1!$B$2 :$C$5,0,MATCH($A2,Sheet1!$B$1:$C$1,0))=B$1,ROW(She et1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),COUNTIF($B$1:B$1,B$1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

andrewm Wrote:
Hi

I am trying to change sheet 1 into sheet 2 form

ie. sheet one is


date 15/7 16/7 ...
andrew E R
john R E
mary w R
Jim R w etc

to

date E W R R
15/7 andrew mary john jim
16/7 john jim andrew mary etc

I know what to do with the E and W's (as there are only one ), but
problem
with R's

andrewm


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=387140