I want to count the occurrences of UNIQUE values within a GROUP - I know how to find and count the unique values, but I don't know HOW to tell Excel what a GROUP consists of, i.e. where one ends and a new one starts.
I have created a representative example for my problem, see attachment.
Last edited by MinisoftEggshell; 01-28-2022 at 04:56 AM.
Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
I uploaded the attachment, it should work now.
Last edited by AliGW; 01-31-2022 at 04:19 AM. Reason: PLEASE don't quote unnecessarily!
i2=UNIQUE(FILTER(C2:C100,C2:C100<>""))
j1=TRANSPOSE(UNIQUE(FILTER(B2:B100,B2:B100<>"")))
J2=IF($I2<>"",COUNT(UNIQUE(FILTER($C$2:$C$100,($C$2:$C$100=$I2)*($B$2:$B$100=J$1)))),"")
Copy across and down
Last edited by CARACALLA; 01-28-2022 at 05:57 AM.
In every cell it says 1, but the value 10 occours three times in the Applejuice group? And two times within the Oranges group. Why is every occourence 1?
Misunderstood, delete it
Last edited by wk9128; 01-28-2022 at 06:02 AM.
j2=IF($I2<>"",COUNTIFS($C$2:$C$100,$I2,$B$2:$B$100,J$1),"")
copy across and down
This is not the best way, for sure...
=TRANSPOSE(UNIQUE(B2:B23))
and
=LET(a,SORT(UNIQUE($C$2:$C$23)),CHOOSE({1,2,3,4},a,COUNTIFS(C2:C23,a,B2:B23,M1),COUNTIFS(C2:C23,a,B2:B23,N1),COUNTIFS(C2:C23,a,B2:B23,O1)))
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
Thank you very much, that helped a great deal!
Who were you talking to??
Generally I was talking to both of you because I do appreciate your help. Before you posted your solution I already altered CARACALLA's formula for my actual sheet. Which solution is more efficient, that's something I cannot decide because I have very limited knowledge in Excel. The sheet this formula is intended for has 3000 rows and 400 groups and 150 IDsOriginally Posted by Glenn Kennedy
Caracalla's will be better for the scale of your problem. I know that there will be a great way to do it, using the approach that I took... but it is not springing to mind!! If inspiration strikes, I'll repost on this thread.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Please try
=LET(z,B1:C23,g,INDEX(z,,1),id,INDEX(z,,2),uid,UNIQUE(id),ug,TRANSPOSE(UNIQUE(g)),f,COUNTIFS(g,ug,id,uid),IF(SEQUENCE(,COUNTA(ug),0),IF(SEQUENCE(ROWS(uid),,0),f,ug),SUBSTITUTE(uid,"ID","ID/Group")))
or
=LET(z,B1:C23,g,INDEX(z,,1),id,INDEX(z,,2),uid,TRANSPOSE(UNIQUE(id)),ug,UNIQUE(g),f,COUNTIFS(g,ug,id,uid),IF(SEQUENCE(COUNTA(ug),,0),IF(SEQUENCE(,COUNTA(uid),0),f,ug),SUBSTITUTE(uid,"ID","Group/ID")))
wow that's a complex formula but it works nicely! @Bo_Ry, in the example sheet I provided are the two columns Group and ID next to one another. In my real sheet these two columns are B and I - I was trying to figure out whether or not it's possible to use that formula but leaving the column structure intact?
OK never mind I figured it out myself I expanded the range in z and edited the indexes in g and id accordingly.
It really fascinates me, this formula
Last edited by MinisoftEggshell; 01-31-2022 at 10:18 AM. Reason: figured it out myself
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks