# 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

Relevance

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:

Sub Special_Countif()

Dim i, LastRowA

LastRowA = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Columns("M").ClearContents

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

End If

Next

Columns("M:M").Select

Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, _

Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Range("M1").Value = "P.O. Number"

Columns("M:M").AutoFit

Range("M1").Select

End Sub

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.

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.

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