Comparing Data - please help!

robt

New Member
Joined
Apr 22, 2002
Messages
8
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top