Excel Array to Table

[Ray S.]

I have an array in Excel that looks like a cross-tab query. I have Items that
are a series of cost centers as rows in the first column (call it
cost_centers). The rest of my columns are a series of business units (call
them B1...B12). The values in the array are percentage allocations for each
business unit. How can I import that Excel array as a table that will have
cost_centers, B1...B12, and each allocation value (call it pct_alloc)?

 

[Ray S.]

OK, nobody has replied...I did it, but it was a lot of work. Is there some
easy way to do this?

 

[John W. Vinson]

I have an array in Excel that looks like a cross-tab query. I have Items that
are a series of cost centers as rows in the first column (call it
cost_centers). The rest of my columns are a series of business units (call
them B1...B12). The values in the array are percentage allocations for each
business unit. How can I import that Excel array as a table that will have
cost_centers, B1...B12, and each allocation value (call it pct_alloc)?

It's not clear just what result you want, Ray. I see you have a solution (you
didn't explain it either though), but what i would recommend is a "Normalizing
Union Query". Assuming you want to import into a properly normalized table
with fields CostCenter, BusinessUnit and Allocation, you can use File... Get
External Data... Link to link to your spreadsheet; and in the SQL window
create a query like

SELECT X.CostCenter, "B1" AS BusUnit, [B1] AS Allocation FROM
linkedspreadsheet AS X
UNION ALL
SELECT X.CostCenter, "B2" AS BusUnit, [B2] AS Allocation FROM
linkedspreadsheet AS X
UNION ALL
SELECT X.CostCenter, "B3" AS BusUnit, [B3] AS Allocation FROM
linkedspreadsheet AS X
UNION ALL
<etc through all 12>

You can then base an Append query on this UNION query.

 

[Ray S.]

Thanks John,

Actually, my final step was to base a cross-tab query on the results. It
sounds kind of crazy, but the percent allocations were in a huge cross-tab
form to begin with. I needed to associate the cost center numbers with the
result of another query that gave me "functional area" names according to the
cc#s, (which is how I needed to present the business unit data using the
percent allocations for a calculation). Great help, as always. You guys are
really the best!

I have an array in Excel that looks like a cross-tab query. I have Items that
are a series of cost centers as rows in the first column (call it
cost_centers). The rest of my columns are a series of business units (call
them B1...B12). The values in the array are percentage allocations for each
business unit. How can I import that Excel array as a table that will have
cost_centers, B1...B12, and each allocation value (call it pct_alloc)?

It's not clear just what result you want, Ray. I see you have a solution (you
didn't explain it either though), but what i would recommend is a "Normalizing
Union Query". Assuming you want to import into a properly normalized table
with fields CostCenter, BusinessUnit and Allocation, you can use File... Get
External Data... Link to link to your spreadsheet; and in the SQL window
create a query like

SELECT X.CostCenter, "B1" AS BusUnit, [B1] AS Allocation FROM
linkedspreadsheet AS X
UNION ALL
SELECT X.CostCenter, "B2" AS BusUnit, [B2] AS Allocation FROM
linkedspreadsheet AS X
UNION ALL
SELECT X.CostCenter, "B3" AS BusUnit, [B3] AS Allocation FROM
linkedspreadsheet AS X
UNION ALL
<etc through all 12>

You can then base an Append query on this UNION query.