Search and paste data from one workbook to another / 2 criteria

[alex_i]

Hi everyone,

I've been pulling my hairs in the last 3 weeks trying to find a solution to my Excel (2013) problem online - unfortunately without any success, so I've finally decided to post it and see if anyone would be willing to help.
Anyway, here is the deal:

I have two spreadsheets:
1. Sheet1 is a report that has 3 columns:
A – Employee N
B – Date (the report is weekly so there is a maximum of 7 different values)
C – Hours worked
Example:

[TABLE="width: 249"]
<tbody>[TR]
[TD]Employee N[/TD]
[TD]Date[/TD]
[TD]Hours worked[/TD]
[/TR]
[TR]
[TD]80015[/TD]
[TD]07/07/2016[/TD]
[TD]7.75[/TD]
[/TR]
[TR]
[TD]80015[/TD]
[TD]08/07/2016[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]80016[/TD]
[TD]05/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80016[/TD]
[TD]06/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80016[/TD]
[TD]08/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80017[/TD]
[TD]03/07/2016[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]80017[/TD]
[TD]05/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80017[/TD]
[TD]06/07/2016[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]80017[/TD]
[TD]08/07/2016[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]80018[/TD]
[TD]03/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80018[/TD]
[TD]04/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80018[/TD]
[TD]05/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80018[/TD]
[TD]06/07/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]80018[/TD]
[TD]08/07/2016[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]



2. Sheet2 has a little bit more info and it looks like that:
[TABLE="width: 641, align: left"]
<tbody>[TR]
[TD]FORNAMES[/TD]
[TD]SURNAME[/TD]
[TD]CLOCK [/TD]
[TD][/TD]
[TD]3-Jul[/TD]
[TD]4-Jul[/TD]
[TD]5-Jul[/TD]
[TD]6-Jul[/TD]
[TD]7-Jul[/TD]
[TD]8-Jul[/TD]
[TD]9-Jul[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]NUMBER[/TD]
[TD][/TD]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[TD]HOURS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]80015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7.75 [/TD]
[TD]7[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]80016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8 [/TD]
[TD]8[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]80017[/TD]
[TD][/TD]
[TD]8.75 [/TD]
[TD][/TD]
[TD]8[/TD]
[TD]8.75[/TD]
[TD][/TD]
[TD]8.75[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]80018[/TD]
[TD][/TD]
[TD]8 [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]











At the moment I am reading the report (sheet1) and manually typing in the hours each worker did in sheet2 on a weekly basis.

If cell FORNAMES is A1, can I make cell E3 to search the whole report (sheet1) and display the value it finds based on two criteria (date and employee/clock N must both match)? Obviously I will extend the formula amongst all cells in the range E3:K6
If there is no such record in the report (sheet1) just leave the relevant cell in sheet2 empty.

Any suggestions?
If more details are required I will be monitoring the thread closely and try to reply asap.

Thanks for your help!

 

[alex_i]

Bump?

 

[bhos123]

is your 3-Jul, 4-Jul ...they are text values or Dates, can you populate them as same format as Col B, 03/07/2016, 04/07/2016 and so on...

 

[alex_i]

Shouldn't be a problem

 

[bhos123]

1> In Sheet1, Insert a column at C, then put C2= A2&"-"&B2 drag down
2> In Sheet2 , assuming you have Clock Number in Col C and 3-Jul in Col D, so put D3=Vlookup($C3&"-"&D$1,Sheet1!C:D,2,0) and drag to right and Down.

 

[alex_i]

Thank you so much! Will be checking if it is working properly on Monday and update the thread. Cheers

 

[alex_i]

Thank you so much! Will be checking if it is working properly on Monday and update the thread. Cheers

Now it is all working. Thanks again! What I had to figure out apart from adjusting the cell coordinates to my actual workbook was to figure out why it is working for the first day (3 - Jul) only. Then I found out that before I drag the formula in sheet2 sideways I had to add $s before C and D as follows:
=VLOOKUP($D8&"-"&F$6,Sheet1!$C:$D,2,0)

Also if anyone is using that solution - expect to meet #N/A in the cells where no data is available, which will have an impact on formulas having that particular cell in their range. In my case it was SUM(range). I had to change it to =SUMIF(RANGE, "<>#N/A") which worked as a charm.

Thanks a lot for the help!

 

[bhos123]

you are welcome!