Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Comparing Data - please help!

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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?

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •