Unlock the Hidden Power of GROUPBY: Mastering Case Sensitivity
Why the case of the characters in the first column of the GROUPBY Function changes?
in the example shown, we have product codes and location.
After using GROUPBY , you will notice that the case of the characters in the first column changes.
This happens because the Function sorts all the columns entered in the Row Fields. Based on the sorting, it applies or generalizes the case of the characters in the first cell to the rest of the cells.
if the first cell in the sorted column has uppercase characters, the entire column will adopt uppercase. Conversely, if the first cell contains lowercase characters, the entire column will reflect lowercase.
If you want to use the equation with data while taking into account the case of the characters, the equation will give you inaccurate data.
In the examples shown, there are product codes and quantities. However, the products differ.
For example :
product A-100 is not the same as product a-100.
Using GROUPBY, it will consider the two as the same thing, and therefore the data summarization will be inaccurate.
Additionally, if there is other data such as location, as in the second example,
Product A-100 that was sold in Egypt is not the same as product a-100 that was sold in the same location.
Example 1:
Case Insensitivity:
=GROUPBY(E7:E16,F7:F16,SUM)
✔Case Sensitivity:
=LET(
RowField, E7:E16,
Values, F7:F16,
DROP(
GROUPBY(
HSTACK(
BYROW(
--EXACT(BYROW(RowField, CONCAT), TOROW(BYROW(RowField, CONCAT))),
CONCAT
),
RowField
),
Values,
SUM
),
,
1
)
)Example 2:
Case Insensitivity:
=GROUPBY(F7:G16,H7:H16,SUM)
✔Case Sensitivity:
=LET(
RowField, F7:G16,
Values, H7:H16,
DROP(
GROUPBY(
HSTACK(
BYROW(
--EXACT(
BYROW(RowField, CONCAT),
TOROW(BYROW(RowField, CONCAT))
),
CONCAT
),
RowField
),
Values,
SUM
),
,
1
)
)Hazem Hassan -Microsoft Excel MVP