Pivot Tables Multiple Consolidation Ranges

[sxschech]

In searching for a method to unpivot a series of data, I found out about the Multiple Consolidation Ranges. Problem is that I need to include more data to produce a list without extra copy pasting. The data will be imported to Access.
[tt]
Data
22 23 24 ...
01/05/08 01/10/08 01/15/08 ...
XYZ Group1 5 5 7
XYZ Group2 4 4 6
XYZ Group3 0 0 2
ABC Group1 19 22 23
ABC Group2 19 19 19
ABC Group3 0 0 1



I was able to get it to look like this by Concatenating Col A and Col B into a new Col C:

XYZ GROUP1 01/05/08 5
XYZ GROUP1 01/10/08 5
XYZ GROUP1 01/15/08 7
XYZ GROUP2 01/05/08 4
XYZ GROUP2 01/10/08 4
XYZ GROUP2 01/15/08 6
XYZ GROUP3 01/05/08 0
XYZ GROUP3 01/10/08 0
XYZ GROUP3 01/15/08 2

However, I still need to add row 1 so that result looks like:

Prog Group Wk RunDate Total
--- ------ -- -------- --
XYZ GROUP1 23 01/05/08 5
XYZ GROUP1 24 01/10/08 5
XYZ GROUP1 25 01/15/08 7
XYZ GROUP2 23 01/05/08 4
XYZ GROUP2 24 01/10/08 4
XYZ GROUP2 25 01/15/08 6
XYZ GROUP3 23 01/05/08 0
XYZ GROUP3 24 01/10/08 0
XYZ GROUP3 25 01/15/08 2

Can this be done? Also can it be done without me having to concatenate Col A and B as well (As I have a lot of data to transform)?
[/tt]

 

[SkipVought]

UNPIVOT?

faq68-5287

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[sxschech]

Thanks Skip, sorry didn't mention more explicitly in my post. I did use the information from the FAQ to get the results I currently have.

I found out about the Multiple Consolidation Ranges.

The problem I'm encountering is knowing/understanding how to expand the selection to include the additional row and col. When I tried including them (Row one - Week Numbers and Col A - Program Type), I didn't achieve the results as shown in the final listing of my original post. I can manually add them after the data list is generated, but this would require additional copy and pasting which I hoped to avoid doing.

 

[SkipVought]

You can derive WK from the date. Forget handling WK in the process.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[sxschech]

Hi Skip,

I appreciate your advice, however, I don't know how to derive the weeknos from the date since the weekno are not tied to the calendar date ( 01-Jan-2008 <> Week 1, rather it is week 16), even if I could derive, wouldn't it still require copy and paste to fill in all the rows?

 

[SkipVought]

Your sample data seemed to indicate that your WEEK was 5 calendar days rather than 7. Is this true?
[tt]
Data
22 23 24 ...
01/05/08 01/10/08 01/15/08 ...
[/tt]

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

Sorry, I did not give you a tip on the dates.

Assuming that you're using 7 day weeks, and 1/1/2008 is week 16...
[tt]
WeekOffset: =INT(DATEVALUE("1/1/2008")/7)-16
[/tt]
then take any of your other dates, to calculate the week, suppose your date is in A3...
[tt]
=INT(A3/7)-WeekOffset
[/tt]


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[sxschech]

What I showed was sample data to give an idea of the structure, sorry if it caused confusion. Data are actually shown with a Friday date

[tt]
Date Weekno
21-DEC-2007 14
28-DEC-2007 15
04-JAN-2008 16
11-JAN-2008 17
18-JAN-2008 18
25-JAN-2008 19
[/tt]

 

[SkipVought]

[tt]
Date Weekno MyFormula
21-Dec-07 14 14
28-Dec-07 15 15
4-Jan-08 16 16
11-Jan-08 17 17
18-Jan-08 18 18
25-Jan-08 19 19
[/tt]


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[sxschech]

Skip, I must be doing something wrong, I pasted your formula as =INT(A1/7)-14 and result was 5619.

 

[SkipVought]

5619 in your WeekOffset CONSTANT. You must subtract the WeekOffset from each...
[tt]
=INT(A3/7)-WeekOffset
[/tt]

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}