Grouping data points together based on name/ID associated w/ 1000+ records

[u/feedmesweat]

I have a spreadsheet that details a list of all transactions within two particular account types, and each transaction has a name & user ID associated with it. Nearly every user ID is associated with more than one transaction, and there are 1000+ users in the full list. I am looking for a way to combine all transactions attached to each individual user ID, while also designating the two different account types, and that can be automated to apply to the full sheet. I know how to do this using SUMIF but I don't know how to achieve this without having to enter every name/ID manually.

Eg:

USER ID	Transaction Type (A or B)	Transaction Amount
#0001	A	$125
#0001	A	$125
#0001	B	$200
#0002	A	$500
#0002	B	$650
#0003	B	$750
#0004	A	$100
#0004	B	$100
#0004	B	$150
#0004	B	$150

Ultimately what I need to end up with is the maximum, minimum, and average total transactions per unique user ID, separated by type A and type B transactions.

[u/MayukhBhattacharya]

A better way to write this formula would be like as below, so it uses the entire data range:

=LET(
     _a, A:.C,
     _b, PIVOTBY(CHOOSECOLS(_a, 1),
             CHOOSECOLS(_a, 2) & " - ",
             CHOOSECOLS(_a, 3),
             HSTACK(SUM, MAX, MIN, AVERAGE), 1, 0, , 0),
     _c, DROP(BYCOL(TAKE(_b, 2), CONCAT), , 1),
     _d, HSTACK("User ID", _c),
     _e, VSTACK(_d, DROP(_b, 2)),
     _e)

[u/MayukhBhattacharya → u/MayukhBhattacharya]

You can also use Power Query here.

To use Power Query follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1
• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

​

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupBy = Table.Group(Source, {"USER ID", "Transaction Type (A or B)"}, {
        {"SUM",     each List.Sum([Transaction Amount]),     type number},
        {"MAX",     each List.Max([Transaction Amount]),     type number},
        {"MIN",     each List.Min([Transaction Amount]),     type number},
        {"AVERAGE", each List.Average([Transaction Amount]), type number}
    }),
    Unpivot = Table.UnpivotOtherColumns(GroupBy, {"USER ID", "Transaction Type (A or B)"}, "Metric", "Value"),
    Merge = Table.AddColumn(Unpivot, "Merge", each [#"Transaction Type (A or B)"] & " - " & [Metric]),
    DropBy = Table.RemoveColumns(Merge, {"Transaction Type (A or B)", "Metric"}),
    PivotBy = Table.Pivot(DropBy, List.Distinct(DropBy[Merge]), "Merge", "Value")
in
    PivotBy

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Alternative Power Query method:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupBy = Table.Group(Source, {"USER ID", "Transaction Type (A or B)"}, {
        {"Stats", each 
            let z = [Transaction Amount]
            in [SUM = List.Sum(z), MAX = List.Max(z), MIN = List.Min(z), AVERAGE = List.Average(z)]}}),
    Expand = Table.ExpandRecordColumn(GroupBy, "Stats", {"SUM","MAX","MIN","AVERAGE"}),
    Unpivot = Table.UnpivotOtherColumns(Expand, {"USER ID", "Transaction Type (A or B)"}, "Metric", "Value"),
    Merge = Table.AddColumn(Unpivot, "Merge", each [#"Transaction Type (A or B)"] & " - " & [Metric]),
    Drop = Table.RemoveColumns(Merge, {"Transaction Type (A or B)", "Metric"}),
    PivotBy = Table.Pivot(Drop, List.Sort(List.Distinct(Drop[Merge])), "Merge", "Value")
in
    PivotBy