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

Question

peterbartholomew1 · Answer

Patrick2788&nbsp;Basically, I came up with your solution but more 'packaged' and hence more verbose.&nbsp;&nbsp;That was once I had undone my immediate reaction of hitting Ctrl/Shift/L whenever faced with manual filters!&nbsp; That rather missed the point in the present circumstances.&nbsp;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)&nbsp;

patrick2788 · Answer

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.)

patrick2788 · Answer

I believe I know what you're getting at but I'm not certain because of the wording. Do you happen to have a sample workbook you can upload?

kumar_raju · Answer

Patrick2788&nbsp;I am new for this, can you please guid me how to updalod the working file in this portal.&nbsp;Actually I used COUNTA(UNIQUE(A4:A1178)) formula to get single value from cells with duplicate value by which whole column count and after filter show same value for some criteria too.&nbsp;Kindly suggest...

patrick2788 · Answer

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.

Forum Discussion

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

Share

Resources