Forum Discussion

MichielS340's avatar
MichielS340
Copper Contributor
Jul 25, 2024

Excel combine lambda and countif in formula

Hi, 

 

I have the following formula to get a range of values (col) of which i want to check how many times the values appear in the range and then filter for more than one time (duplicates). Somehow I am not able to get the formula working pr

 

=LET(tbl;UNIQUE(VSTACK(HSTACK(tbl_Prognose[PRJ_NR];tbl_Prognose[DESC]);HSTACK(tbl_Orderportefeuille[Nummer];tbl_Orderportefeuille[Omschrijving])));
     col;CHOOSECOLS(tbl;1);
     BYROW(col;LAMBDA(r;COUNTIF(col;r))))

 

operly. Someone any advice on this 😅

Many thanks in advance

excel
office 365
  • Martin_Angosto's avatar
    Martin_Angosto
    Iron Contributor
    Jul 25, 2024

    MichielS340 

     

    Hi, I believe it would be easier to help you if you could share a sample file (without sensitive data) of your problem. Also, can you develop further what is the expected output? Do you want to retrieve an array with only values that appear more than once in a set range?

     

    If you could share a screenshot or a sample file together with a desired output I am sure it would be an easy task!

    • MichielS115's avatar
      MichielS115
      Copper Contributor
      Jul 25, 2024

      Martin_Angosto sorry thats more helpful. See attached. Basically what I want to check is if there are numbers with different names spelled. So for example in the example I want to have a list containing 10193460 | a

      10193460 | aa

       

      and the other projects should not appear because the numbers were spelled in the same way.

      I was thinking of preparing a unique table and then counting the number of times the projects would appear because when the names are not spelled the same you would have multiple times the specific project. But perhaps there are other more logic ways to achieve this.

       

       

  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 25, 2024

    MichielS340 COUNTIF does not work with an array object in the range argument. You could try replacing COUNTIF(col;r) with SUM(N(col=r))>1. For example:

     

    =LET(
    tbl; UNIQUE(VSTACK(HSTACK(tbl_Prognose[PRJ_NR];tbl_Prognose[DESC]);HSTACK(tbl_Orderportefeuille[Nummer];tbl_Orderportefeuille[Omschrijving])));
    col; CHOOSECOLS(tbl;1);
    FILTER(tbl;MAP(col;LAMBDA(r;SUM(N(col=r))>1));"")
)

     

    An alternative approach without BYROW or MAP could be:

     

    =LET(
    tbl; UNIQUE(VSTACK(HSTACK(tbl_Prognose[PRJ_NR];tbl_Prognose[DESC]);HSTACK(tbl_Orderportefeuille[Nummer];tbl_Orderportefeuille[Omschrijving])));
    col; CHOOSECOLS(tbl;1);
    only_once; UNIQUE(col;;1);
    FILTER(tbl;ISERROR(XMATCH(col;only_once));"")
)

     

    See attached...

Resources