Pivot table, two data sources.

[Erasmus Bowen]

Hi,

(Sorry for Xposting. I already posted this in the MS/XL/Misc NG but
received no replies.
Everybody there must still be on vacation ... ?
I really would like to do this *without* programming but I know that this is
where the gurus hang out.

I have a large CSV data file that I am using as an "external data source"
for a Pivot table in an (autoupdate) template.
That works fine. It's the sales file created automatically by an
application. So it's "sales.csv" and my template with the Pivot table is
"sales.xlt".

But I also have costs, so I have another file where costs are recorded
manually. The data is in "Costs.xls" and I will have another Pivot table
where I analyse the costs : "Costs.xlt"

Now I want to offset Costs against Sales, so I am looking for some way to
get a new pivot table to read both the Sales.CSV and the Costs.XLS data and
combine the data and display the results.

Anyone here know how to do that ?
Or, can anyone point me to a howto site where that is explained.


TIA

Erasmus

 

[DM Unseen]

Erasmus,

this post actually belongs to the QueryDAO subgroup(not many check
there though).

To get the Sales and the cost together you first need to tell us how
you want that.

I suspect you need to define a query that creates 1 new table from
those 2 tables and shows how the new source table should look like. If
you want a compare them on a record by record basis I suspect a JOIN
is needed between those tables. If you just want to list both costs &
sales beneath eachother in the pivottable, you need to concatenate
those tables(UNION query)

The best solution would be to have both tables in a database and write
the new source query there.

DM Unseen

 

[Richard Buttrey]

Hi,

(Sorry for Xposting. I already posted this in the MS/XL/Misc NG but
received no replies.
Everybody there must still be on vacation ... ?
I really would like to do this *without* programming but I know that this is
where the gurus hang out.

I have a large CSV data file that I am using as an "external data source"
for a Pivot table in an (autoupdate) template.
That works fine. It's the sales file created automatically by an
application. So it's "sales.csv" and my template with the Pivot table is
"sales.xlt".

But I also have costs, so I have another file where costs are recorded
manually. The data is in "Costs.xls" and I will have another Pivot table
where I analyse the costs : "Costs.xlt"

Now I want to offset Costs against Sales, so I am looking for some way to
get a new pivot table to read both the Sales.CSV and the Costs.XLS data and
combine the data and display the results.

Anyone here know how to do that ?
Or, can anyone point me to a howto site where that is explained.


TIA

Erasmus

This assumes there is a common key linking the Sales and Costs, e.g. a
common account code or whatever.

Probably the easiest way to achieve this is to open the Sales.csv
file, parsing it as you do so, creating an xls workbook

Then open the Costs.xls sheet and use a Vlookup formula to copy across
the Sales values to a suitable additional column, then sum the Sales
and Cost Columns in a new third column and use this in a Pivot table.

You may need to check with another Vlookup formula that all the keys
in Sales also exist in Costs, and vice versa so that you do not miss
any. Correcting and adding any missing ones as necessary.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Erasmus Bowen]

OK
Will check the Query DAO group.
Didn't wan't to get into programming VLOOKUPS etc.
I don't know SQL syntax but
"concatenate "those tables(UNION query)"
seems to be what I need.
Thanks
Erasmus