Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Compare multiple cells (https://www.askmehelpdesk.com/showthread.php?t=371620)

  • Jul 3, 2009, 10:03 AM
    tejutej
    compare multiple cells
    Dear Supporting Team,

    I am reconciling the spreadsheet and need to find out the mismatch between column C(X-VISITDT) and column F (Y-VISITDT), where the subject ID(Column A & D) and CPE Event (column B&E) are same.

    Could you please provide me the formula, which gives the mismatch between visit dates, if Subject and CPEVENT are same. Please find the below table foe example.


    Subject CPEVENT X-VISITDT Subject CPEVENT Y-VISITDT
    283001001 W5D29 15 NOV 2007 283001001 W1D1 18 OCT 2007
    283001001 W1D1 18 OCT 2007 283001001 W5D29 15 NOV 2007
    283001002 W1D1 10 OCT 2007 283001002 W1D1 10 OCT 2007
    283001002 W5D29 07 NOV 2007 283001002 W5D29 07 NOV 2007
    283001002 W9D57 06 DEC 2007 283001002 W9D57 06 DEC 2007
    283001002 W17 31 JAN 2008 283001003 W1D1 11 APR 2008
    283001003 W1D1 11 APR 2008 283001004 W1D1 08 OCT 2008
    283001004 W1D1 08 OCT 2008 283001005 W1D1 14 JAN 2009
    283001005 W1D1 14 JAN 2009 283001005 W5D29 11 FEB 2009
    283001005 W5D29 11 FEB 2009 283001005 W9D57 11 MAR 2009
    283001005 EOT 06 MAY 2009 283002001 W1D1 03 SEP 2008



    Kind regards,
    Tejaswi
  • Jul 3, 2009, 03:22 PM
    JBeaucaire
    Code:
    Subject          CPEVENT          X-VISITDT        Subject          CPEVENT          Y-VISITDT
    283001001        W5D29        15 NOV 2007        283001001        W1D1        18 OCT 2007
    283001001        W1D1        18 OCT 2007        283001001        W5D29        15 NOV 2007
    283001002        W1D1        10 OCT 2007        283001002        W1D1        10 OCT 2007
    283001002        W5D29        07 NOV 2007        283001002        W5D29        07 NOV 2007
    283001002        W9D57        06 DEC 2007        283001002        W9D57        06 DEC 2007
    283001002        W17        31 JAN 2008        283001003        W1D1        11 APR 2008
    283001003        W1D1        11 APR 2008        283001004        W1D1        08 OCT 2008
    283001004        W1D1        08 OCT 2008        283001005        W1D1        14 JAN 2009
    283001005        W1D1        14 JAN 2009        283001005        W5D29        11 FEB 2009
    283001005        W5D29        11 FEB 2009        283001005        W9D57        11 MAR 2009
    283001005        EOT        06 MAY 2009        283002001        W1D1        03 SEP 2008
    Put this formula in G2 and copy down. If the events are the same, you will get a MATCH/MISMATCH on the values in C & F, if the events are not the same, the cell will be blank.

    =IF(A2&B2=D2&E2,IF(C2=F2,"Match", "MisMatch"),"")

    • All times are GMT -7. The time now is 12:19 AM.