Compare two date fields on different sheets and carry out an action.

[webwyzard]

Hi

I am trying to figure out a method of looking at two columns in different sheets and comparing the dates and then carrying out a copying action to populate a column on the RH empty column.

The problem is the first sheet has a single datetime format and the second sheet has a separate date and time column.

If there is a matching date from the first sheet (AUSUSD) in the second sheet (Announce) then i would want to copy the relative cells in columns B to E over to the first sheet into columns R to U.

The attached workbook has cells highlighted and some sample data.

Any advice on how to do this appreciated.

Regards

Neil

[Pete_UK]

Please explain how the row that you have highlighted in the Announce sheet (on 16th April 2018 @ 08:30) ties in with the row that you have copied it to the AUSUSD sheet (which occurred on 16/03/2018 @ 13:25).

Pete

[webwyzard]

Pete

My bad, a copy and paste to the wrong month. Date dyslexia abounds sorry. New upload with correction available now.

regards

Neil

[Pete_UK]

I don't see an easy way of doing this, as your date/times in the AUSUSD sheet are in a different format than in the other sheet, and the times are not the same. There are some duplicate dates in the AUSUSD sheet (e.g. 20/03/2018), and many duplicates in the Announce sheet, so I think it will be difficult to match up the correct records.

Maybe someone else can see a way forward.

Pete

[webwyzard]

Pete,

Perhaps i need to first insert columns in each sheet and convert the times to similar formats?

If there are more than one identical date we can just copy to the first occurence or to every occurrence and remove duplicates later.

Not sure if that helps?

Neil

[Pete_UK]

It's easy to convert the dates - I put this in W3 of the AUSUSD sheet:

=DATE(LEFT(I3,4),MID(I3,6,2),MID(I3,9,2))

then copied down. BUT, if we then have a formula in R3 (say), trying to look for a matching date, then the first few rows won't have a match until we get to row 19 (4th April), then there is nothing for the next row. For the 6th April, however, there are four possible matches in the Announce sheet - which is the one that applies? When we get down to 11th April, there are five matching records on the Announce sheet.

There is not a simple one-to-one relationship using only the dates. Is there some other field that could be used to distinguish the records?

Pete

[webwyzard]

I did a long reply to your answer but for some reason it did not send properly???
Hey ho. I should have looked at the data a bit more closely as you say (i do database design so should have twigged it).
What we are trying to do here is see if the trading of a certain currency pair is affected by the various high level announcements thoughout the year. So we have data on theoretical trading split into the 5 days of the week. Some trades make money and some do not. We want to see if there is a correlation between the announcements and profit/losses over time.
So my idea was to automatically stick an announcement next to a trading day and make a note of what happend. Perhaps even write some simple code to add up the matching trades and give a sum over time.
As it is i think i will need to use some VBA to do it.

My thoughts are along these lines.
Loop through all the data rows and look for an announcement row matching that date (compare the two dates after using your conversion etc.). If it finds one then increment a variable and look for additional date matches in the announcement sheet. Then insert rows in the data sheet (count -1) and copy the announcements into the blank rows. So each data row with an announcement will have as many announcements as needed.

I think i can loop through the rows ok. On each loop of the data rows i need to loop through all the announcements until i get a match?
Then store that row number and find the next match and so on until it does not match and subtract 1 from the total matches. Then copy the range in the announcements from first to last match and paste into the Data row number (after first inserting the right number of blank rows).

Might that work?

[Pete_UK]

You can save yourself the first few steps by putting this formula in X3 of the AUSUSD sheet (assuming you also have the date conversion formula in W3 that I gave you yesterday):

=COUNTIF(Announce!B:B,W3)

copy this down, and it will give you a count of the number of times each date occurs in the other sheet. If the value is 0 there is no matching date. If it is 1 (like 4th April), there is no need to insert an extra row. The number of extra rows you need to insert for any particular date is obviously the number -1.

The other steps you outline seem fine, though I'm not sure what you will do after that.

I have an early start tomorrow and I'll be out most of the day, and then the next day I'm off to Spain for 2 weeks (an even earlier start), so I don't think I'll be able to keep with you on this topic.

Good luck with it.

Pete

[webwyzard]

Thanks for that i will give it a go

Have a great two weeks!

[Pete_UK]

I'm visiting my new grand daughter - she's not quite 3 weeks old. Plus, of course, her parents.

Pete