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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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,143,745
Messages
5,720,608
Members
422,292
Latest member
Bernd0501

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