1

I want to group 3 columns (Fruit, Color & Vendor) and get their corresponding group count in excel, without using any VBA code, but just simply using Excel functions.

Fruit Vendor Ledger Table:

ID Fruit Color Vendor
1 Apple Red Rocket Apples
2 Apple Red Rocket Apples
3 Apple Yellow Rocket Apples
4 Apple Green Rocket Apples
5 Apple Green Rocket Apples
6 Apple Green Rocket Apples
7 Apple Green Sweet Greens
8 Apple Green Sweet Greens
9 Orange Orange Tangerines
10 Orange Orange Tangerines
11 Orange Orange Tangerines
12 Banana Yellow Phils
13 Banana Yellow Phils
14 Banana Green Brighton
15 Banana Green Brighton
16 Banana Green Brighton

Expected result:

Fruit Color Vendor Count
Apple Red Rocket Apples 2
Apple Yellow Rocket Apples 1
Apple Green Rocket Apples 3
Apple Green Sweet Greens 2
Orange Orange Tangerines 3
Banana Yellow Phils 2
Banana Green Brighton 3

In T-SQL, I would have written the SQL Query as follows. I wanted to know the function or feature by which I could achieve the same in Excel.

SELECT [Fruit], [Color], [Vendor], COUNT(1)
FROM [dbo].[FruitVendorTable] WITH (NOLOCK)
GROUP BY [Fruit], [Color], [Vendor]
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
GaryTheBrave
  • 97
  • 11
  • 1
    use a pivot table. – Scott Craner May 16 '23 at 21:01
  • @ScottCraner - I thought Pivot doesn't give you one single row for each group but rather creates like multiple levels, one for each column. Unless you can share a screenshot of we can use Pivot and still have all 3 columns in a single level. – GaryTheBrave May 16 '23 at 21:05
  • 2
    you mean like this: https://imgur.com/a/znntUQk – Scott Craner May 16 '23 at 21:06
  • 1
    Just turn off the subtotals and Grand totals, then show in tabular form and repeat values. – Scott Craner May 16 '23 at 21:07
  • 1
    Yes, exactly like that. How do I turn off the subtotals and grand totals? – GaryTheBrave May 16 '23 at 21:12
  • Easy with Pivot Tables as mentioned by @ScottCraner Sir, to turn off the subtotals goto --> Design Tab --> Left Corner in the Ribbon, Subtotals --> **Do Not Show Subtotals** – Mayukh Bhattacharya May 16 '23 at 21:13
  • 1
    When you select on of the cells in the pivot table you get the ability to open the Design tab. It is in there. – Scott Craner May 16 '23 at 21:14
  • 1
    Perfect. It was actually 2 things for me. First is **Subtotals --> Do Not Show Subtotals** and the second was **Report Layout --> Show in Tabular Form**. Thank you @ScottCraner & MayukhBhattacharya. Much appreciated. – GaryTheBrave May 16 '23 at 21:18
  • @GaryTheBrave btw I have posted an alternative using formulas in answers you can give a try. but yes pivot table will be easy here. – Mayukh Bhattacharya May 16 '23 at 21:18
  • 1
    Thanks @MayukhBhattacharya. I actually needed a Pivot table solution. I keep wanting to do quick GROUP BY clauses but never was able to get a similar looking result from SQL in Excel. But now thanks to you two, I was able to. I appreciate it. – GaryTheBrave May 16 '23 at 21:24

2 Answers2

4

Perhaps with Excel Formulas try:

enter image description here

• Formula used in cell F2

=LET(
    α, B2:B17 & "|" & C2:C17 &
        "|" & D2:D17,
    UNIQUE(
        HSTACK(
            B2:B17,
            C2:C17,
            D2:D17,
            MMULT(
                N(
                    TOROW(α) =
                        α
                ),
                SEQUENCE(
                    ROWS(α),
                    ,
                    ,
                    0
                )
            )
        )
    )
)
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
3

Posting the answer, on behalf of @ScottCraner and @MayukhBhattacharya. Thank you both!

  1. Pivot the Table
  2. Design Tab > Subtotals > Do Not Show Subtotals
  3. Design Tab > Reports Layout > Show in Tabular Form
  4. Design Tab > Reports Layout > Repeat all Item Labels

Pivot Table results

GaryTheBrave
  • 97
  • 11