Shrinking daily data set into weekly

[jonkul]

Hello!

I have a 200 000 row data set with daily data that I want to shrink down to a more manageable data set with weekly data.

It looks a little like this:

Date............Client.....id..Week...Orders
2011-07-17.....se.....123.....28.....1
2011-07-17.....se.....123.....28.....2
2011-07-18.....se.....123.....29.....5
2011-07-18.....se.....123.....29.....3

There are 4 different clients, and 300 different ids. The report will contain new ids every now and then, so I can't create anything too static.

I want the end result to be a something like this instead:

Client.....id..Week...Orders
...se.....123.....28.....3
...se.....123.....29.....8

A new and slimmer table where the 7 daily order values have been summed up into a weekly one.

Do you guys have any good ideas for that, or a link to a good thread?

Thanks,
Jon

 

[Comfy]

I would copy the 200000 rows to a new sheet and remove all duplicates by Client and id.

I would then loop through this unique list to sum the necessary Order values.

Just finishing work so can post some code when I get home if no one else does.

 

[Greg Truby]

They're called Pivot Tables. They'll do just what you describe. Deb's got tons of info on them at her website www.contextures.com in her Excel Tips section.

But be warned. If you have any affinity for data crunching, pivot tables are like crack. One hit and you can become a junkie. You've been warned.

 

[Comfy]

They're called Pivot Tables. They'll do just what you describe. Deb's got tons of info on them at her website www.contextures.com in her Excel Tips section.

But be warned. If you have any affinity for data crunching, pivot tables are like crack. One hit and you can become a junkie. You've been warned.

or that
Something I often forget about. But yes they can be awesome

 

[jonkul]

Comfy: That gives me a list with unique entries that I can use for SUMIF/SUMPRODUCT. Is it something like that you have in mind? My next problem will be what to do when someone adds a new id into the system.


Greg: I have played around a little with pivot tables, but not enough. I am already using a pivot table for the big data set, filtered at a weekly level.
The downsides are:
- When I add value fields for Year on Year & Week on Week comparisons (% Difference From -> previous), they only work if I also show the previous data as well (e.g. I have to expand both 2011 and 2010 to make the YoY work). I would love to _only_ show Week 28 with working YoY and WoW, do you have any tips on how to do that instead?
- My second problem with the pivot table is that because I have to expand several weeks and years, I get a lot of empty old IDs without any fresh data in my new report.

I was trying to solve this by summing the data up on a weekly level, and then adding manual WoW/YoY functions to the table (and pasting values if necessary for performance) so that I can make a better pivot table.

With this in mind, do you have any good advice?

 

[Greg Truby]

Which version of Excel are you using?

 

[jonkul]

2007 at work, but I'll take care of it using 2010 at home if that helps.

 

[Greg Truby]

When I add value fields for Year on Year & Week on Week comparisons (% Difference From -> previous), they only work if I also show the previous data as well (e.g. I have to expand both 2011 and 2010 to make the YoY work). I would love to _only_ show Week 28 with working YoY and WoW, do you have any tips on how to do that instead?

I'd play with the idea of still using a pivot table to crunch the numbers overall. Then create a template report form with your needed label across the side &/or top and then use the GETPIVOTDATA function and said labels to pull in the appropriate values. Then you might still have the pivot with last year's numbers in it too so your formulas can use the GETPIVOTDATA function to calculate <SUP>Y</SUP>/<SUB>Y</SUB> or <SUP>W</SUP>/<SUB>W</SUB>. Conceivably the pivot could even be on a hidden sheet if that makes the presentation form of the workbook cleaner.

BTW, you can have Excel auto-generate GETPIVOTTABLE formulae for you (usually much easier than trying to suss out the correct magic syntax on your own). In XL 2007, on the PivotTable Tools contextual tab Options, in the first group, PivotTable, there is a dropdown button for Options. You have to click the little arrow to open the list and then click the last item Generate GetPivotData.

 

[jonkul]

Thanks! I was kind of hoping that it was possible and that you knew how when you first replied. I'll try it out when I get back to work tomorrow. =)