Copy Duplicate Rows to a New Sheet

_karlo

New Member
Joined
Nov 7, 2019
Messages
4
Hi all,

I am looking to copy duplicate rows from a large sheet of credit card charges.

I want to copy any duplicate rows based on a string text in column "AL" on sheet "Trx List" to a sheet named "Duplicates".

I'm really new to VBA so any help would be greatly appreciated!

Thanks,
K
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Show us a sample of some data that you have so that we can see exactly what you need.
 
Upvote 0
Try this

Code:
Sub Copy_Duplicate()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a() As Variant, lr As Long, r As Range, dict As Object
  Set sh1 = Sheets("Trx List")
  Set sh2 = Sheets("Duplicates")
  lr = sh1.Range("L" & Rows.Count).End(xlUp).Row
  a = sh1.Range("AL1:AL" & lr).Value
  Set r = sh1.Range("A" & lr + 1)
  Set dict = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    If dict.Exists(a(i, 1)) Then
      Set r = Union(r, sh1.Range("A" & i))
    Else
      dict.Add a(i, 1), Empty
    End If
  Next
  r.EntireRow.Copy sh2.Range("A2")
End Sub
 
Upvote 0
Chain IDSub IDDBAOutletSub DateTIDBatch NoItem NoCard noCard TypeAuth CodeAuth Src CodePOS Entry modeTrx TypeDCC EligbleDCC IndCardholder amtCurrencyTrx AmountCurrencyCashback amtCurrencyTrx dateTrx timeRRNTnx Ref txtVoid IndicatorCustom DataBatch Control NoConversion ratePaper/ElectronicWallet Typewallet DataDateFinal AmtCategoryUnique identifier
21014XXXXXXXXXXXXXXXXXDUBBR02/11/201900002101XXXXXXX3031XXXXX******9696MasterCard030XXX0CONTACT EMVSaleYY6.37USD5.5EUR0.00USD01/11/201912:00:0011000XXXXXNM000000000000000303110205001.158268ELECTRONIC01/11/20195.5F&B01/11/2019XXXXX******96965.5
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""01/10/20194XXXX******73036.5
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""01/10/20194XXXX******73036.5

<tbody>
</tbody>

Above is a small sample I have put together.

In the far most rright column (AL) I have made a text string which includes the Date of the transaction, the card number and the amount.

Based on that, any duplicates in "AL" I need to investigate.

Thanks for your help!
 
Upvote 0
Hi Dante,

Thanks for that!

I did try that and it worked, the only thing is that it did not copy both the duplicate records to the duplicate tab.

So if I have two duplicate values in column "AL" it copies only one of them to the "Duplicate" tab.

Is it possible to have the two rows (or three, four etc) to the duplicate tab?

Thanks so much!
 
Upvote 0
Try this

Code:
Sub Copy_Duplicate()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a() As Variant, lr As Long, r As Range, dict As Object
  Set sh1 = Sheets("Trx List")
  Set sh2 = Sheets("Duplicates")
  lr = sh1.Range("L" & Rows.Count).End(xlUp).Row
  a = sh1.Range("AL1:AL" & lr).Value
  Set r = sh1.Range("A" & lr + 1)
  Set dict = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    If dict.Exists(a(i, 1)) Then
[COLOR=#0000ff]      Set r = Union(r, sh1.Range("A" & i), sh1.Range("A" & dict(a(i, 1))))[/COLOR]
    Else
      dict.Add a(i, 1)[COLOR=#0000ff], i[/COLOR]
    End If
  Next
  r.EntireRow.Copy sh2.Range("A2")
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top