VBA / macro to match multiple Criteria on two sheets then copy and paste cell value

[CliffWeb]

I need a macro to match/find Rep list and ID on Sheet 1 with Sheet 2 then copy and paste Skill Level from sheet 2 under the correct Skill on Sheet 1. I have a list of over 300 Reps and about 20 different skills and each skills has a levels ranging from 1-8. Below is a quick a example of how sheet 1 should look after comparing the list matching and copying the value from the Data to sheet 1. Sorry I don't know how to add the grid lines but Rep name is A3 and so on.

Sheet 1
Rep Name	Rep ID	1510	1511	1512	1516	1517	1688
Rep 1	Rep ID 1				6	1	2
Rep 2	Rep ID 2	4	3	5
Rep 3	Rep ID 3	5			3		2
Rep 4	Rep ID 4	3		4	1
Rep 5	Rep ID 5		3			1	1
Sheet 2 Data Sheet
Rep Name	Rep ID	Skill01	Skill01 Lvl	Skill02	Skill02 Lvl	Skill03	Skill03 Lvl
Rep 1	Rep ID 1	1517	1	1688	2	1516	6
Rep 2	Rep ID 2	1510	4	1511	3	1512	5
Rep 3	Rep ID 3	1688	2	1516	3	1510	5
Rep 4	Rep ID 4	1516	1	1510	3	1512	4
Rep 5	Rep ID 5	1511	3	1517	1	1688	1

<tbody>
</tbody>

 

[CliffWeb]

I will have a look if you can provide a new set of data (file) that shows a complete example of the variety of these clockings with expected results. !!!

would it be better to rewrite to code to display the information as it is in the image below? If it is, then I would prefer this. The goals is to eliminate the steps it takes to view the break # and time, prefer to view both on one page. Highlight time below 15mins and above 30 mins using Red font. here is a link to the file https://app.box.com/s/mgyqhghtmtne7qsmqblabhp5ryrjgtjq and link to the image if needed. https://app.box.com/s/bh28t69swyynn03r6t5z7ym4p7s9lxan

 

[MickG]

The previous code is of no use as the differentiating of days can only be done by determining the time lapse between breaks, (not Dates) assuming the Break are , say over 3 or 4 hrs then that space can represent the next day.
Please try the new attached File !!

https://app.box.com/s/fp6a7jx8kfusgx1z8d3snrhzw773fxdk

 

[CliffWeb]

The previous code is of no use as the differentiating of days can only be done by determining the time lapse between breaks, (not Dates) assuming the Break are , say over 3 or 4 hrs then that space can represent the next day.
Please try the new attached File !!

https://app.box.com/s/fp6a7jx8kfusgx1z8d3snrhzw773fxdk

Some reps work 8 or 10 hrs daily the max is 16hrs. I guess I posted the above a little late before you this code. I was hoping to use the newer layout in the link above your comment to minimize the effort to see the actual time of the break. 2nd. I was hoping this would be all vba to hide the formulas. I have a strong feelings people with dibble dabble in the formula and mess it up. Thanks again.

Here are the links the 2nd one is a photo of the ideal layout tablehttps://app.box.com/s/mgyqhghtmtne7qsmqblabhp5ryrjgtjq and link to the image if needed. https://app.box.com/s/bh28t69swyynn03r6t5z7ym4p7s9lxan

 

[MickG]

Here's an update based on your latest post,but one (I think your latest looks much the same as the previous)
The formulas are only in the "Break columns", and are placed there by the code for use in Code for the "Time Breaks" Header box. You just need to run the code again and the formulas will be replaced.
For Update of the Breaks "Break down" at top of Sheet , just Click on a break time from the "breaks Columns.

https://app.box.com/s/iztp2m605ka43fpuh6ucszqrebhpa64h

 

[CliffWeb]

Here's an update based on your latest post,but one (I think your latest looks much the same as the previous)
The formulas are only in the "Break columns", and are placed there by the code for use in Code for the "Time Breaks" Header box. You just need to run the code again and the formulas will be replaced.
For Update of the Breaks "Break down" at top of Sheet , just Click on a break time from the "breaks Columns.

https://app.box.com/s/iztp2m605ka43fpuh6ucszqrebhpa64h

Yes I like this. Will test it on Monday. Thanks plenty. Sorry for the headache if I caused any.

 

[CliffWeb]

Yes I like this. Will test it on Monday. Thanks plenty. Sorry for the headache if I caused any.

The code works, I think I just have to review the data more closely. Some Reps are showing 75647 mins for break times, some are showing 0 in the table but display the actual time and length of the break above the table on the first sheet.