Macro to consolidate entries from days to weeks

[Seraph122]

Hi All,

I'm looking for a way to consolidate entries in the "Import" tab which have data at the daily level, into weekly level entries (basically summing the data if it has the same Number, Day and Foods). I have a Calendar worksheet that shows what the new week entry should be. After it consolidates the entries, I need it to adjust those numbers using the Adjustment table, so all "Tomato" values are increased by 3.2% (e.g final value would then be 1.032 times its original value).

Can someone assist me with this? I've attached example data.

Thank you greatly!

[rcm]

I adjusted your file a bit.

In calendar it automatically assigns the first day of the week in column B

It also populates the output tab when you run the macro included

I did not adjusted since it was not clear where to place the result (aside to the right or in the same cell)

Cheers,

[Seraph122]

Hi rcm,

Thanks for the help! Is there any way to do this and delete the helper column after? (VBA newbie here)

It looks good though, thanks again!

[jindon]

The result in attached slightly differ from your result, see figure in red.

[Seraph122]

Hi Jindon,

You're absolutely correct the second tomato entry was off, thanks for catching that!

Also, I think your macro is excellent and solves my issue - if you have time would you mind adding comments into the code so I can better understand what's going on? I'm still extremely new to VBA so I'm not sure what each line of code means.

Thanks for your assistance!

[Seraph122]

Hi Jindon,

I noticed 2 things that I could use some assistance with:

1) The code doesn't seem to work when I use non-2013 dates

2) The values don't appear if the Food and Food2 values don't match for an entry. They aren't necessarily equal, because the difference is consistent for each food (e.g. food will be Tomato and food 2 would be Tomato_2). The adjustment look-up could use either one, but preferably it would match Food.

Can you help me with these points?

Thanks!

[jindon]

My code is not count Food2 in consideration.

Can you explain about Food and Food2 for the calculation?

[Seraph122]

Hi Jindon,

When I changed the values under Food and Food2, the values in the Value column don't appear after I run the macro. Even when one of the values is the same as the adjustment table, it doesn't seem to show when I run the macro.

Which column does the code read to adjust the value in the export?

Also, can it be adjusted to work with non-2013 dates?

Thanks!

[jindon]

Please Login or Register  to view this content.

Should take care of different year.

I just don't understand the relation between Food & Food2 for calculation.
In other words, don't understand the reason for FOOD2 at all.

If you upload the file with different situation with the result, it might help me to understand.

[Seraph122]

Hi Jindon,

Thanks for your help so far!

Food 1 and Food 2 simply correspond - for example every time Food 1 is Tomato, then Food 2 is Ketchup. Every time it is Cheese, then Food 2 is Fondue. If you can make Food 1 column the one that corresponds to the Adjustment table, that would be great. I updated the data to show what I'm talking about. Food 2 just needs to be part of the export data, it doesn't have to touch any of the calculations.

Also, could you help me tweak it so that it adds to the "Output" worksheet? I'm super new to VBA so it's still kinda confusing. I'm trying my best to walk through your macro but it seems too advanced for me at the moment .

Thanks!

[jindon]

Change 3 to 2

Please Login or Register  to view this content.

[Seraph122]

Hi Jindon,

That worked perfectly!

Can you tell me how to make the macro put the output results on a new worksheet called "Output"?

Thanks!

[jindon]

Try this

Please Login or Register  to view this content.

[Seraph122]

Jindon,

This works great!! I really can't thank you enough!!

[jindon]

Re your PM

If you step through the code, you will see variable "txt".

temp = Join$(Array(WorksheetFunction.WeekNum(a(i, 2)), a(i, 3), Year(a(i, 2))), Chr(2))

a(i,2) = Col.2, holds the date.
a(i,3) = description
Year(a(i,2)) = Year of the date in co.B
WeekNum Excel function returns Week number of the date of its year.

So concatenating these data in a string makes Unique key for the output.

How to step through the code.
While you are in VBE;
1) click on somewhere on the code.
2) as you hit F8, the code will execute line by line.
3) if the line past that line, place the cursor on the variable "txt" and you will see the contents.

[Seraph122]

Excellent - thanks Jindon!