Results 1 to 12 of 12

Thread: Vlookup issue

  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Vlookup issue

    I'm trying to take values from tab "SCHED A" COLUMN C and have them recorded in tab "SCHED X" COLUMN F.

    To do this I concatenated the last name and first name in tab "SCHED X" "COLUMN D" so as to match the name in tab "SCHED A".

    Not one item matched even though I used the TRIM function.

    Anyone have any answers.

    Thanks
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    MNN,

    Using Index/Match, place this formula in G2 then copy down.

    Code:
    =INDEX('SCHED A'!$A$2:$C$137,MATCH(TRIM(D2),'SCHED A'!$B$2:$B$137,0),3)
    Many of your names do not match because of trailing spaces (resolved by trim) and missing spaces within the names.

    Example:
    "BRAND, GERARD A<space><space><space><space><space><space>" (Sched X)
    "BRAND,GERARD A" (Sched A)

    HTH,
    Maud

    MNN.png
    Last edited by Maudibe; 2016-09-07 at 00:03.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you Maud.

    I do not use the INDEX MATCH function much. I did try the TRIM FUNCTION with the VLOOKUP function but the response only gave me a #N/A and not the respective unit. Why does the TRIM FUNCTION WORK with INDEX MATCH and NOT with the VLOOKUP?

    What does HTH mean in you closing?

    Thank you again.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud,

    As I was reviewing the formula with the INDEX MATCH functions, I noted that we still received error messaged #N/A on SCHED X however SCHED A did in fact have the match name and a related value.

    What happened to respond an error and not the value? and why only on some and not others?

    Thanks.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    MNN,

    Look closely at the mismatches that produce #N/A. You will notice that spaces are missing after the comma between the last and fist names. As you correct them on Schedule A the formula will return the value instead of an error.


    Applying the trim function in VLOOKUP makes your formula work
    Code:
    =VLOOKUP(TRIM(D2),'SCHED A'!$B$2:$C$137,2,FALSE)
    HTH = "Hope that helps

    HTH,
    Maud

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Maud

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I also took note that the some names in the Sched A did not have a space after the comma.

    is there a formula that could be developed to test if there is a space after the comma then go to the next name and if there is no space, place a space there and then move on to the next name in the next row.

    Thanks,

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    MNN,

    You can't write a formula for cells in one column that change the values in another. However, this VBA code can easily do that

    In a standard module:
    Code:
    Public Sub FixNames()
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For I = 2 To LastRow
        If InStr(1, Cells(I, 2), ", ", vbTextCompare) = 0 Then
            Cells(I, 2) = Replace(Cells(I, 2), ",", ", ", 1, 1, vbTextCompare)
        End If
    Next I
    End Sub
    HTH,
    Maud

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    MNN

    If you are looking for just a formula to test which names are missing the space after the comma then place the following formula in D2 on the SCHED A sheet and copy down.

    Code:
    Cell D2 =IFERROR(IF(FIND(", ",B2,1)>0, ""),"<space> not present")
    MNN1.png

  10. #10
    New Lounger
    Join Date
    Mar 2012
    Location
    California
    Posts
    7
    Thanks
    0
    Thanked 2 Times in 2 Posts
    You can also fix the name column with a couple of search/replace actions. First select the name column and replace all commas with ", ". This will put a space after all commas, but will also insert an extra space where the name was properly entered to begin with. This is fixed with a second replace action, where you replace all double spaces with single spaces.

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi MNN

    When dealing with Employees and cross-referencing between sheets, it is essential that you correctly identify the same person. The best way of doing this, in my opinion, is to use a unique Employee Staff Number rather then rely on 'names'.

    For example, if Anne Smith marries and becomes Anne Jones, her staff ID will stay the same.
    Misspelling of names between sheets is just one issue that can be avoided by using unique staff IDs. And different 'name versions' like JOHNY B. GOOD and JOHN GOOD and JOHNIE B GOODE and JONNY B. GOOD and DR. J. B. GOOD won't matter if you use a staff ID.

    The fixes suggested by abilyeu are good. This can also be done via formula.

    In my attached version, on sheet [SCHED A] I added column [D] to remove the commas, strip extra spaces. This will become the primary fullname lookup column, to cross-reference against column [D] on sheet [SCHED X]. On sheet [SCHED A] I also added a lastname column [F], which can be used as a secondary lookup column against column [B] on sheet [SCHED X]

    On sheet [SCHED X], I changed the formula in column [D] to create the fullname lookup.
    The formula in column [F] on sheet [SCHED X] uses the MATCH function and INDEX functions:
    Code:
    =IFERROR(INDEX('SCHED A'!C:C,IFERROR(MATCH(D2,'SCHED A'!D:D,0),MATCH(TRIM(B2),'SCHED A'!F:F,0))),0)
    Code:
    MATCH(D2,'SCHED A'!D:D,0)
    This MATCH function returns the [SCHED A] row number if an exact match of the fullname is found in column [D]
    This will work for the majority of the entries. However, for cases like
    ELINZANO-MANALANG ANNABELLE (on [SCHED X] sheet)
    ELINZANO-MANALANG ANNAB (on [SCHED A] sheet)
    ..we will just use the secondary lookup of lastname to get the row number:
    Code:
    MATCH(TRIM(B2),'SCHED A'!F:F,0)
    If the row number for primary lookup fullname, or secondary lookup lastname, isn't found, then the IFERROR will give a zero Amount.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-09-17 at 12:56.

  12. #12
    New Lounger
    Join Date
    Mar 2012
    Location
    California
    Posts
    7
    Thanks
    0
    Thanked 2 Times in 2 Posts
    I would just like to emphasize what zeddy said about the importance of identifying the same person across multiple data sources. It becomes especially critical if you are dealing with very large lists of people. In my case, I often have to deal with lists of up to 3000 job applicants, many of whom aren't in my own database to begin with. My own database (in MS Access) has a unique numeric ID for everyone, but it's largely useless when first working with these lists. The only numeric identifier that people generally put on their job applications is a Social Security #, which only HR is allowed to see and thus does not appear on the reports I get. To complicate things even further, there are many people with similar or even identical names and you have to find some way to distinguish them. I even run into cases where people misspell their own names on applications, or file multiple applications under variations of their name to get around certain limitations in the hiring system.

    If you're in a situation like me where you're comparing lists of names from external sources with a list of names that you maintain yourself, it is essential that your own data be as complete as possible. Ideally you will have an ID number for each person, as well as first name, last name, middle name(s), generational suffixes (i.e. Jr., Sr., II, III, IV, etc.), and anything else you can use for name matching. That way when a new list comes in you can use various techniques, like those described in the posts above, to get the names into a uniform format that you can then run lookups on.

    I myself maintain a collection of UDFs in a personal macro workbook that I use for manipulating names, such as removing extraneous spaces, switching between "lastname, firstname MI" and "firstname MI Lastname" formats, splitting names into separate columns (firstname, lastname, etc.), proper-case capitalization, and more. VBA can be slower than formulas, especially in very long lists, but it makes it easier to build in the handling of special cases like hyphenated names, generational suffixes with and without periods, multi-word last names (like "de Soto", "Van der Waal"), and so on. Even with all that, however, I still have to do a lot of manual checking to make sure that any mismatches are actually new people and not just due to typos, and that matches are really the same people and not different people with similar names. This sometimes requires finding and reading through the applications, which is very time consuming, but when you're doing any kind of work that can affect people's jobs, you have to be as thorough and accurate as possible.

    Lastly, I should mention that if you're working with things like personnel data, security is an important consideration. I recommend using VeraCrypt, but there are other popular options as well. But that's a subject for another thread.

Posting Permissions

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