I want to determine the number of unique values in a matrix.
A similar question is posted on https://www.helpmij.nl/forum/showthr...unieke-waarden
but we only found difficult or slow solutions:Are there for 365 users also better or simpler solutions (without VBA)?
Please Login or Register to view this content.
Last edited by HansDouwe; 08-09-2022 at 12:48 AM.
Power Query"Please Login or Register to view this content.
Last edited by Czeslaw; 08-09-2022 at 04:04 AM.
Thx for your quick response with a new solution in Powerquery.
I'm not familiar with Powerquery yet.
How do I run this query?
I only see "connection only" on Query Table1 in the added file and cannot refresh it.
hello,
is that count case sensitive (hoofdlettergevoelig) otherwise your first formula is a problem
i prefer a dictionary
PS. this function is very quick !Please Login or Register to view this content.
Alias : Cow18
Last edited by bsalv; 08-09-2022 at 01:17 AM.
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Is this simple enugh for you?
=COUNTA(UNIQUE(FILTERXML("<z><m>"&TEXTJOIN("</m><m>",,A1:L12)&"</m></z>","//m")))
I doubt you'll get anything shorter.
Last edited by AliGW; 08-09-2022 at 01:58 AM. Reason: Added example workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
No the count should not be case sensitive, Indeed the first formula is case sensitive.Originally Posted by bsalv
Was post #5 of any use? Maybe you didn't see it ...
Yes, it is useful to add tags directly in the TEXTJOIN.
Only the FILTERXML I find user-unfriendly, because you have to add tags, of which I can never remember the exact syntax.
But so be them.
Thanks for this solution.
Power Query, loaded. Refresh the Query with right click --> refresh
Thx JEC, this also works well.
Dutch-speaking get-together
I'm not Dutch, though, and nor is Czeslaw.
I don't see any text in this tread. Maybe they will come back after a new message is posted. Hence this try.
Edit: Yes trick worked. The text in this tread is back.
Last edited by HansDouwe; 08-23-2022 at 06:42 AM.
@AliGW, I bet you already know, but there is a problem with the search functionality. Once you search for topics, all answers are invisible.
If you have signed up for office insider:
=LET(r,TOCOL(A1:L12),ROWS(UNIQUE(FILTER(r,r<>""))))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Same fix as above - refresh the page and see if the search results appear.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks