CODEWITHSUNDEEP
exceldatecomparematch
manolish
manolish

Reputation: 47

excel match rows with same date and time

I have 2 large excel sheets that contain the following columns:

Date Time Value

Most dates are common for both sheets but sometimes there are extra dates in one sheet than the other making the row count not being the same.

To make it more visual I have small parts of the 2 sheets. You can see that time 22:00 is missing from second sheet

Sheet 1

Date    Time    Value
2013.02.26  20:00   1714
2013.02.26  21:00   3121
2013.02.26  22:00    3485
2013.02.26  23:00   4501
2013.02.27  00:00   4407
2013.02.27  01:00   3359
2013.02.27  02:00   4455

Sheet2

Date    Open    Value
2013.02.26  20:00   3106
2013.02.26  21:00   7330
2013.02.26  23:00   7297
2013.02.27  00:00   6469
2013.02.27  01:00   4833
2013.02.27  02:00   6841

I would like somehow to compare the Values for the same date and time from both sheets.

Does anyone know how to handle this?

Upvotes: 0

Views: 1207

Answers (1)

user4039065
user4039065

Reputation:

Since the combinations of date and time appear unique to each worksheet, a conditional sum of the value based on one date and one time should produce a single value. The SUMIFS function can perform a conditional sum.

In sheet2!D2 as,

=sumifs(sheet1!C:C, sheet1!A:A, A2, sheet1!B:B, B2)

Depending on what you mean by 'compare the values', simple subtraction can determine the difference. A returned value of zero would indicate that there is no corresponding value for any particular date and time combination.

Upvotes: 1

Related Questions