How to count and duplicates and hso wonly unique records in Excel

I need to show a count in cell J the amount of duplicate records in WORK_ORDERS and show ony unique records, no Dulpicates. I have attached an example of the raw data and one set manually ot the way i need it to look.
Capture.PNG
Capture2.PNG
maximus1974Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexandre MichelManager; IT ConsultantCommented:
Hi

What about you use the "Remove Duplicate" Function in Excel?
Deduplicate
You could create one more column that is a concatenation of all your column and work on that

Alex
ShumsExcel & VBA ExpertCommented:
Hi,

Assuming you have Data in sheets Called "Data" change the sheet name accordingly in below code:
Sub GetDuplicateCounts()
Dim DataSh As Worksheet, xWs As Worksheet, NewSh As Worksheet
Dim DataLR As Long, NewLR As Long

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Define Variables
Set DataSh = Worksheets("Data") 'Change sheet name here
DataLR = DataSh.Range("A" & Rows.Count).End(xlUp).Row

'Delete sheets other than Data Sheet
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    If xWs.Name = "Count" Then
        xWs.Delete
    End If
Next xWs
Application.DisplayAlerts = True

'Create New Sheets
Set NewSh = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
NewSh.Name = "Count"

'Copy Unique Values to New Sheets
DataSh.Range("A1:I" & DataLR).AdvancedFilter xlFilterCopy, CopyToRange:=NewSh.Range("A1"), Unique:=True
Application.CutCopyMode = False
NewSh.Activate
NewSh.Range("J1").Value = "Count"
NewLR = NewSh.Range("A" & Rows.Count).End(xlUp).Row
NewSh.Range("J2:J" & NewLR).FormulaR1C1 = "=IF(RC1="""","""",COUNTIF(Data!C1,RC1))" ' Change the sheet name here, replace "Data"
NewSh.Range("J2:J" & NewLR).Value = NewSh.Range("J2:J" & NewLR).Value
NewSh.Columns.AutoFit
NewSh.Range("A1").Select

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window


If you have any problem running above code, please post a sample workbook.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.