Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Offset and Match
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 3 A001 115 12-Jan-2004 B001 152 19-Feb-2004 28-Mar-2004 A001 115 19-Feb-2004 22-Mar-2004 C001 115 21-Mar-2004 31-May-2004 B001 115 11-Apr-2004 C001 115 19-Jun-2004 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 3 A001 115 12-Jan-2004 19-Feb-2004 22-Mar-2004 B001 152 19-Feb-2004 28-Mar-2004 11-Apr-2004 C001 115 21-Mar-2004 31-May-2004 19-Jun-2004 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 Scurrah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offset match | Excel Discussion (Misc queries) | |||
OFFSET & MATCH | Excel Discussion (Misc queries) | |||
IF, OFFSET,MATCH Please Help | Excel Discussion (Misc queries) | |||
Using Match & Offset | Excel Worksheet Functions | |||
Using MAX with OFFSET and MATCH | Excel Worksheet Functions |