Using Columns for Rows

[#1]

Hi,

I have a table in Excel that looks like this:

JobNO	Per1	Per2	Per3	Per4	Year
1-9-100-1130	845,069	852,886	852,886	875,244	2008
1-9-150-2000	-1,296,115	-1,320,953	-1,345,791	-1,370,629	2008
1-9-150-2010	-6,081,111	-6,200,349	-6,319,586	-6,438,824	2008
1-9-150-2020	-145,179	-149,795	-154,411	-159,028	2008
1-9-150-2030	0	0	0	0	2008
1-9-150-2040	-10,946,211	-10,998,257	-9,853,337	-9,891,794	2008
1-9-150-2050	0	-6,944	-740	-1,122	2008
1-9-150-2060	-1,023,129	-1,027,505	-1,029,945	-1,032,385	2009
1-9-150-2070	-130,519	-131,218	-131,917	-132,617	2009
1-9-150-2080	-79,143	-86,057	-92,972	-99,886	2009
1-9-150-2090	-144,221	-144,858	-145,509	-146,161	2009
1-9-150-2100	-775,553	-783,567	-791,582	-798,748	2009
1-9-150-2110	-423,864	-424,069	-424,273	-424,407	2009
1-9-150-2120	-5,654,111	-5,654,111	-5,654,111	-5,654,111	2009
1-9-150-2130	-802,655	-804,578	-806,500	-809,043	2009
1-9-200-3000	428,172	452,280	372,648	456,407	2010
1-9-200-3010	134,915	149,442	131,858	141,380	2010
1-9-200-3020	0	0	0	0	2010
1-9-200-3030	442,576	358,345	523,037	376,969	2010
1-9-200-3050	477,838	449,903	428,790	527,087	2010
1-9-200-3060	91,602	127,491	179,766	183,282	2010
1-9-200-3070	0	0	0	0	2010
1-9-200-3090	16,732	42,598	6,791	133,782	2010
1-9-200-3095	119,896	107,931	137,057	65,130	2010

I've only taken two Job Numbers as an example of what I want it to look like:

JobNO	Period	Amount
1-9-100-1130	2008 Per1	845,069
1-9-100-1130	2008 Per2	852,886
1-9-100-1130	2008 Per3	852,886
1-9-100-1130	2008 Per4	875,244
1-9-150-2000	2008 Per1	-1,296,115
1-9-150-2000	2008 Per2	-1,320,953
1-9-150-2000	2008 Per3	-1,345,791
1-9-150-2000	2008 Per4	-1,370,629

Please could someone help me on this. It seems so simple but I'm not getting it. The script I'm loading is:

"ExcelLoad":

JobNO, Per1, Per2, Per3, Per4, Year

LOAD

C:\Documents and Settings\SJ\Desktop\BB_Table.xlsx

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

S

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

First I'd cut and paste the Year column to the second place.

Then, either using the wizard loading from "Table Files" in the script editor "Enabling Transformation Step" or using the code below

CrossTable(Period, Amount, 2)LOAD JobNO, Year, Per1, Per2, Per3, Per4FROM(biff, embedded labels, table is Sheet1$);

Should return as you want.

Regards.

[#3]

HI Miguel,

Thanks, CrossTable does the trick

Regards,

S