Forum Discussion

Kumar_Raju's avatar
Kumar_Raju
Copper Contributor
Oct 25, 2022

How to find unique value from duplicate value in numbers while in data filter

How to find unique value from duplicate value in numbers while in data filter
excel
Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 25, 2022
You may not have the option to upload a workbook because your account is new. You could host it on OneDrive or another sharing host site.
Kumar_Raju's avatar
Kumar_Raju
Copper Contributor
Oct 26, 2022

Patrick2788 

Please use the below link to download the workbook, so that you will get the clarity

https://we.tl/t-moOYzA3ngg

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 26, 2022

    Kumar_Raju 

    Thank you for sharing the workbook.  Essentially, you need a conditional SUBTOTAL. I have a method for obtaining this with MAP.

     

    Here are your formulas for those 3 columns Left to Right:

    Product:

     

    =LET(c,A4:A1178,m,MAP(c,LAMBDA(e,IF(SUBTOTAL(3,e)=1,e,""))),COUNTA(UNIQUE(m))-1)

     

     

    Material No:

     

    =LET(c,B4:B1178,m,MAP(c,LAMBDA(e,IF(SUBTOTAL(3,e)=1,e,""))),COUNTA(UNIQUE(m))-1)

     

    Material Group:

     

    =LET(c,C4:C1178,m,MAP(c,LAMBDA(e,IF(SUBTOTAL(3,e)=1,e,""))),COUNTA(UNIQUE(m))-1)

     

     

    Including PeterBartholomew1 because the original request was missing some details and I'd like to see what he comes up with for this one!

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 27, 2022

      Patrick2788 

      Basically, I came up with your solution but more 'packaged' and hence more verbose.  That was once I had undone my immediate reaction of hitting Ctrl/Shift/L whenever faced with manual filters!  That rather missed the point in the present circumstances.

       

      I presented the entire formula as a Lambda function but, within that, I placed the SUBTOTAL trick within a further Lambda 'IsVisbleλ' to expose the intent but conceal the means.

      IsVisbleλ 
=  LAMBDA(list, 
      MAP(list, 
        LAMBDA(item, SUBTOTAL(3, item))
      )
   );

CountVisibleλ
=  LAMBDA(list,
      LET(
         filteredItems, FILTER(list, IsVisbleλ(list)),
         COUNTA(UNIQUE(filteredItems))
      )
   );

      With those in place, my worksheet formulae were

      = CountVisibleλ(product)

= CountVisibleλ(materialNum)

= CountVisibleλ(materialGroup)

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Oct 27, 2022

        That's not verbose at all. It's elegant and easy to read. It's interesting how this type of request has come up several times in the past 6 months.

        Another one I had worked on was summing while ignoring hidden columns. CELL("width") makes it theoretically possible, but the formula will not recalculate automatically when columns are hidden or shown (Then again, there are better approaches to concealing data than hiding columns, in my opinion.)

Resources