Summarizing Data From A Year Period - Multiple Columns With Same Name and Different Dates

[Rex32]

Hey Guys,

First I must say that you guys were INCREDIBLY helpful last time I reached out on the forums and I have since purchased the Mr. Excel book collection and have begun to work through it but I am in a bind at work and was hoping for some help.

So I have a file of 2,166 columns that list 183 unique names, in column A, Invoice Date in column B, Hours in column C, Dollars in column D, and Invoice number (just as a random check) that I need to sort through. Ultimately I need the data to list all the unique names in column A, total for the year in column b, column c - ba are labelled 1/9/13 through 12/25/13 populated with the appropriate data.

I tried using an amazing VBA script written by another member for a bill of materials question but keep getting data mismatch errors.


I'm not entirely sure why just the sum isn't okay, I would just use a SumIF - but my boss wants it by weekly period.

I have a source file with more information in it that I can provide if need be, I just need to remove some identifying information first.

Any help would be greatly appreciated, I have spent my whole day on this and am at the bang my head against the wall stage and really just need some help. I can't seem to find a way to attach the file, but I will gladly email it to anyone and I have uploaded it here (I just Googled 'File Hosting' if there is a better way I can redo it)

filehosting.org | Download | TempHoursMrExcel.xlsx

The macro in there is the old one, just left it so you can see it.

Thank you so much guys, if anyone is located in MPLS, MN - BEERS ON ME!


Thanks,

Rex

 

[Chris Mack]

I haven't looked at the file you uploaded.

Based on what you've said, I would say, could you put the unique names in column A manually, and then just use SUMIF formulas, or SUMPRODUCT formulas.

For example, for cell B2:

=SUMIF(OriginalSheet!A:A,A2,OriginalSheet!D:D)

And then for C2:

=SUMPRODUCT((OriginalSheet!A:A=$A2)*(OriginalSheet!B:B=C$1)*D:D)

And then copied across to the rest of the date columns.

Hope this helps,

Chris.