Duplicates based on the second attribute

[UmairKamal]

Hi,

I have a data set with Unique Reference number repeating in a spread sheet. however, they have a second value based on which I would like to remove them from the spread sheet.

as an example, Column A has the Unique Ref number for invoices with the corresponding issue dates in the Column E. I would to find out the duplicate invoices based on the issue date being different.

i.e. In the table above, row number 2 & 5 has same reference numbers but different issue dates, which makes the record on row 5 a duplicate. Same thing goes for Row 3 & 6, and Row 4 & 7. Although Row 11 & 12 have the same Ref numbers and the corresponding issue dates, these are not duplicates.

 

[queuesmef]

You could used conditional formatting to find and highlight the duplicates for Column A, then you can filter your data on the Highlighted cells in column A. Once filtered by highlighted color, you can remove the duplicated info.

 

[UmairKamal]

This is what I have been doing but just wondered if there was a formula / MACRO to remove such transactions from the spread sheet. Thank you for your response.

 

[JEC]

And you want the last one being removed?

 

[JEC]

Sub tst()
  Range("A1:E30").RemoveDuplicates Array(1, 5), 1
End Sub

 

[queuesmef]

another alternative: I added 3 columns; 1) Before the data "Index" column from 1 to Number of rows. 2) AFTER the data in columns G & H, I labeled G=DupOnot, and H=Reomve.
Column A, the index just helps to resort back to original order IF NEEDED. Col G - looks at the RefNo & determines if the cell above it is the same (hence, a duplicate, and puts 'dup' in the column.
in Col H - Remove is a formula to help determine if DupOnot has 'Dup' AND if the Amts are the same. Using the same logic as earlier, if the cell above it (same amount)....the formula in H2 = =IF(AND(G2="dup",C1=C2),"Remove?","") and it was copied down. The 2 step process looks at both Reference and Amount....if the word 'Remove?' is in Col H, then you can more than likely remove it. I should mention that the data should be Sorted by RefNo and then IssueDate BEFORE adding in the formulas described above. Opps missed formula in Cell G2 = =IF(B1=B2,"dup","") and copied down.

 

[JEC]

The RemoveDuplicates method is just a built-in Excel function. You could do that by clicking some buttons in the ribbon.

 

[UmairKamal]

Thank you JEC and Queuesmef for your responses and suggestions.