From: TriciaZ on
I used the pivot table and it worked like a charm.
However, the data is going to be transferred into another program
and it needs to be in the same format as the original with headers like:

State County Tons Commodity

I have played around with the pivot table some, but it is not right.
Perhaps a formula or function would work better? Thanks!


--
taz0923
From: tompl on
This would be really easy to do with MS Access, but challenging in MS Excel.

In Excel, you could list all counties and products on a seperate worksheet
then use the sumproduct formula to lookup the sums. Not a very elegant
solution but it might work.

Tom

"TriciaZ" wrote:

> I used the pivot table and it worked like a charm.
> However, the data is going to be transferred into another program
> and it needs to be in the same format as the original with headers like:
>
> State County Tons Commodity
>
> I have played around with the pivot table some, but it is not right.
> Perhaps a formula or function would work better? Thanks!
>
>
> --
> taz0923
From: TriciaZ on
I was thinking that too. I could open Access and import the data from excel.
Then run a query?? Then send it back to excel. I have 10 separate sheets
of data with thousands of rows of data...any more detailed suggestions would
be great! Thanks
--
taz0923


"tompl" wrote:

> This would be really easy to do with MS Access, but challenging in MS Excel.
>
> In Excel, you could list all counties and products on a seperate worksheet
> then use the sumproduct formula to lookup the sums. Not a very elegant
> solution but it might work.
>
> Tom
>
> "TriciaZ" wrote:
>
> > I used the pivot table and it worked like a charm.
> > However, the data is going to be transferred into another program
> > and it needs to be in the same format as the original with headers like:
> >
> > State County Tons Commodity
> >
> > I have played around with the pivot table some, but it is not right.
> > Perhaps a formula or function would work better? Thanks!
> >
> >
> > --
> > taz0923
From: tompl on
Well, you have not provided much detail with which I can be more specific.
Where does the data come from originally? Why is it in Excel.? Why is it on
ten separate worksheets? Is the format of the ten worksheets the same? Why
do you want to put it back into Excel when you mentioned something about
sending it to another application? Maybe you could skip Excel altogether.
Keep the data in a table in Access, set up the query, then export the data in
a format that can be used by your other application. Do you regularly append
data to the existing? Do you get a completely new set of data periodically?
So many questions!

Tom


From: TriciaZ on
The data is from a spreadsheet created in Excel. I am helping someone
manipulate their data. The data contains information from 7 different states
and many many counties within those states. The format of the 10 worksheets
is the same, yes. The end user needs the data in Excel in a specific
arrangement in order to send it to another application for analysis. I do
not know if Access would make this process any easier, in fact, the more I
think about it perhaps not. I do not know if the end user regularly
manipulates this data or not.

Basically, I have 10 spreadsheets full of data all set up like this:

State County Tons Commodity
IA ADAMS 143.97 AMMONIUM NITRATE
NE ADAMS 97 AMMONIUM NITRATE
OK ALFALFA 78.08 AMMONIUM NITRATE
OK ALFALFA 101 AMMONIUM NITRATE
IA ALLAMAKEE 72.88 AMMONIUM NITRATE
IA ALLAMAKEE 109.25 AMMONIUM NITRATE
KS ALLEN 1014.69 AMMONIUM NITRATE
LA ALLEN 118.78 AMMONIUM NITRATE


The sheet needs to read:
State County Tons Commodity
IA ADAMS 143.97 AMMONIUM NITRATE
NE ADAMS 97 AMMONIUM NITRATE
OK ALFALFA 179.08 AMMONIUM NITRATE
IA ALLAMAKEE 182.13 AMMONIUM NITRATE
KS ALLEN 1014.69 AMMONIUM NITRATE
LA ALLEN 118.78 AMMONIUM NITRATE

Hopefully this is a better explanation.
This is VERY simplified, but this is what I'm trying to help him do.
Thanks! I initially thought maybe a nested function - VLOOKUP and IF, or
even a visual basic program. I don't know what to do. Thanks for helping.

--
taz0923


"tompl" wrote:

> Well, you have not provided much detail with which I can be more specific.
> Where does the data come from originally? Why is it in Excel.? Why is it on
> ten separate worksheets? Is the format of the ten worksheets the same? Why
> do you want to put it back into Excel when you mentioned something about
> sending it to another application? Maybe you could skip Excel altogether.
> Keep the data in a table in Access, set up the query, then export the data in
> a format that can be used by your other application. Do you regularly append
> data to the existing? Do you get a completely new set of data periodically?
> So many questions!
>
> Tom
>
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Data moving 2
Next: Excell mistake in addition