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.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch