# removing repeatative data from difrrent excel sheets

I have worksheets named WS1 and WS2. I need to compare Column C of WS1 with Column D of WS2. If both columns contain same values then I need to highlight both the cells of "Column C of WS1" and "Column D of WS2".
Example:
Column C of WS1
500,000.00
260,000.00
65,000.00
30,000.00
46,104.00
530,000.00
350,000.00
55,000.00
7,000.00
2,520.16
30,000.00
210,000.00
650,000.00
10,000.00

Column D of WS2
2520.16
500,000.00
260,000.00
65,000.00
30,000.00
46,104.00
530,000.00
350,000.00
55,000.00
7,000.00
30,000.00
1,827,099.63
210,000.00
650,000.00
10,000.00
If values of Column C = Value of Column D then the cell containing same values in both columns is highlighted. My data is not uniform. They may have repetitive values. I am doing it manually right now and it is time-consuming as I have a lot of data to work on. Can you please suggest me.
I am looking for exact matches for currency amounts and also I want the amounts which has been already matched to be left out. The value in each cell should be matched with only one value of another cell. Leave out the next repeated value to match with next.
Asked:
###### 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.

Commented:
Select all the cells on WS1 and use the following conditional formatting formatting formula:

=NOT(ISERROR(MATCH(C1:C14,'WS2'!\$D\$1:\$D\$15,0)))

Repeat for WS2 using the :

=NOT(ISERROR(MATCH(D1:D15,'WS1'!\$C\$1:\$C\$15,0)))

You can select whatever colour fill you want to highlight duplicate cells.
1
Author Commented:
But conditional formatting feature does not work in 2 different worksheets or workbooks. and will this formula will leave figures which already have been matched because i have repeatative figures in bulk.
0
Commented:
On my Excel 2010 and greater CF can be used on separate worksheets- are you using Excel 2007 or earlier? If so, you might be able to get around it as follows:

1. Make the cells on WS1 a 'named range' eg call it WS1Range. Do the same on the cells on WS2 (so WS2range)
2. On ws1, use this CF formula on the first cell:

=NOT(ISERROR(MATCH(C1:C15,WS2Range,0))) and apply the desired formatting to the entire range of cells => I'm assuming that ws1 has only 15 cells in column C. Adjust as required.

3. On WS2, use this CF formula on the first cell:

=NOT(ISERROR(MATCH(D1:D15,WS1Range,0))). As above, apply formatting to the required number of cells.
1
Excel & VBA ExpertCommented:
Hi Aisha,

Your subject says Removing Duplicate Data and content says Highlight Duplicates. I am not sure if you want to delete Duplicate Data in Sheet2,

Try in attached...Click Button Highlight & Delete Dups.

It will highlight both the Columns of Col C in Sheet1 & Col D in Sheet2 and delete duplicate rows (which is 30,000) in Sheet2.

Hope this helps.

PS: Comparing & Deleting Duplicates on Amount is not advisable, instead try some other columns which has Alpha Numeric or Text values.
Compare-Two-Columns---Highlight.xlsm
1
Excel & VBA ExpertCommented:
Hi Aisha,

Did last solution provided helped you?
0
Author Commented:
NOPE,
actually i have repeatative nature of figure and i want amount which have been matched once to be left alone. and its not comparing all my figures.
0
Excel & VBA ExpertCommented:
Please upload your sample workbook with expected result.
0
Author Commented:
i want entries which have been matched from both the sheets to be either removed or highlighted.
by comparing column c of sheet 1 to column B of sheet 2.
sample.xlsx
0
Excel & VBA ExpertCommented:
Hi Aisha,

Previously provided code highlights the entries which are matched in Sheet1 & Sheet2. And deletes the duplicate entries in Sheet2.

Check in attached again.
Aisha_Sharma_Compare2Columns.xlsm
1

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.

Author Commented:
Thank you so much for your suggestion.;)
0
Excel & VBA ExpertCommented:
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
0
###### 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 Office

From novice to tech pro — start learning today.