Compare 2 lists in excel

  BLUELEN 10:22 04 May 06

Is there a fairly simple way of comparing 2 lists in excel to find duplicate entries. Have tried a couple of ways from examples found on google but had no success.Grateful for any ideas. Thanks.

  keef66 11:02 04 May 06

click here

just by chance I read this here yesterday; can't claim credit for it myself

scroll down to the bit on checking for duplicates



  VoG II 11:50 04 May 06

If these are separate lists on different sheets you will need to use a macro - click here adapted to suit.

If you tell us more about how your data is organised we can be more specific.

  BLUELEN 12:27 04 May 06

The lists are basically just 2 very long list of invoice numbers, both on the same spreadsheet.Would like to be able to see what invoices in list1 are still shown in list2. Hope I've explained ok. Thanks

  VoG II 12:35 04 May 06

What are tyhe ranges of the rows and columns containing the lists?

Are they on the same sheet - if not, what are the sheet names?

  BLUELEN 12:45 04 May 06

List1 is in column A 1 to 5490

List2 is in column D 1 to 7495

Both in sheet 1 of workbook.

  VoG II 13:03 04 May 06

Open the workbook. ALT+F11 to open the Visual Basic Editor, Insert Module. Copy the code below and paste it in.

Sub Dups()
Dim iListCount As Integer, iCtr As Integer, x As Range
Application.ScreenUpdating = False
iListCount = Sheets("sheet1").Range("D1:D7495").Rows.Count
For Each x In Sheets("Sheet1").Range("A1:A5490")
For iCtr = 1 To iListCount
If x.Value = Sheets("Sheet1").Cells(iCtr, 4).Value Then
Sheets("Sheet1").Cells(iCtr, 1).Font.ColorIndex = 3
x.Font.ColorIndex = 3
End If
Next iCtr
Next x
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

Close (X) the Visual Basic Editor. Tools > Macro > Macros, click on Dups then click the Run button.

The macro should highlight all duplicates in both lists in red.

  BLUELEN 13:05 04 May 06

Thanks very much will give it a go.

This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

What I learned from my mentor, Oscar-winning VFX supervisor Phil Tippett

Siri vs Google Assistant