Macro to copy a number to other sheet based on two conditions

[PSV86]

<tbody></tbody>

Hello there!

I’m just breaking my brains to do this and I ask your help!

I made it without a problem using match and index but is really slow and I want to try it with a macro.

Two sheets, one called Data, and other called Report.

Data has 3 columns, A contains a date, B contains a name, and C (that’s the important one, contains a number) and it has a lot of rows with information.

Reports contains 9 columns
A is equal a different names
B to I are iqual to dates

So I have something like that:

Data:

12/12/17	Name 1	8
12/13/17	Name 1	6
12/15/17	Name 2	8
12/15/17	Name 7	5

<tbody>
</tbody>

Report:

Name	12/13/17	12/14/17	12/15/17	12/16/17	12/17/17	12/18/17	12/19/17	12/20/17
Name 1
Name 2
Name 3
Name 4
Name 5
Name 6
Name 7
Name 8
Name 9

<tbody>
</tbody>

So what I need?
If the date and the name in the Data sheet, is the same as the date and the name in Report, then copy the number.

Using the example posted the result it will be:

Name	12/13/17	12/14/17	12/15/17	12/16/17	12/17/17	12/18/17	12/19/17	12/20/17
Name 1	6
Name 2			8
Name 3
Name 4
Name 5
Name 6
Name 7			5
Name 8
Name 9

<tbody>
</tbody>

The formula in the sheet Report in B2 (and so on) used to achieve this results is:
{=IFNA(INDEX(Data!$A:$C,MATCH(1,(Data!$B:$B=$A2)*(Data!$A:$A=B$1),0),3),0)}

I hope you can help me and learn how to do it in the process examining the reply’s!

Thanks a lot!!

 

[MickG]

Try changing the last bit of code for the bit of code below:-

For Each Dn In nRng
   If Dic.exists(Dn.Value) Then
      If Dic(Dn.Value).exists(Dn.Offset(, -1).Value) Then
         Q = Dic(Dn.Value).Item(Dn.Offset(, -1).Value)
         Q(0).Offset(, Q(1)).Value = Dn.Offset(, 1).Value
      End If
   End If
Next Dn

 

[PSV86]

I don’t know what to say... it works shockingly well
I tried with a future date, with an old one, writing a letter instead of a number, it simply works!

Thanks a lot for everything, as I said now is time to study all this references that I don’t understand in the code and still learning about Excel VBA

I hope I can help someone as you helped me!

 

[MickG]

You're welcome
Regrds Mick