Excel question

  exdragon 18:03 23 Mar 2007
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!

  [email protected]© 18:09 23 Mar 2007

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

  VoG II 18:12 23 Mar 2007

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 2007

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

  exdragon 18:43 23 Mar 2007

I'll give it a try!

  exdragon 19:22 23 Mar 2007

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 2007

Just enter the sheet names in the reverse order in

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

  exdragon 20:01 23 Mar 2007

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

  VoG II 20:04 23 Mar 2007

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 2007

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 2007

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.

Elsewhere on IDG sites

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)