Forum Discussion

pcarle1's avatar
pcarle1
Copper Contributor
Jun 19, 2023

Alternative to Countif due to closed workbooks

Hello all, I am looking to see what alternative I can use to countif for the formula below. Essentially what I am doing is checking another workbook to see if an employee id # exists there and if it...
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 20, 2023

    Hi pcarle1 

     

    An alternative could be MATCH:

    =IF(ISNUMBER(MATCH([@EmployeeID], 'filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A, 0)), "y", "")

     

    Not quite sure I understand what the first portion of your complete formula is supposed to do:

    =IF(OR(ISBLANK($L16)),"",...

    as the following should be enough:

    =IF(ISBLANK($L16),"",...

     

pcarle1's avatar
pcarle1
Copper Contributor
Hello and thank you for the response. I totally forgot about utilizing the match function for this. So far I have not gotten it to populate a "Y" when I know it should, but I have been so swamped today I have not had the chance to really dive in it. If I have more issues with it, I will respond with what I learn.

In response to the first portion, this was created as a way to return the value "" if no value was found in cell $L16. I had been accustomed to putting IF(OR(ISBLANK for a while as this was something I learned not realizing the OR( is not needed,
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 20, 2023

pcarle1 

So far I have not gotten it to populate a "Y" when I know it should

Assuming you're right data quality is probably the source of the issue (GIGO)...

  • pcarle1's avatar
    pcarle1
    Copper Contributor
    Jun 21, 2023

    Lorenzo I think I found the issue, the data between the two sources seems to be formatted differently. For example, in one spreadsheet the ID # can be 6, but in the other sheet the same employee would be 00006 as our ID numbers are all listed as a 5 digit number from 00001-99999. I think my new problem is that both of these two sources are unable to be adjusted by me as they come out of data lakes. Is there any way to have the MATCH function still work for these? 

    source 1 - source 2:

    00006 = 6

    00354 = 354

    01000 = 1000

    12345 = 12345

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 21, 2023

      pcarle1 

       

      If Source1 (your Master as I understand) is an Excel file where the Employee ID are always 5 digits with leading zeros it's highly probable those IDs are stored a Text values in which case the following does it here:

      =IF(ISNUMBER(MATCH(TEXT([@EmployeeID],"00000"), 'PathToYour[Master.xlsx]Sheet1'!$A:$A, 0)), "y", "")
      • pcarle1's avatar
        pcarle1
        Copper Contributor
        Jun 21, 2023
        This fixed it! Thank you so much for the assistance on this one. I am marking the topic as resolved.

Resources