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

[RIC63]

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

[georgiboy]

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

[RIC63]

Hi georgiboy

..right..i forget..

i use Excel 2016 and 2021 (2021 preferred)

thank you for reminding me

[p45cal]

Horrible formula in cell L3, copied across:

=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:

=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:

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.

[georgiboy]

Maybe the below formula in cell L2 and drag accross:

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

[RIC63]

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 ?

[RIC63]

Good morning georgiboy


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

thanks for your help

[georgiboy]

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?

[RIC63]

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


Thanks again

[p45cal]

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/t5/excel-blog/announcing-lambdas-to-production-and-advanced-formula/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/msoffice/forum/all/will-lambda-function-be-added-to-excel-2021/5bd84a6d-39d8-4aba-8ddf-f4f290f21a52
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67
https://answers.microsoft.com/en-us/msoffice/forum/all/when-will-the-lambda-function-be-available/285f6165-34ef-489d-942a-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!

[arnelgp]

can you use Recordset?
see this demo.

[RIC63]

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

[RIC63]

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