ExcelBanter - Using Offset and Match

Andrew

Will a given Pt (Patient?) always visit the same site? In your first table, 'B001' visits site 152 the first two times and site 115 the third time, but in your summary table the visits are all recorded against site 152. If it can be assumed that each Pt always uses the same site, and if there will only ever be one entry in each 'Visit' column for each Pt (which is the case in your table),you can use SUMIF, e.g.

=SUMIF($A$A,"A001",C:C) for Pt A001, Visit 1, assuming your table starts in cell A1
=SUMIF($A$A,"A001",D:D) for Pt A001, Visit 2, etc

The output won't be formatted as a date by default, so you'll have to change it

To handle 'empty' Visits that haven't taken place, you could change this to

=IF(SUMIF($A$A,"A001",C:C)=0,"",SUMIF($A$A,"A001", C:C)

If your summary table needs separate rows for 'Pt B001 Site 152' and 'Pt B001 Site 115' you could concatenate together the 'Pt' and 'Site' in both tables to give a unique value to search on

Hope this helps

JB

----- Andrew Scurrah wrote: ----

I wonder if some one can help me..

I have below a spreadsheet that tracks certain data
items..

Pt Site Visit 1 Visit 2 Visit
A001 115 12-Jan-2004
B001 152 19-Feb-2004 28-Mar-200
A001 115 19-Feb-2004 22-Mar-200
C001 115 21-Mar-2004 31-May-200
B001 115 11-Apr-200
C001 115 19-Jun-200

What I would like to do is combine the rows with
identical "Pt" and "Site" values into ONE row in a second
sheet...as follows..

Pt Site Visit 1 Visit 2 Visit
A001 115 12-Jan-2004 19-Feb-2004 22-Mar-200
B001 152 19-Feb-2004 28-Mar-2004 11-Apr-200
C001 115 21-Mar-2004 31-May-2004 19-Jun-200

I've tried to use OFFSET and MATCH, but I think my
arguments are incorrect...they can't find the data in the
first sheet correctly..

Can anyone help me ??

Thank you in advance...In desparate need to make this
work..

Sincerely

Andrew Scurra