GROUPBY is a really amazing addition to Excel, it makes so many things a lot easier. The only regrets I have with it is that a) it does not have argument for excluding "tags", i.e. row_fields, and b) it does not have an option to preserve the original order of the data as it is useful in some situations. But I guess we could not have all that we would like to and these things are addressable by combining it with other functions...
Thanks for you comment, I don't know, what a solution to a) could look like - but for b) you could do something like wrap it in a SORTBY function with a by_array XMATCH to the original ordered values.
@@excelwithExcel-xlsx Many thanks for your reply, yeah, we will have to live with that. As far as a) is concerned what I meant was something like DROP the 1st column as an inherent argument of GROUPBY. Also, after doing some testing it seems that while GROUPBY with COUNTA applied to a single cell returns the result when the input is number, it does return #VALUE when the input is text - would that be a mistake to be fixed or does that resut from something?
@@hagiasofia6179 Okay - yes, I think in order to create a clean formula, the Microsoft team decided to drop that argument because you can create it yourself with DROP. Interesting - well, as far as I know, it should be the other way around (error/ 0 for a number and 1 for a text...but I also don't know why you would want to apply GROUPBY to single results. 😅
GROUPBY is a really amazing addition to Excel, it makes so many things a lot easier. The only regrets I have with it is that a) it does not have argument for excluding "tags", i.e. row_fields, and b) it does not have an option to preserve the original order of the data as it is useful in some situations. But I guess we could not have all that we would like to and these things are addressable by combining it with other functions...
Thanks for you comment, I don't know, what a solution to a) could look like - but for b) you could do something like wrap it in a SORTBY function with a by_array XMATCH to the original ordered values.
@@excelwithExcel-xlsx Many thanks for your reply, yeah, we will have to live with that. As far as a) is concerned what I meant was something like DROP the 1st column as an inherent argument of GROUPBY. Also, after doing some testing it seems that while GROUPBY with COUNTA applied to a single cell returns the result when the input is number, it does return #VALUE when the input is text - would that be a mistake to be fixed or does that resut from something?
@@hagiasofia6179 Okay - yes, I think in order to create a clean formula, the Microsoft team decided to drop that argument because you can create it yourself with DROP.
Interesting - well, as far as I know, it should be the other way around (error/ 0 for a number and 1 for a text...but I also don't know why you would want to apply GROUPBY to single results. 😅
@@excelwithExcel-xlsx I came accross this when testing usefulness of GROUPBY for REDUCE - VSTACK formula.
The output cannot be column A,D,E , it has to be fr A to E , right ?
No, you can also do something like =GROUBY(A,D:E,FUNCTION)