VBA Conditional copy/paste based on date condition

[pochitodiaz]

All,

I have been trying to get a script to work, but I have not been able to do it, i don't even know if it is possible, hope anyone can help me. I have a report of about 17k rows. Column C on the "Calls" sheet includes date and time formatted as mm:dd:yyyy hh:mm, I am trying to copy the values of column H to a new sheet "other" based on whether the value on C is greater or equal to a date/time but lower than another. The value should be copy to the new sheet to the cell next a cell that matches the same criteria. Let me see if I can explain it clearer with numbers.

Column C has 1st row 01/01/2015 9:00 2nd row 01/01/2015 10:00. Column H has a value of 1st row 1, 2nd row 3. On the "Other" sheet I have on column A the entire day in increments of 30 minutes so 01/01/2015 0:00. 01/01/2015 0:30, 01/01/2015 1:00 and so on. The idea is for the conditional copy to look the values of column C on the "Calls" sheet, and copy the value from column H to the appropriate cell on sheet "Other" column B based on what 30 minute interval it matches.

I would really appreciate any help anyone can provide. Please let me know if I wasn't clear enough

Thank you

 

[onlyadrafter]

Hello,

Depending on how your data is entered on both sheets

does

=INDEX(Calls!$H$1:$H$100,MATCH(A1,Calls!$C$1:$C$100,0))

work?

 

[pochitodiaz]

Onlyadrafter,

Thank you for your reply, but that didn't seem to do the trick, although let me check if I understand it. The formula should be enter in the B column of the "Other" sheet right? What would you need to know about how the data is entered on the sheets to make it clearer? Thank you for all your help

 

[onlyadrafter]

Hello,

yes, the formula needs to go into the 'Other' sheet.

Regarding the data entry method. If I enter all the dates/times manually I get matches. If on the other sheet I start with the first date and time then use a formula to get 30 min intervals, then I don't get any matches.

Having had a look around, found a solution from Aladin. Try:

=INDEX(Calls!$H$1:$H$100,MATCH(TEXT(D1,"dd/mm/yyyy hh:mm"),TEXT(Calls!$C$1:$C$100,"dd/mm/yyyy hh:mm"),0))

but you need to enter with Ctrl+Shift+Enter. This gives me results if the 30 min intervals are formula.

 

[pochitodiaz]

Hi,

Thank you for your assistance onlyadrafter and sorry for the delay. I couldn't get it to work, but my boss ended up using a different kind of software to get the report she wanted, so now it become a moot point I really don't get why it kept given me just the #Value! on the cell, it is like it doesn't see it from the other sheet, but oh well. I really appreciate all your help.

Thank you