matching column items within a group

[Stephen09]

Dear Forum,

I have three columns in Excel. Column A contains the names of various academic institutions. Almost all of these occur more than once. Column B contains the city or cities they belong to. Column C contains a list of cities, some of which are the actual cities the institution belongs to, some of which are not. I am looking for a formula for Column D which tells me if the value in Column C matches a value in Column B for a given academic institution.

Thank you for any guidance,

Stephen

 

[Jon von der Heyden]

Hi Stephen

Welcome to the board.

=C2=B2

This will yield TRUE if the two values match, FALSE if they do not. Copy and paste this formula to end of range and then use autofilter (Data -> Filter -> Autofilter) to isolate those that do / do not match.

Regards
Jon

 

[Stephen09]

Thanks for the quick reply, but my problem is that the values in Columns B and C do not always match up that cleanly. For example, the true city of affiliation may be located in column B7 and its corresponding city in C8.

 

[Jon von der Heyden]

Hi Stephen

Could you possibly provide a sample of your data? See the 1st link in my signature to see the options available in terms of supplying sample data.

Please also elaborate a little - what are the criteria to determine a match?

Regards
Jon

 

[Stephen09]

I hope I am posting this correctly:

State Profiles2.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Affiliation	AFF_TRUE_CITY	VP_PROFILE_CITY
2	ABBOTTLABS	ABBOTTPK	ABBOTTPK		ForeveryaffiliationIwanttodetermineifthevaluesassociatedwiththat
3	ABBOTTLABS	NCHICAGO	NCHICAGO		affiliationfromColumnCmatchatleastonecorrespondingvalueforthat
4	ACADATHENS	ATHENS	THEWOODLANDS		affiliationfromColumnB.
5	ACADSINICA	ANHUI	LONGBEACH
6	ACADSINICA	BEIJING	LONGBEACH
7	ACADSINICA	BIJING	LONGBEACH
8	ACADSINICA	CANTON	LONGBEACH
9	ACADSINICA	CHANGCHUN	LONGBEACH
10	ACADSINICA	CHENGDU	LONGBEACH
11	ACADSINICA	DALIAN	LONGBEACH
12	ACADSINICA	FUJIAN	LONGBEACH
13	ACADSINICA	GUANGZHOU	LONGBEACH
14	ACADSINICA	GUIYANG	LONGBEACH
15	ACADSINICA	HEFEI	LONGBEACH
16	ACADSINICA	JILIN	LONGBEACH
17	ACADSINICA	JILINPROVINCE	LONGBEACH
18	ACADSINICA	JILING	LONGBEACH
19	ACADSINICA	KUNMING	LONGBEACH
20	ACADSINICA	LANZHOU	LONGBEACH
21	ACADSINICA	NANJING	LONGBEACH
22	ACADSINICA	NANKANG	LONGBEACH
23	ACADSINICA	QINGDAO	LONGBEACH
24	ACADSINICA	SHANGHAI	LONGBEACH
25	ACADSINICA	SHANXI	LONGBEACH
26	ACADSINICA	SHENGYANG	LONGBEACH
27	ACADSINICA	SHENGYANY	LONGBEACH
28	ACADSINICA	SHENYANG	LONGBEACH
29	ACADSINICA	TAIPEI	LONGBEACH
30	ACADSINICA	TOKYO	IRVINE
31	ACADSINICA	WUHAN	LONGBEACH
32	ACADSINICA	XIAN	LONGBEACH
33	ACCELRYSINC	CAMBRIDGE	SANDIEGO
34	ACCELRYSINC	HOUSTON	SANDIEGO
35	ACCELRYSINC	SANDIEGO	SANDIEGO
36	ADELPHIUNIV	GARDENCITY	GARDENCITY
37	ADVDEVCORP	BURLINGTON	BURLINGTON
Sheet2