Finding items in one list that appear in another

egoodman

New Member
Joined
Oct 7, 2002
Messages
6
My problem: I have a very large list of items that I had to break into two spreadsheets. The first spreadsheet (spreadsheet #1) has 55,931 lines and three columns the second one (spreadsheet #2) has 64,837 lines and three columns. I have a third sheet (spreadsheet #3) that consists of 1160 lines and one column. I would like to identify everytime an item in sheet #3 appears in Column B of spreadsheets 1 and 2 by having them turn the background yellow. For instance if spreadsheet 3 was a list of automobiles and spreadsheets 1 and 2 was a list of all vehicles I would like to identify everytime an automobile is located in spreadsheets 1 and 2 that appears on sheet 3 by turning the background yellow.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
On 2002-10-08 12:00, egoodman wrote:
My problem: I have a very large list of items that I had to break into two spreadsheets. The first spreadsheet (spreadsheet #1) has 55,931 lines and three columns the second one (spreadsheet #2) has 64,837 lines and three columns. I have a third sheet (spreadsheet #3) that consists of 1160 lines and one column. I would like to identify everytime an item in sheet #3 appears in Column B of spreadsheets 1 and 2 by having them turn the background yellow. For instance if spreadsheet 3 was a list of automobiles and spreadsheets 1 and 2 was a list of all vehicles I would like to identify everytime an automobile is located in spreadsheets 1 and 2 that appears on sheet 3 by turning the background yellow.

Sheet1: Let column B from B2 on house vehicles.

Sheet2: Let column B from B2 on house vehicles.

( 1.) Select all the automobile cells in Sheet3.
( 2.) Go to the Name Box on the Formula Bar.
( 3.) Type List and hit enter.
( 4.) Select all the vehicle cells in column B in Sheet1.
( 5.) Activate Format|Conditional Formatting.
( 6.) Choose Formula Is for Condition 1.
( 7.) Enter the following in the white box:

=MATCH(B2,List,0)

( 8.) Activate the Format button.
( 9.) Choose yellow on the Patterns tab.
(10.) Click OK, OK.
(11.) Repeat the steps 4 to 10 for Sheet2.
 

Forum statistics

Threads
1,181,462
Messages
5,930,075
Members
436,718
Latest member
messa57fr

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