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.
Printable View
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.
Soz, I had corrected it but uploaded wrong version, correct version attached nowQuote:
Originally 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):Code:=LET(m,B2:B106,c,C2:C106,r,XLOOKUP(UNIQUE(m&c),m&c,ROW(m)),IF(ISNUMBER(XMATCH(ROW(m),r)),1,0))
Code:=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 fromQuote:
Originally Posted by Scuba
=ROWS(UNIQUE(B2:C106))
@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 :)Quote:
Originally Posted by georgiboy
I'll try these now, thank you
Examination of the zip version of their workbook implies it can handle UNIQUE:Quote:
Originally Posted by georgiboy
Attachment 31513
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.Quote:
Originally Posted by p45cal
Quote:
Originally Posted by georgiboy
This works perfectly for Office 365 and is exactly what I was trying to achieve. :bow: :clap:
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!Quote:
Originally Posted by georgiboy
Row2:copy down.Code:=--(COUNTIFS($B$2:$B$106,B2,$C$2:$C$106,C2)=COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2))
That's a nice simple option
simplifying your original formula:
becomes:Code:=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)
Code:=IF(COUNTIFS($B2:$B$106, B2,$C2:$C$106,C2)>1,0,1)
That works even sexier :bow::clap:Quote:
Originally Posted by arnelgp
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.
Such array formulae don't work well with tables.
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.