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

PSV86

New Member
Joined
Dec 15, 2017
Messages
8
[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]12/12/17[/TD]
[TD]Name 1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12/13/17[/TD]
[TD]Name 1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]12/15/17[/TD]
[TD]Name 2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12/15/17[/TD]
[TD]Name 7[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Report:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]12/13/17[/TD]
[TD]12/14/17[/TD]
[TD]12/15/17[/TD]
[TD]12/16/17[/TD]
[TD]12/17/17[/TD]
[TD]12/18/17[/TD]
[TD]12/19/17[/TD]
[TD]12/20/17[/TD]
[/TR]
[TR]
[TD]Name 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]12/13/17[/TD]
[TD]12/14/17[/TD]
[TD]12/15/17[/TD]
[TD]12/16/17[/TD]
[TD]12/17/17[/TD]
[TD]12/18/17[/TD]
[TD]12/19/17[/TD]
[TD]12/20/17[/TD]
[/TR]
[TR]
[TD]Name 1
[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 7[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!!
 
Try changing the last bit of code for the bit of code below:-
Code:
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
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top