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

1. ## 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

2. ## 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.

3. ## 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

4. ## 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.``

5. ## 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. ## 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. ## 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. ## 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. ## 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

10. ## 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. ## 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. ## 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.``

13. ## 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. ## Re: Match staff name and date then import data from matched row (Example attached)

You're very welcome

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

#### 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