Comparing Data - please help!

[robt]

Hi all

I have two lists of information. Most of the lists contain the same names of people, but not all. I've used =OR(EXACT( etc and this tells me whether each name can be found anywhere within the other list with a simple true or false answer. The problem is that if it is true, i want excel to take some data from a cell next to the persons name and place it into the cell next to the same name on the other list. Does this make any sense?

[Aladin Akyurek]

On 2002-04-23 12:31, robt wrote:
Hi all

I have two lists of information. Most of the lists contain the same names of people, but not all. I've used =OR(EXACT( etc and this tells me whether each name can be found anywhere within the other list with a simple true or false answer. The problem is that if it is true, i want excel to take some data from a cell next to the persons name and place it into the cell next to the same name on the other list. Does this make any sense?

Does this make any sense?

Yes, it does. But, We got here a city map without street names on it .

Lets say that the list1 is in A2:B10, where A2:A10 holds the names, and list2 in D4:D10 also holds a set of names:

In E4 enter: =VLOOKUP(D4,$A$2:$B$10,2)

would seem to do what you're asking.

If not, give the exact ranges of the lists along with expected results.

Aladin

[robt]

Heres more info

sheet 1 contains a list of names with the firstname in A1:A202, the surname in B1:B202
and a unique ten digit personal id number in C1:C202

sheet 2 is identical to the first but there are no numbers listed in column C and only 98 names are listed, but are all present somewhere within the first sheet. I need excel to find the people in the first list and put their id number in column C, sheet 2

i.e find this persons id number from this sheet and place it next to his name in the other

[Aladin Akyurek]

On 2002-04-23 12:55, robt wrote:
Heres more info

sheet 1 contains a list of names with the firstname in A1:A202, the surname in B1:B202
and a unique ten digit personal id number in C1:C202

sheet 2 is identical to the first but there are no numbers listed in column C and only 98 names are listed, but are all present somewhere within the first sheet. I need excel to find the people in the first list and put their id number in column C, sheet 2

i.e find this persons id number from this sheet and place it next to his name in the other

Does column C in sheet2 house first names or surnames?

[robt]

Column C in sheet 2 does not contain any data. The firstnames and surnames are in columns A and B as in sheet 1. It's the personal ID number in Column C of sheet one that needs to go in column C of sheet two

[Aladin Akyurek]

On 2002-04-23 14:55, robt wrote:

Column C in sheet 2 does not contain any data. The firstnames and surnames are in columns A and B as in sheet 1. It's the personal ID number in Column C of sheet one that needs to go in column C of sheet two

OK, Rob, the last question was unnecessary. Upon close reading I see that column A and B in Sheet2 must have first names and surnames.

Since you have Id's, there must/can be duplicate first names and/or surnames.

In C1 in Sheet2 enter:

=INDEX(Sheet1!$C$1:$C$202,SUMPRODUCT(MATCH(A1&"-@-"&B1,Sheet1!$A$1:$A$202&"-@-"&Sheet1!$B$1:$B$202,0)))

Copy this down as far as needed.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-04-23 15:18 ]

[ This Message was edited by: aladin akyurek on 2002-04-23 15:53 ]

[Yogi Anand]

On 2002-04-23 12:55, robt wrote:
Heres more info

sheet 1 contains a list of names with the firstname in A1:A202, the surname in B1:B202
and a unique ten digit personal id number in C1:C202

sheet 2 is identical to the first but there are no numbers listed in column C and only 98 names are listed, but are all present somewhere within the first sheet. I need excel to find the people in the first list and put their id number in column C, sheet 2

i.e find this persons id number from this sheet and place it next to his name in the other

Hi robt:
One of the ways you can do this is by adding temporary column D to sheet1, and add the following formula in cell D2

=A2&B2
and then drag it down to row 202
Now back in sheet2 , add the following formula in cell C2

=INDEX(Sheet1!$A$1:$D$202,MATCH(A2&B2,Sheet1!$D$1:$D$202),3)
and then drag it to the cell corresponding with the last name listed in sheet2

then highlite the cells in column C and then EDIT|COPY ... and then EDIT|PASTE_SPECIAL|VALUES to convert the formulas to static values

Now you may delete the column D that was added in sheet1.

HTH



_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 14:04 ]

[Yogi Anand]

Hi Aladin:
Sorry, I did not see your last post until after I posted mine. Mine requires adding a temporary column in Sheet1, while yours does without any invasive surgery. So yours is abetter solution.

Regards!
Yogi Anand

[Yogi Anand]

Hi Prof Aladin:
I am back again ... this time with a question -- oopse! I mean a set of questions:

Here is the formula that you suggested above:

=INDEX(Sheet1!$C$1:$C$202,SUMPRODUCT(MATCH(A1&"-@-"&B1,Sheet1!$A$1:$A$202&"-@-"&Sheet1!$B$1:$B$202,0)))


And here is my formula that I have modified to alleviate the need for adding a temporary column in sheet1:

'{=INDEX(Sheet1!$A$2:$C$202,(MATCH(A2&B2,Sheet1!$A$2:$A$202&Sheet1!$B$2:$B$202,0)),3)}

Obviously this is an array formula so I have intentionally shown it with the curly brackets.

On examination, it is obvious that the two formulas are substantially identical -- except that with the use of the SUMPRODUCT function, your formula does not need to be entered as an array formula (CTRL+SHIFT+ENTER).

Question 1:
Why the use of the SUMPRODUCT function ... its only argument is a MATCH function, which granted, has its own arguments

Question 2:
The use of the SUMPRODUCT function in this case obviously works ... But Why?, and How?

Question 3:
Under what other circumstances can a SUMPRODUCT function be used where its usage may not be obvious?

Thanks ... awaiting your insightful explanation

Regards!

Yogi Anand