Unpivoting Data - Split from one table to apply to other table.

[himalarohee]

Hi, I have a table which have been loaded into data model like:

	2014	2015	2016
A	10	20	30
B	10	15	25
C	25	25	30

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

I have another one like

	2014	2015	2016
a	0.5	0.6	0.7
b	0.4	0.3	0.2
c	0.1	0.1	0.1

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

How can I create a third table like below, which takes split from one table and it applies to the other one to create like below?

	2014	2015	2016
A-a	5	12	21
A-b	4	6	6
A-c	1	2	3
B-a	5	9	17.5
B-b	4	4.5	5
B-c	1	1.5	2.5
C-a	12.5	15	21
C-b	10	7.5	6
C-c	2.5	2.5	3

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[GraemeMcRae]

I'll have a go at it...

I put your first table in Sheet1 and your second table in Sheet2, exactly as you laid it out, with cell A1 of each sheet empty, the titles in row 1 and the letters A, B, C or a, b, c in column 1.

Then, in Sheet3, I made the following column headings in the first row:

Seq, Sheet2, Sheet1, =TRIM(Sheet2!A1), =TRIM(Sheet2!B1), =TRIM(Sheet2!C1), =TRIM(Sheet2!D1)

so the column headings look like this when displayed:

Seq, Sheet2, Sheet1, , 2014, 2015, 2016

Then, in the second row, I placed these formulas:

=SUM(1,OFFSET(A2,-1,0))
=MOD($A2-1,COUNTA(INDIRECT(B$1&"!$A:$A")))+1
=MOD(INT(($A2-1)/COUNTA(INDIRECT(B$1&"!$A:$A"))),COUNTA(INDIRECT(C$1&"!$A:$A")))+1
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:D2))&"-"&INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:D2))
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:E2))*INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:E2))
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:F2))*INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:F2))
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:G2))*INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:G2))

Notice that the last three are essentially the same; just copy one to the next. Then copy the formulas down from row 2 to the subsequent rows 3 through 10 in this case. The resulting table, in cells Sheet3!D1:G10 looks like this:

	2014	2015	2016
A-a	5	12	21
A-b	4	6	6
A-c	1	2	3
B-a	5	9	17.5
B-b	4	4.5	5
B-c	1	1.5	2.5
C-a	12.5	15	21
C-b	10	7.5	6
C-c	2.5	2.5	3

<tbody>
</tbody>

As you can see, this approach is fairly general; It can handle sheets of any number of rows and columns, however it doesn't scale to more than 2 sheets, and it doesn't stop after 9 (in this case) rows (it just keeps spitting out the same table over and over!). With a little work, it could be made more general, however; I just wanted to throw this out there so you can tell me if you think I'm on the right track.

 

[Useful]

Hello,

try

https://drive.google.com/open?id=0B1-oaYEAJB2CblBmZnc0TTFjQUE

 

[himalarohee]

Hi, I have just three cells in second row, how do you have 7 different formulas for it? Where do I use it all?

I'll have a go at it...

I put your first table in Sheet1 and your second table in Sheet2, exactly as you laid it out, with cell A1 of each sheet empty, the titles in row 1 and the letters A, B, C or a, b, c in column 1.

Then, in Sheet3, I made the following column headings in the first row:

Seq, Sheet2, Sheet1, =TRIM(Sheet2!A1), =TRIM(Sheet2!B1), =TRIM(Sheet2!C1), =TRIM(Sheet2!D1)

so the column headings look like this when displayed:

Seq, Sheet2, Sheet1, , 2014, 2015, 2016

Then, in the second row, I placed these formulas:

=SUM(1,OFFSET(A2,-1,0))
=MOD($A2-1,COUNTA(INDIRECT(B$1&"!$A:$A")))+1
=MOD(INT(($A2-1)/COUNTA(INDIRECT(B$1&"!$A:$A"))),COUNTA(INDIRECT(C$1&"!$A:$A")))+1
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:D2))&"-"&INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:D2))
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:E2))*INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:E2))
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:F2))*INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:F2))
=INDEX(INDIRECT($C$1&"!$A:$D"),$C2+1,COLUMNS($D2:G2))*INDEX(INDIRECT($B$1&"!$A:$D"),$B2+1,COLUMNS($D2:G2))

Notice that the last three are essentially the same; just copy one to the next. Then copy the formulas down from row 2 to the subsequent rows 3 through 10 in this case. The resulting table, in cells Sheet3!D1:G10 looks like this:

	2014	2015	2016
A-a	5	12	21
A-b	4	6	6
A-c	1	2	3
B-a	5	9	17.5
B-b	4	4.5	5
B-c	1	1.5	2.5
C-a	12.5	15	21
C-b	10	7.5	6
C-c	2.5	2.5	3

<tbody>
</tbody>

As you can see, this approach is fairly general; It can handle sheets of any number of rows and columns, however it doesn't scale to more than 2 sheets, and it doesn't stop after 9 (in this case) rows (it just keeps spitting out the same table over and over!). With a little work, it could be made more general, however; I just wanted to throw this out there so you can tell me if you think I'm on the right track.

 

[Fazza]

if you add field header "ID" to top LH corner of each range and create normal defined names "A" for one table and "B" for the other, then this SQL returns the desired result

SELECT A.ID & '-' & B.ID AS [ID], A.[2014]*B.[2014] AS [2014], A.[2015]*B.[2015] AS [2015], A.[2016]*B.[2016] AS [2016]
FROM A, B

regards

 

[GraemeMcRae]

The seven formulas in the second row go in cells A2, B2, C2, D2, E2, F2, and G2.