How to create a report from duplicate data in worksheet using VBA and capture both unique and duplicate items. Watch the video:

In a sheet1 we have item names in column A and sales values in column B. The item names can be singular or duplicates. The user wants to get the names of the duplicate items and keep them in sheet1 along with their total sales. The items that are unique he wishes to shift to sheet2 along with their sales values.
Our strategy to find a solution to this question is as follows:
1. We copy the items data to a new worksheet and remove the duplicates to create a list of unique items.
2. Now we compare the unique list one by one with our original items list in sheet1. In the process using the count property we determine which items repeat. We also total the sales values of these duplicate items. Based on the count value of greater than 1 we keep the duplicates with the total sales in a specific location. The items with count 1 are transferred to sheet2. In this manner we are able to create a report of the duplicates and unique values.
3. Finally we delete the dynamically added new worksheet because it was a temporary helper sheet. We are now left with a neat report in sheet1 and sheet2.
Here’s the complete VBA code to differentiate between duplicates and other unique data:

Option Explicit

Sub ListUniqueValues()

Dim sht As Worksheet

Dim mySelection As Range

Dim lastrow As Long

lastrow = Sheets(“Sheet1”).Range(“A” & Rows.count).End(xlUp).Row

Range(“A2:A” & lastrow).Select

‘Set mySelection = Selection

Dim ws As Worksheet

Set ws = Worksheets.Add(After:=Worksheets(Worksheets.count))

ws.Name = “Unique”
ws.Range(“A1”) = “Item Name”
ws.Range(“A1”).Font.Bold = True

mySelection.Copy

With ws.Range(“A2”)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

ws.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes

ws.Columns(“A”).AutoFit

ws.Range(“A:B”).Font.Size = 14

Application.CutCopyMode = False

addDuplicateValues

deleteUniqueSheet

End Sub

Option Explicit

Sub addDuplicateValues()

Dim i As Long, j As Long, lastrowu As Long, lastrowd As Long, lastrowsht2 As Long, count As Long, total As Long

Dim ItemName As String

count = 0
total = 0

Sheets(“Unique”).Select
lastrowu = Sheets(“Unique”).Range(“A” & Rows.count).End(xlUp).Row

For i = 2 To lastrowu
ItemName = Sheets(“Unique”).Cells(i, 1)
Sheet1.Select
lastrowd = Sheets(“Sheet1”).Range(“A” & Rows.count).End(xlUp).Row
For j = 2 To lastrowd
If Sheets(“Sheet1”).Cells(j, 1) = Sheets(“Unique”).Cells(i, 1) Then
count = count + 1
total = total + Sheets(“Sheet1”).Cells(j, 2)
End If

Next j

If count > 1 Then

Cells(i, 5) = ItemName
Sheet1.Cells(i, 6) = total
Sheet1.Range(Cells(i, 5), Cells(i, 6)).Font.Size = 14
ElseIf count = 1 Then
lastrowsht2 = Sheets(“Sheet2”).Range(“A” & Rows.count).End(xlUp).Row
lastrowsht2 = lastrowsht2 + 1
Sheet2.Cells(lastrowsht2, 1) = ItemName
Sheet2.Cells(lastrowsht2, 2) = total
Sheet2.Range(“A:B”).Font.Size = 14
Sheet2.Columns(“A:B”).AutoFit

End If

count = 0
total = 0
ItemName = “”

Next i

End Sub

Option Explicit

Sub deleteUniqueSheet()

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
If sht.Name = “Unique” Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
Next sht

End Sub

Here’s a sample workbook to download and practice: