Compare 2 lists in excel

  BLUELEN 10:22 04 May 2006

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 2006

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 2006

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 2006

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 2006

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 2006

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 2006

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 2006

Thanks very much will give it a go.

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

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017