Conditional Format to match numbers

WalterAPO
WalterAPO used Ask the Experts™
on
Hello,

There are 2 sheets in the attached spreadsheet.  The 1st sheet, Weekly, has about 3,000 lines and I download this weekly.  The 2nd sheet, Perm, are my numbers.  I tried to use Conditional Formatting find duplicates, but I can’t get it to work.  I need the Conditional formatting to find my number out of the almost 3,000 and then I will delete the other numbers.  

Thank you,

Walter Humko
Bid-Number-Weekly--3-Aug-15.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Software Team Lead
Commented:
I guess you can try use a countif function to check if there's any duplicate entries and then if needed, highlight that column with Conditional Formatting accordingly.

At cell B2 in worksheet "Weekly", you can put this formula:

=COUNTIF(Perm!A:A,Weekly!A2)

Open in new window


and then drag the formula for the same for the rows in same column.
Bid-Number-Weekly--3-Aug-15-b.xlsx
aikimark
Top Expert 2014

Commented:
to find my number out of the almost 3,000
So, you're trying to find the numbers in the Perm list that are in the Weekly list or vice versa?

I will delete the other numbers
From which worksheet are you deleting rows?
aikimark
Top Expert 2014

Commented:
If deleting from the Weekly list put this formula in B2 and fill down:
=IsError(Match(A2,Perm!B:B,0))

Open in new window


Then you can filter for the False values in column B and delete those rows.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Since this is a regular process for you, I would suggest to go for a VBA solution, you will need to click a button after placing weekly data on Weekly Sheet and your duplicated numbers will be deleted instantly.

Please find the workbook and click on the button Delete Duplicates on Weekly Sheet to delete those numbers from Weekly Sheet which are present in both the sheets Weekly and Perm.

After downloading the workbook, don't forget to enable macros or contents while prompted when opening the file first time.
Bid-Number-Weekly--3-Aug-15.xlsm
WalterAPO

Author

Commented:
Thank you this is a very easy solution.   I can share this with my coworkers and it will significatnly reduce the time it takes to update my spreadsheet.   This is hard to pick the best solution, because the others are great too.  

Again, thank you.

Walter

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial