Help needed to count duplicates but derived by a column of values

HI


I wish I could count how many equal values there are in column I for each group of equal values found in column G and write the results to the related row 2/column. e.g.


In column G there is only one value 1 so in L2 a possible formula would return 0,


In column G there are three 2 values, among these three 2s are equal so in M2 a possible formula would return 2,


In column G there are five 3 values, among these there are no equal values so in N2 a possible formula would return 0,


and so on.


I would appreciate some help in finding one or more formulas to perform this check.


Thank you

Out of interest, what version of Excel do you use?

Hi georgiboy

..right..i forget..

i use Excel 2016 and 2021 (2021 preferred)

thank you for reminding me

Horrible formula in cell L3, copied across:

Code:

---

=LAMBDA(vals,groups,group,LET(b,MAX(BYROW(--(TRANSPOSE(vals)=FILTER(vals,groups=group)),LAMBDA(a,SUM(a)))),IFERROR(IF(b>1,b,0),0)))($I$2:$I$13,$G$2:$G$13,L$1)

---

all because COUNTIF(S) won't work with arrays.
Nicer formula in cell L4:

Code:

---

=MaxEqualValuesCount($I$2:$I$13,$G$2:$G$13,L$1)

---

which is the same formula, but used in a Name. You get a hint of what goes where:

Attachment 30510

and I've assumed if, for example, there are 5 values made up of 2 unique values, 3 of 1 and 2 of the other, you'll want the maximum only (3).
It would be good if someone could come up with a more elegant solution.

Maybe the below formula in cell L2 and drag accross:

Code:

---

=COUNT(FILTER($G$2:$I$13,(COUNTIF($I$2:$I$13,$I$2:$I$13)>1)*($G$2:$G$13=L1)))

---

Good morning p45cal
thanks for your help but on my excel 2021 prof plus i get an error ...

=_xlfn.LAMBDA(_xlpm.vals;_xlpm.groups;_xlpm.group;LET(b;MAX(_xlfn.BYROW(--(MATR.TRASPOSTA(_xlpm.vals)=FILTRO(_xlpm.vals;_xlpm.groups=_xlpm.group));_x lfn.LAMBDA(_xlpm.a;SOMMA(_xlpm.a))));SE.ERRORE(SE(b>1;b;0);0)))

...could it be that LAMDA and LET are only available on office 365 ?

Good morning georgiboy


I modified the formula to match my charset settings and it seems to work

thanks for your help

Quote:

---

Originally Posted by RIC63

e.g. if I put a value equal to I12 in I13 ..in M2 the result is 4 instead of 3 ...does it give you the same result ?

---

I get 3, see attached - is it 4 on there for you?

no as I wrote above now it's ok, I have the same results as you


Thanks again

Quote:

---

Originally Posted by RIC63

but on my excel 2021 prof plus i get an error ...
<snip>
...could it be that LAMDA and LET are only available on office 365 ?

---

I'm not sure, in https://techcommunity.microsoft.com/...a/ba-p/3073293 they say: "We are excited to announce that LAMBDA and LAMBDA helper functions are now generally available to anyone using Production: Current Channel builds of Excel.". I don't know what that means!
Check out:
https://answers.microsoft.com/en-us/...f-f4f290f21a52
https://support.microsoft.com/en-us/...a-ccbf254b8b67
https://answers.microsoft.com/en-us/...a-68a54850c77f
Maybe you need to check for updates?

Anyway, it won't matter because there's a much better solution by georgiboy in msg#5!

can you use Recordset?
see this demo.

Hi p45cal


I read about the availability of the new functions and I checked on my W10 pro if there were updates available for both the operating system and for office and it is up to date...maybe in the next few hours -as I read it has already happened to others- the new functions become functional


Thanks again for your help and information sharing

Hi arnelgp


thanks to you too for the proposed solution, the solution with the simple formula appears to be the most suitable but I will still do some tests to see which is more performing for the amount of data on which I have to make it work


Thanks again