Oct 25 2022 01:31 AM
How to find unique value from duplicate value in numbers while in data filter
Oct 25 2022 06:23 AM
Oct 25 2022 08:01 AM
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...
Oct 25 2022 08:10 AM
Oct 25 2022 10:36 PM
Please use the below link to download the workbook, so that you will get the clarity
Oct 25 2022 11:41 PM
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.
Oct 26 2022 02:42 AM
Oct 26 2022 01:35 PM - edited Oct 26 2022 01:46 PM
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!
Oct 27 2022 04:59 AM
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)
Oct 27 2022 05:44 AM - edited Oct 27 2022 05:45 AM
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.)