+ Reply to Thread
Results 1 to 14 of 14

Match staff name and date then import data from matched row (Example attached)

  1. #1
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Match staff name and date then import data from matched row (Example attached)

    Hey,

    I'm wondering if anyone would be able to help me with a macro, it's been bugging me for weeks and manually doing this would take me forever.

    Step 1) Match a name from Column A in AMOStaffList to a name in Column F In MDPData
    Step 2) If a match IS found go to Step 3, if a Match is NOT found go to Step 1 and continue searching.
    Step 3) Check the date on the matched row from Column BW in AMOStaffList to the left 10 characters of the date in Column D in MDPData
    Step 4) If a Match IS found go to Step 5, If a match is NOT found go to step 1 and continue searching.
    Step 5) Using the matched rows change the Value of Column BY and Column BZ in AMOStaffList to the values from Column H and Column I in MDPData

    Note: In this example I have manually filled in the data in Column BY and BZ, normally these cells would be empty. In my main document the AMOStaffList is much larger so is the MDPData file.

    I would much appreciate some help with this.

    Best Regards
    Hyflex
    Attached Files Attached Files

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    Hyflex,

    Does it have to be a macro? What you're looking for can be accomplished with a formula solution. Attached is a modified version of your example workbook.
    In 'AMOStaffList' I left the Manually Entered values alone and put the formula in cell CB9 copied over and down:
    =INDEX(MDPData!H$2:H$20,MATCH(1,INDEX((MDPData!$F$2:$F$20=$A9)*(INT(MDPData!$D$2:$D$20)=$BW9),),0))

    The results are identical.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Match staff name and date then import data from matched row (Example attached)

    Hey,

    Thanks for your help that looks wonderful, I had a hard time getting it going but it works really good,
    I have however found two little bugs which I didn't put in my example

    1) If no match is found it should leave it blank not put #N/A
    2) In some of my staff's names they have things like (N3) at the end of their name I need it to ignore them.

    If you see the remodified example the issue should be fairly obvious.

    Thank you ever so much though tigeravatar, you are a genius :D I've given you some rep
    Attached Files Attached Files

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    Hyflex,

    Thank you for the rep

    As for the formula changes requested, give this a try:
    Please Login or Register  to view this content.

    Slightly simplified version:
    Please Login or Register  to view this content.
    Last edited by tigeravatar; 03-21-2012 at 02:29 PM.

  5. #5
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Match staff name and date then import data from matched row (Example attached)

    Thanks yet again tigeravatar,

    It seems to work in the example but not my main sheet...
    Please Login or Register  to view this content.
    All I've changed is:
    MDPData to [Merged.xlsm]Merged
    $20 to $393653 (last row)

    I just spotted another thing, it's not doing an EXACT match (xlWhole)

    I think for my data (the amount I have) a macro might be far superior.

    Thanks again, I need someone else to help so I can give you more rep :P

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    Hyflex,

    Didn't realize it was almost 400,000 rows O.o
    Give this macro a try, let me know how it goes.
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Match staff name and date then import data from matched row (Example attached)

    Hey tigeravatar,

    You're are truly awesome, It works really good and is actually pretty fast, way faster than how long it would take me xD

    There unfortunately is one bug left and that is if they have a two worded name with a space (Example: "George Leon") it doesn't match their name and doesn't find them thus resulting in no data being pulled/imported.

    Thanks for your help so far, I look forward to your response.

    Regards
    - Hyflex

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    Hyflex,

    In the example where you needed to cut off the non-name stuff, it had paretheses () around the non-name stuff, e.g. Larry (N3)
    Is that always the case?

  9. #9
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Match staff name and date then import data from matched row (Example attached)

    Hey tigeravatar

    Some names could look like:
    1) Larry (N3)
    2) Larry
    3) Larry Smith
    4) Larry Smith (N3)
    5) Larry John Smith
    5) Larry John Smith (N3)

    Potentially even more parts to their name: Larry John Martin Cole Smith (N3)

    I've attached an example where:

    1) Mark Coulhen shouldn't have any data but he's getting some from "Mark"
    2) Mark Denbo should have 11.24 & 8.27 but he's getting data from "Mark"

    I hope this makes sense, thank you for your patience (I added some more rep to you hehe)

    Thanks so much

    Regards
    - Hyflex
    Attached Files Attached Files

  10. #10
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    Update the strName = Evaluate line to the following, and it should work for you:
    Please Login or Register  to view this content.

    The logic being used is that you always only want to cut off the (xxx) after a name (if it exists). So it finds the first "(" and removes it and everything after it. This will allow people with multiple names, like Mark Coulhen, to find their own rows instead of just Mark's row.

  11. #11
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Match staff name and date then import data from matched row (Example attached)

    Thank you so so so so much tigeravatar,

    You've saved me many many man hours of typing in numbers, I have however found I think the last error for some reason some of our staff have apostrophises in their names and it's not finding a match, is it possible to ignore apostrophises?

    So: "Jennifer' Smith" = "Jennifer Smith"
    We have one(two) member(s) of staff, Husband and Wife under the same stats: "Joe 'n' Debby Jones" <-- I changed their surname for confidentiality.

    If it is too hard to do then I'll just remove them all but I would really like to keep them.

    Thanks once again, you've made my day

    Regards
    - Hyflex

  12. #12
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    Adding the Replace method can get rid of apostrophes:
    Please Login or Register  to view this content.
    Last edited by tigeravatar; 03-22-2012 at 02:48 PM.

  13. #13
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Match staff name and date then import data from matched row (Example attached)

    Hey, sorry for the late reply.

    It works perfectly, thank you very very much tigeravatar. You're a star

  14. #14
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match staff name and date then import data from matched row (Example attached)

    You're very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1