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

[Kumar_Raju]

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

[#2]

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?

[#3]

@Patrick2788 

I am new for this, can you please guid me how to updalod the working file in this portal.

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.

Kindly suggest...

[#4]

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.

[#5]

@Patrick2788 

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

https://we.tl/t-moOYzA3ngg

[#6]

@Kumar_Raju 

Your formula

= COUNTA(UNIQUE(list))

is perfectly valid, as is

= ROWS(UNIQUE(list))

The latter focusses upon the dimensions of the resulting array whereas the former looks more at the content.  If blank fields are present within the list

= ROWS( UNIQUE( FILTER(list, list<>"") ) )

could be useful.

[#7]

Thank you
but it not useful in this scenario

[#8]

@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 @Peter Bartholomew because the original request was missing some details and I'd like to see what he comes up with for this one!

[#9]

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

[#10]

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