[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!!
<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!!