Count Unique within Group

**Krazy Kasper** · 09-23-2008, 03:55 PM

My spreadsheet (about 24,000 rows) contains order numbers and promotion codes. I need to count the unique orders within each promo code and show just the PromoCode with the totals. The Sub-Total function doesn't seem to allow for "Count Unique records". I tried using an "Array" formula but I need to adjust it for each group and even then the PromoCode doesn't appear on the same line. I would like to avoid using a Pivot Table.

Sample Spreadsheet Data:
ORDER# PROMOCODE
123456 B100
987654 B100
555555 B100
444444 B100
111111 C700
222222 C700
999999 J300
777777 J300
666666 J300

Should Yield:
4 B100
2 C700
3 J300

Can anyone help me figure this out?
Thanks,

**Leith Ross** · 09-23-2008, 08:56 PM

Hello Krazy Kasper,

This macro should get you going. It will place the count and the promo code in adjacent columns on the Active Sheet. Let me know if you want to do something different. You will need to change the starting cells for the promo codes and the list. They are marked in red.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross