Forum Discussion
AndyT410
Dec 12, 2024Brass Contributor
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.
- PeterBartholomew1Silver Contributor
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" )
- sandip2655Copper Contributor
unable to attached Excel File
- sandip2655Copper 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_EekelenPlatinum 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.
- AndyT410Brass Contributor
Thanks. I didn't realise it was removing duplicates. That explains it.