Consulting

Results 1 to 13 of 13

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

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location

    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
    Attached Files Attached Files

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Out of interest, what version of Excel do you use?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hi georgiboy

    ..right..i forget..

    i use Excel 2016 and 2021 (2021 preferred)

    thank you for reminding me

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:

    2023-02-07_223319.jpg

    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.
    Attached Files Attached Files
    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.

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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)))
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  6. #6
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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 ?

  7. #7
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Good morning georgiboy


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

    thanks for your help
    Last edited by RIC63; 02-08-2023 at 03:24 AM.

  8. #8
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Quote Originally Posted by RIC63 View Post
    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?
    Attached Files Attached Files
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  9. #9
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    no as I wrote above now it's ok, I have the same results as you


    Thanks again

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by RIC63 View Post
    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!
    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.

  11. #11
    can you use Recordset?
    see this demo.
    Attached Files Attached Files

  12. #12
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

  13. #13
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •