CODEWITHSUNDEEP
vbaexcelexcel-2007
xena.t
xena.t

Reputation: 13

Excel Macro compare two columns in two sheets and insert row values

I'm getting a little desperate here. I work in an energy company and I'm trying to insert daily energy consumption values into quarter hourly values. I tried doing so without VBA through vlookup function but it didn't work. Then I tried the very primitive solution of "copy/paste" but after spending 2 hours on less than 2 months I found out it's the most stupid thing to do.

So, here's the issue:

sheet 1 has my daily consumption values for a whole year, each row represents a day (column A is day, B is month, C is the value), total number of rows is 267

Sheet 2 has my quarter hourly values which means 4*24 values a day for a whole year (column A is day, B is month, C is time in hours and minutes and D is where I want to insert the daily values), total number of rows is 35137

I want to compare the date values in both sheets (i.e. columns A and B) and when as long as they are the same, it needs to copy its value from column C in sheet 1 to column D in sheet 2.

I hope I made it clear enough. I would be unimaginably thankful for any help!

Upvotes: 0

Views: 1220

Answers (2)

Bogey
Bogey

Reputation: 5754

OK, not sure I understood you correctly, but if I did -

Dates are stored as double values by Excel. An integer represents the start of the day (exactly midnight, so 0:00:00 on a given day). Everything after the period describes the hour of the day. For example, 41,876.628 would be the 28th August 2014, 3:03 pm. 41,876.000 would be the 28th August 2014 at the very start of the day (0:00:00 o'clock).

If you want to compare if two dates have the same year / month / day, but ignore the hour/minutes/seconds, all you need to do is to round down(!) the dates to the nearest integer.

So on your sheet 2, create a field containing the specific date of your entry, e.g. August 25 2014, 3:15 pm (it needs to be in a date format, not a string). Now if you use the INT(...) function, where "..." is a reference to the cell containing a date, you'll get a new date - which corresponds exactly to the beginning of August 25 2014, so at 0:00:00 o'clock.

If you do the same for your dates on sheet 1, you'll get comparable date values, again for the start of each day on this sheet.

Now you should easily be able to do a VLOOKUP from sheet2 to sheet1 to the newly created columns (those containing the INT(...date...) functions), as those values will now be easily comparable.

Upvotes: 1

John Bustos
John Bustos

Reputation: 19574

Assuming I understand your question correctly and all you want is the value repeated for every cell in Col D of Sheet 2 where the day and month match the day and month in Sheet 1, the quickest (of many solutions) I can think of that could accomplish this would be to rely on the fact that you know that the daily value only exists 1 time in Sheet 1 and, so you could use the SumIfs() function as follows:

Assuming your data starts in row 2 and row 1 is headers, in cell D2 of Sheet 2, put in the following formula:

=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,B2)

and drag it down for the whole of Col D

In essence, you're saying:

Give me the sum of all values from Sheet 1 Col C where Col A matches my A2 AND Col B matches my B2.

Now, since you only have the day / month combo only once in Sheet 1, this would give you that value.

Hope that makes sense and does the trick!

Upvotes: 1

Related Questions