Matching Data Lists

clilley113

New Member
Joined
Feb 7, 2017
Messages
6
Hi,

I have two lists of data over two excel sheets within a workbook. ('sheet 1' & 'Matured')

What I would like to do is input some form of formatting that will highlight any cell on 'sheet one' that contains a value that is on the 'Matured' sheet.

'Sheet 1's data is in cells U2 to U2474

'Matured' data is in cells B2 to B181

Does any one have any suggestions?

Thanks in advance as always!

C
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Re: Matching Data Lists - HELP!

Check out Conditional Formatting. Highlight U2:U2474 of Sheet 1, Conditional Formatting, New Rule, Use a Formula,

=COUNTIF(Matured!B$2:B$181,U2)

Format Fill whatever color you like.

I haven't tested this formula but it should work. Let us know.
 
Upvote 0

clilley113

New Member
Joined
Feb 7, 2017
Messages
6
Re: Matching Data Lists - HELP!

Amazing that worked perfectly!

One more question...

If i want to highlight any rows that are duplicated, say from row starting A2 - row A2074 how would i do that?

Thanks so much for your help!

C
 
Upvote 0

mesco019

New Member
Joined
Feb 23, 2017
Messages
5
Re: Matching Data Lists - HELP!

Hello there!

to highlight any duplicated value in the row you can use a conditional rule, first you have to select the range that you would like the rule to be apply, after you go to create a rule, you will see an option that will say "apply format to unique or duplicated values" and then select duplicated values and the color that you will like to have :)
 
Upvote 0

clilley113

New Member
Joined
Feb 7, 2017
Messages
6
Re: Matching Data Lists - HELP!

Thanks for the reply.

If i do that it wont only highlight only entire rows that match it will only do it by cell.

Is there a way you an only highlight entire rows that match?

Thanks

C :)
 
Upvote 0

Forum statistics

Threads
1,186,986
Messages
5,960,961
Members
438,504
Latest member
KristofGoossens

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top