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

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
Asked:
aisha sharma
  • 5
  • 4
  • 2
1 Solution
 
B HaydenCommented:
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
 
aisha sharmaAuthor 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
 
B HaydenCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ShumsDistinguished 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
 
ShumsDistinguished Expert - 2017Commented:
Hi Aisha,

Did last solution provided helped you?
0
 
aisha sharmaAuthor 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
 
ShumsDistinguished Expert - 2017Commented:
Please upload your sample workbook with expected result.
0
 
aisha sharmaAuthor 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
 
ShumsDistinguished 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
 
aisha sharmaAuthor Commented:
Thank you so much for your suggestion.;)
0
 
ShumsDistinguished 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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now