Excel question

  exdragon 18:03 23 Mar 07
Locked

Hi - I'm using Excel 2003. Is it possible to find duplicates spread over 2 worksheets?

I have one w/s with a list of names, the other has some of those names plus some which are new. Can I somehow finish up with the names which are on both sheets highlighted in some way on the second sheet?

I know I can cut, past, merge and sort, but what I'd really like to do is for a repeated name on the second sheet to become, say, red, if it appears on sheet 1.

Hope that's clear!

I think conditional formatting is required. Wait for VoG!!

  VoG II 18:12 23 Mar 07

Try a macro. ALT + F11 to open the Visual Basic Editor. Insert > Module then copy and paste in:

Sub CheckDups()
Dim NewSht As Worksheet, OldSht As Worksheet, NewLastRow, iRow, Found As Range
Set NewSht = Sheets("Entries2007")
Set OldSht = Sheets("Entries2006")
NewLastRow = NewSht.Cells(Cells.Rows.Count, 4).End(xlUp).Row
For iRow = 2 To NewLastRow
Set Found = OldSht.Columns(4).Find(what:=NewSht.Cells(iRow, 4).Value, lookat:=xlWhole)
If Not Found Is Nothing Then NewSht.Cells(iRow, 4).Font.ColorIndex = 3
Next iRow
End Sub

Change the sheet names to suit and change the 4 to matrch the column that you want to check (1=A, 2=B, 3=C, 4=D etc.).

Duplicates will be coloured red.

  VoG II 18:14 23 Mar 07

Conditional Formatting may not work if more than one sheet is involved.

  exdragon 18:43 23 Mar 07

I'll give it a try!

  exdragon 19:22 23 Mar 07

Just realised that it's the first sheet I need to be coloured: I want see how many people who entered a competition last year have entered again this year.

Would you mind telling me what needs changing in the macro, please?

Thanks

  VoG II 19:23 23 Mar 07

Just enter the sheet names in the reverse order in

Set NewSht = Sheets("Entries2007")
Set OldSht = Sheets("Entries2006")

  exdragon 20:01 23 Mar 07

Just realised there are other macros in effect - will this cause a problem?

  VoG II 20:04 23 Mar 07

No - there shouldn't be any conflict. All mine does is search and colour duplicates - it doesn't sort or anything like that.

  exdragon 20:11 23 Mar 07

So - do I just paste it into the first or second sheet at the end of the existing macros? Then what next - how do I make it work, please?

  VoG II 20:14 23 Mar 07

You can paste in at the end of your existing macros. Then close the Visual Basic Editor. Tools > Macro > Macros, click CheckDups then click the Run button. You can run the macro from any sheet.

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

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…