Forum Discussion

cheezy's avatar
cheezy
Copper Contributor
Jun 08, 2023
Solved

formula to finding matching/non matching values based on values in two columns

Hi    trying to find a formula that could achieve the following as column "required output" Trying to identify tasks that have the same ID but different times recorded      Time       TaskID   ...
excel
  • Harun24HR's avatar
    Harun24HR
    Jun 08, 2023

    cheezy COUNTIFS() may work.

    =IF(COUNTIFS($B$2:$B$6,B2)=1,"No",IF(COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)=1,"Yes","No"))

     

     

Harun24HR's avatar
Harun24HR
Bronze Contributor
Jun 08, 2023

cheezy COUNTIFS() may work.

=IF(COUNTIFS($B$2:$B$6,B2)=1,"No",IF(COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)=1,"Yes","No"))

 

 

  • cheezy's avatar
    cheezy
    Copper Contributor
    Jun 09, 2023

    Harun24HR 

     

    that works thanks.

     

    How about another scenario: how can i achieve below? the required output counts the number of tasks per task id but if the time is the same (like taskID 3 having two task @ 01:40 ) then it ignores 

    and only counts as 1?

     

    Time      TaskID      required output
    00:21              1         1
    00:50               2        1 
    01:30               2        2
    01:40               3        1
    01:40               3        1
    02:50               4        1
    02:29               4        2
    03:29               4         3
    03:40               4         4

Resources