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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|