Actually not quite Scuba, since you asked for the reverse of this effect. With your lastest Workbook sample it only counts the first occurrence with the exception of Cell E2 which for some reason is showing =G4.
Actually not quite Scuba, since you asked for the reverse of this effect. With your lastest Workbook sample it only counts the first occurrence with the exception of Cell E2 which for some reason is showing =G4.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Soz, I had corrected it but uploaded wrong version, correct version attached nowOriginally Posted by Aussiebear
I did ask before, which version of Excel do you use?
For example, if you use Excel 365 you could use the below (first item gets a 1):
The reverse version would be (last item gets a 1):=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m)),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))
=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m),,,-1),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))
So if arnelgp's vba solution is correct, that sum is 44, which you can also get from
=ROWS(UNIQUE(B2:C106))
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
@p45cal I make you right, but that depends on their Excel version and if they want to see the result row by row or just the overall sum of unique.
Apologies, I use Office 365
I'll try these now, thank you
Examination of the zip version of their workbook implies it can handle UNIQUE:
2024-04-12_123752.jpg
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Don't forget to look at @p45cal's option in post 24 if you are just wanting an overall sum of unique.
Appreciate that but I think the TS should inform us, especially when they have already been asked.
This works perfectly for Office 365 and is exactly what I was trying to achieve.
Thank you everyone who contributed to this, we got there in the end.
Regards
Richard
If you do want an overall sum then you should look at post 24 from @p45cal as it makes more sense, less intensive on the system.
Absolutely!
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Row2:copy down.=--(COUNTIFS($B$2:$B$106,B2,$C$2:$C$106,C2)=COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2))
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
simplifying your original formula:
becomes:=IF(COUNTIFS(B:B, B2,C:C,C2)>1,IF(COUNTIFS(B:B,B2,C:C,C2)>1,IF(COUNTIFS($B$2:B2, B2, $C$2:C2,C2)=1,1,0),""), 1)
=IF(COUNTIFS($B2:$B$106, B2,$C2:$C$106,C2)>1,0,1)
That works even sexier
Sorry to intrude again here but I was testing the results on a table, when I noticed that arnelgp's formula worked but Georgiboy's created a Spill error.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Such array formulae don't work well with tables.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Indeed, I feel that it is a design flaw on Microsofts part.
They push us to use table objects and then don't support their new features with them.
Reply With Quote
