Forum Discussion

AndyT410's avatar
AndyT410
Brass Contributor
Dec 12, 2024

Groupby

Hi,

I'm trying to filter a list of all reps conversations to 1 reps conversations. I would previously have used a sort, filter, lamda combination but this looked simpler. I don't seem to be able to get it to return all matching results or change it's sort order. What am I doing wrong. Thanks in advance.

=GROUPBY(A:E,A:A:C:C,COUNTA,1,0,{3,5},A:A="AP")

 

  • You data set has 579 rows for AP, but when you summarise them with GROUPBY duplicates are, as the function name suggests grouped and counted. There are 24 entries for AP with a count greater than 1.  The number of grouped rows equals 550, but the sum of the counts is still 579.

     

  • Depends on goals, it's not clear what is desired result. GROUPBY aggregates duplicated records. If you'd like to show all records for the selected Rep, when Filter/Sort.

    • AndyT410's avatar
      AndyT410
      Brass Contributor

      I'm trying to get the last contact per school per contact. I just used sum as it said you have to use a function and I was going to ignore that column.

    • AndyT410's avatar
      AndyT410
      Brass Contributor

      I'm trying to get a list of the last contact made at each school by topic.

  • I do not like entire column references with array formulas (unless you happen to have 1048575 records).  As an aside it may be worth noting that there is now a function TRIMRANGE that will convert entire column references to ranges that describe the data more precisely so as to avoid wasting processing time).  In the attached I have used Tables and reduced the number of columns used to group the data and at the same time to use a single column to count the numbers for each group.

    = GROUPBY(
        Table1[[Rep]:[Account Number]],
        Table1[Rep],
        COUNTA,
        1, 0, ,
        Table1[Rep] = "AP"
     )

     

  • sandip2655's avatar
    sandip2655
    Copper Contributor

    Use this Formula =GROUPBY(HSTACK($A$2:$A$5513,$B$2:$B$5513,$C$2:$C$5513),$F$2:$F$5513,SUM,0,0)

     

    Excel Copy Attached

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    You data set has 579 rows for AP, but when you summarise them with GROUPBY duplicates are, as the function name suggests grouped and counted. There are 24 entries for AP with a count greater than 1.  The number of grouped rows equals 550, but the sum of the counts is still 579.

     

    • AndyT410's avatar
      AndyT410
      Brass Contributor

      Thanks. I didn't realise it was removing duplicates. That explains it.

Resources