X
xman
This is my first post here...I hope that i've chosen the right
category....
My problem is similiar to other people's when it comes to consolidating
ranges with Pivot Table (sorry for my english...). I've searched the
forum but I haven't found either solution or workaround for my
problem.
My data is divided into 2 sheets, because i need to use more than 255
columns (about 433). It looks like this:
col1(ID) col2(Chain store category) col3(address) col4(date)
col5-col255 (product's data). One product uses 9 columns like: space on
a shelf, price, comments....
Bacause there are more product's I need to report, I've spliited the
data into 2 sheets. So the second sheet has the first 4 columns the
same as in the first one (a copy) and from col5-col178 other product's
data.
When you create Pivot Table from multiple consolidation ranges, you
won't get the same pivot table layout that you'd get from a single
range. You can get only a field named "Rows" and one named "Columns" -
in other words, it doesn't create fields named after the column headers
..
How my pivot table should look like?
I need to calculate an average of one "parameter" (like price) for
every product. Becacuse there are (for now) 47 products a pivot table
can handle it. I need to present the results for every chain store
type. I would like the data to be presented like:
On the left (rows) there should be the chain store type (geant,
tesco,....), on the top (columns) there shold be product's 1-47. And in
the "data area" averages of the price (or other parameters).
Creating such a pivot table from one data range is easy but can't it
really be done using more data ranges? I can't use access or other
database. It just must be done with excel. I've even tried importing
"external data" (from the same file) with MS Query - and this should be
some workaround....but not a perfect one.
Please help! Thanks!
category....
My problem is similiar to other people's when it comes to consolidating
ranges with Pivot Table (sorry for my english...). I've searched the
forum but I haven't found either solution or workaround for my
problem.
My data is divided into 2 sheets, because i need to use more than 255
columns (about 433). It looks like this:
col1(ID) col2(Chain store category) col3(address) col4(date)
col5-col255 (product's data). One product uses 9 columns like: space on
a shelf, price, comments....
Bacause there are more product's I need to report, I've spliited the
data into 2 sheets. So the second sheet has the first 4 columns the
same as in the first one (a copy) and from col5-col178 other product's
data.
When you create Pivot Table from multiple consolidation ranges, you
won't get the same pivot table layout that you'd get from a single
range. You can get only a field named "Rows" and one named "Columns" -
in other words, it doesn't create fields named after the column headers
..
How my pivot table should look like?
I need to calculate an average of one "parameter" (like price) for
every product. Becacuse there are (for now) 47 products a pivot table
can handle it. I need to present the results for every chain store
type. I would like the data to be presented like:
On the left (rows) there should be the chain store type (geant,
tesco,....), on the top (columns) there shold be product's 1-47. And in
the "data area" averages of the price (or other parameters).
Creating such a pivot table from one data range is easy but can't it
really be done using more data ranges? I can't use access or other
database. It just must be done with excel. I've even tried importing
"external data" (from the same file) with MS Query - and this should be
some workaround....but not a perfect one.
Please help! Thanks!