• Status: Solved
• Priority: Low
• Security: Public
• Views: 78

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.
0
aisha sharma
• 5
• 4
• 2
1 Solution

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

Distinguished Expert - 2017Commented:
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

Distinguished Expert - 2017Commented:
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

Distinguished Expert - 2017Commented:
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

Distinguished Expert - 2017Commented:
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

Author Commented:
Thank you so much for your suggestion.;)
0

Distinguished Expert - 2017Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.