asked on

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.

B Hayden

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.

aisha sharma

ASKER

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.

B Hayden

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.

Shums Faruk

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

Shums Faruk

Hi Aisha,

Did last solution provided helped you?

aisha sharma

ASKER

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.

Shums Faruk

Please upload your sample workbook with expected result.

aisha sharma

ASKER

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

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aisha sharma

ASKER

Thank you so much for your suggestion.;)

Shums Faruk

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.