A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Find matching records in two tables using mulitiple criteria



 
 
Thread Tools Display Modes
  #1  
Old June 19th, 2009, 08:48 PM posted to microsoft.public.excel.worksheet.functions
RC
external usenet poster
 
Posts: 99
Default Find matching records in two tables using mulitiple criteria


--
RCIn this case there are two tables of data of different sizes copied into
the same worksheet. What is needed is to find each row of records in the
first table that contains matching data to the rows of records in the second
table by using multiple criteria. Here is an example below where the first
table is located in columns A through D and the second table in columns F
through I. The idea is to put a function in the cells in column E that will
return a value (such as 1 or 0, or Yes or No, or True or False) to indicate
the record on the particular row in the first table has a match, or does not
match, a record contained in any row in the second table.

A B C D E F G
H I
1 ID# LN FN Color Match? ID# LN
FN Color
2 321 Adams Alan Green No 321 Adams Alan
Red
3 321 Adams Alan Red Yes 432 Allen
Bob Red
4 432 Allen Bob Red Yes 543 Baker
Dan Green
5 432 Allen Bob Green No 765 Collins
John Green
6 654 Barber Sam Green No
7 765 Collins John Red No
8 765 Collins John Green Yes



Thanks,
--
RC
  #2  
Old June 19th, 2009, 09:05 PM posted to microsoft.public.excel.worksheet.functions
Bobt
external usenet poster
 
Posts: 110
Default Find matching records in two tables using mulitiple criteria

In column E, use the following formula:

IF (A2&B2&C2&D2 = F2&G2&H2&I2, 1, 0)

Results will be 1 if they match and 0 if they don't.

"RC" wrote:


--
RCIn this case there are two tables of data of different sizes copied into
the same worksheet. What is needed is to find each row of records in the
first table that contains matching data to the rows of records in the second
table by using multiple criteria. Here is an example below where the first
table is located in columns A through D and the second table in columns F
through I. The idea is to put a function in the cells in column E that will
return a value (such as 1 or 0, or Yes or No, or True or False) to indicate
the record on the particular row in the first table has a match, or does not
match, a record contained in any row in the second table.

A B C D E F G
H I
1 ID# LN FN Color Match? ID# LN
FN Color
2 321 Adams Alan Green No 321 Adams Alan
Red
3 321 Adams Alan Red Yes 432 Allen
Bob Red
4 432 Allen Bob Red Yes 543 Baker
Dan Green
5 432 Allen Bob Green No 765 Collins
John Green
6 654 Barber Sam Green No
7 765 Collins John Red No
8 765 Collins John Green Yes



Thanks,
--
RC

  #3  
Old June 19th, 2009, 11:41 PM posted to microsoft.public.excel.worksheet.functions
RC
external usenet poster
 
Posts: 99
Default Find matching records in two tables using mulitiple criteria

Thanks Bob, and I apologize for the table in the example turning out like it
did where the columns were not in alignment. The table example looked like it
should until after I posted the question. I've realigned the table in this
reply in hope that it will show a better example.
The IF function appears to be comparing only the data on the same row for
the first and second table, and returning zeroes for each row of records. Is
there a modification, or is there another function, or nested functions, that
will use as the lookup value the data in a range of cells on a row, like the
IF function does, and then use more than one column as the lookup array?

Thanks again,
--
RC


"BobT" wrote:

In column E, use the following formula:

IF (A2&B2&C2&D2 = F2&G2&H2&I2, 1, 0)

Results will be 1 if they match and 0 if they don't.

"RC" wrote:


--
RCIn this case there are two tables of data of different sizes copied into
the same worksheet. What is needed is to find each row of records in the
first table that contains matching data to the rows of records in the second
table by using multiple criteria. Here is an example below where the first
table is located in columns A through D and the second table in columns F
through I. The idea is to put a function in the cells in column E that will
return a value (such as 1 or 0, or Yes or No, or True or False) to indicate
the record on the particular row in the first table has a match, or does not
match, a record contained in any row in the second table.

A B C D E F G
H I
1 ID# LN FN Color Match? ID# LN
FN Color
2 321 Adams Alan Green No 321 Adams Alan
Red
3 321 Adams Alan Red Yes 432 Allen
Bob Red
4 432 Allen Bob Red Yes 543 Baker
Dan Green
5 432 Allen Bob Green No 765 Collins
John Green
6 654 Barber Sam Green No
7 765 Collins John Red No
8 765 Collins John Green Yes



Thanks,
--
RC

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.