Highlighting Duplicate Data in a Workbook 3

[RustyTekki]

Hi,

Just wondering if anyone can help me with this. I haven't used VBA in about 7 years so I'm very rusty!

I have a workbook that has multiple sheets. In column A of each sheet I have an email address. What I need is to ensure that each email address is unique and not duplicated within the workbook.

If my memory serves me correctly what I need is to take the value of cell A1 and compare it to the value of each row in column A and use a loop to ensure that each sheet is also checked as there are new sheets added daily.

Can anyone tell me if my concept is correct and if it is would you be able to guide me on how to create this? As I said I did learn VBA many years ago but as I have not used it in so long I'm no longer confident of my ability!

Cheers,

RustyTekki

 

[Chance1234]

You could add a extra column (hide it if neccesary) and use a countIF , then have on the worksheet event

If your countif > 1 then
format cell(1, Target.row) with what ever colour you wanted



Chance,

F, G + yeah reached 50

 

[Chance1234]

apologies wrong way round with the cell thing there,

Chance,

F, G + yeah reached 50

 

[SkipVought]

hi,

"workbook that has multiple sheets. In column A of each sheet I have an email address"

Your REAL problem is that you have similar data scattered all over the place, rather than consolidated into a proper table, from which it would be infinitely easier to analyze and report, using the plethora of tools that Excel has available.

As it is now, you have severely crippled your ability to use those features.

My advise would be to spend your efforts consolidating your data and making a proper table. THAT is a worth endeavor. To continue with the current workbook structure, will yield a trail of tears, mounting frustration and wasted effort.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[clrc]

Hi,

I agree with Skipvought, consolidate in one sheet all your data. Then here is what I would do to get all unique entries, highlight the whole column by clicking on the column name, in the Excel Toolbar, go to Data>Filter>Advanced Filter, click Copy to Another Location, click Unique Records Only, then specify a cell in the Copy To box, then click OK. Make sure to record everything as a macro so that this procedure is automated. It would really be easier that way.

Hope this helps.

clrc