Conditional Format to match numbers

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
WalterAPOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
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?
aikimarkCommented:
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 ExpertCommented:
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
WalterAPOAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.