Count unique records in excel?
I have a list in excel that have orders numbers the order numbers repeat I only want to count the order number once. Is it possible to it with a formula? I do not want to used the subtotal function. I want to extract the orders submitted without altering the list
- garbo7441Lv 71 decade agoFavorite Answer
Here is one way to do it using a macro. The following example assumes your order numbers are in column A. It will create a unique list of the order numbers in column M, sorted.
If your order number column is not column A, and/or you wish the list to be in a differenct column, modify the macro:
Change the three "A" references to the column letter your order numbers are in, i.e. "C", "G", etc.
Change the two "M" references to the column letter you wish the list to appear in, i.e. "V", "Z", etc.
Change the three "M:M" references to the column letter for the list, i.e. "V:V", "Z:Z", etc.
Change the three "M1" references to the column letter for the list, i.e. "V1", "Z1", etc.
Then copy the macro, modified if necessary, to the clipboard:
Dim i, LastRowA
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To LastRowA
If Application.CountIf(Range("M:M"), Cells(i, "A")) = 0 Then
Cells(i, "M").Offset(1, 0).Value = Cells(i, "A").Value
Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Range("M1").Value = "P.O. Number"
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the editing area to the right.
Close the VBE and return to the worksheet.
Press ALT + F8
When the Macros window opens, highlight the macro and click 'Options..'
Enter a letter to be used as a keyboard shortcut and click 'OK'.
Close the Macros window.
To run the macro press CTRL + your shortcut letter.
Any time you wish to create a new list, just run the macro.
- 1 decade ago
Why not use the built in Data filter option.
Choose the order number you want display the highlight the range to see how many rows.
You could also use a pivot table to display the order numbers and count how many times it appears.
If you need any more help, drop me an e-mail
Hope this helps