Macro Help - Copy paste based on Date

[Steves73]

Hi All

Need some help, apologies if the below a is a bit confusing

So, Need a macro that looks in column B, C, D. Matches the date on the rows 76, 81, 86. then copies the corresponding "Unit No" in Column A to the matched cell in rows 76, 78,86 (and so on) - Calendar will be for a year but got example sake I'm only showing March, April, May

Also, macro will then add to row 79, 84 89 and so on under "Unit No" the corresponding data in either B1,C1,D1,E1

Please note, information in Rows B,C,D and E and populated by Xlookup so automatic

More than happy to reformat to make it easier for the Macro

Any Help is much appreciated

 

[Herakles]

The H$2 in B42 needs to be B40 (the date).

The H$2 in B43 needs to be B40 (the date).

Just drag the formula across.

 

[Herakles]

The ranges look right.

 

[Steves73]

Nearly there, just need to get rid of the #NA

 

[Herakles]

Just copy B74 and B75 along to the right.

 

[Steves73]

Yep, done that, tried dragging the formula from B74 to V74 and also copy and pasting, still same. Should we have a iserror then ""? Or am I doing something wrong?

 

[Herakles]

Yes, that is my fault in using dates that always match. Apologies.

Try this by surrounding the formula in a count of cells in names that contain the date.

=IF(COUNTIF(dates,H2)>0,INDEX(unitno,MATCH(1,MMULT(--(dates=H$2),TRANSPOSE(COLUMN(dates)^0)),0)),"")

 

[Steves73]

That works perfectly
Can i have the same for the "Service Type"

 

[Herakles]

=IF(COUNTIF(dates,H2)>0,OFFSET($A$1,0,MATCH(H$2,OFFSET($A$1:$E$1,MATCH(1,MMULT(--(dates=H$2),TRANSPOSE(COLUMN(dates)^0)),0),0),0)-1),"")

All I'm doing is wrapping the existing formula in :

IF(COUNTIF(dates,H2)>0, ******* , "")

 

[Steves73]

You have done extremely well, all working perfectly, Thank you so much. Really do appreciate you help