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