EXCEL function working like SQL group by + count(distinct *)?
- by Solo
Suppose I have an EXCEL sheet with below data
CODE (COL A) | VALUE (COL B)
==============================
A01 | 10
A01 | 20
A01 | 30
A01 | 10
B01 | 30
B01 | 30
Is there an EXCEL function working like ..
SELECT CODE, count (Distinct *) FROM TABLE GROUP BY CODE
CODE | Distinct Count of Value
===================================
A01 | 3
B01 | 1
or, better yet, Can we have an excel formula pasted in Column C to get something like this:
CODE (COL A) | VALUE (COL B) | DISTINCT VALUE COUNT WITH MATCHING CODE (COL C)
===============================================================================
A01 | 10 | 3
A01 | 20 | 3
A01 | 30 | 3
A01 | 10 | 3
B01 | 30 | 1
B01 | 30 | 1
I know I can use pivot table to get this result easily.
However due to reporting requirements I have to append the "distinct count" column to the excel sheet, hence pivot table is not an option.
My last resort is to use Excel Macro (Which is fine), but before that I would like to learn whether excel functions can accomplish this kind of task.
Many thanks!
