Forum Discussion

AndyT410's avatar
AndyT410
Brass Contributor
Dec 12, 2024
Solved

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 ...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 13, 2024

    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.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 13, 2024

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"
 )

 

Resources