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

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

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

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

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

I have done this for clients. Here is a formula that uses a list of sheets
from sheet LU col F.
=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(L U!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNT A(LU!F:F))&"!A3")))
It can also be done using a defined name for the sheets.
=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$a1:$z1"),"ttl",IN DIRECT(ms&"!a"&ROW(A2)&":z"&ROW(A2)&"")))
or you can use macros
-
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TriciaZ" wrote in message
...
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

Step One: All data must be in one table. If the ten worksheets cannot be
consolidated into one worksheet then the only option is to consolidate it
into one table in Access. For this example the worksheet should be named
€œAll€�.

Step Two: Create a new worksheet with columns State, County, Tons and
Commodity. Row 1 should have these names.

Step Three: Key in each possible combination of State, County and Commodity
in the rows of the new sheet (Assuming Columns A, B and D). Something like:

State County Tons Commodity
IA ADAMS AMMONIUM NITRATE
NE ADAMS AMMONIUM NITRATE
OK ALFALFA AMMONIUM NITRATE
IA ALLAMAKEE AMMONIUM NITRATE
KS ALLEN AMMONIUM NITRATE
LA ALLEN AMMONIUM NITRATE


Step Four: Enter this formula in column C (Tons) Row 2, and then copy it
down to the end of the used rows.


=SUMPRODUCT(--(All!$A$2:$A$65000=A2),
--(All!$B$2:$B$65000=B2),
--(All!$D$2:$D$65000=D2),All!$C$2:$C$65000)

Cant get it on one line, but it is one formula.

That should do it. You can then filter to exclude zero tons if that helps.

Tom

OP sent me one sheet so I wrote this for ONE sheet.

Option Explicit
Sub consolidateSAS()
Dim lr As Long
Dim i As Long

Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("A1:d" & lr).Sort _
Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

For i = lr To 2 Step -1
If Cells(i - 1, 2) = Cells(i, 2) And _
Cells(i - 1, 1) = Cells(i, 1) Then
Cells(i - 1, 3).Value = Cells(i - 1, 3) + Cells(i, 3)
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tompl" wrote in message
...
Step One: All data must be in one table. If the ten worksheets cannot be
consolidated into one worksheet then the only option is to consolidate it
into one table in Access. For this example the worksheet should be named
€œAll€�.

Step Two: Create a new worksheet with columns State, County, Tons and
Commodity. Row 1 should have these names.

Step Three: Key in each possible combination of State, County and
Commodity
in the rows of the new sheet (Assuming Columns A, B and D). Something
like:

State County Tons Commodity
IA ADAMS AMMONIUM NITRATE
NE ADAMS AMMONIUM NITRATE
OK ALFALFA AMMONIUM NITRATE
IA ALLAMAKEE AMMONIUM NITRATE
KS ALLEN AMMONIUM NITRATE
LA ALLEN AMMONIUM NITRATE


Step Four: Enter this formula in column C (Tons) Row 2, and then copy it
down to the end of the used rows.


=SUMPRODUCT(--(All!$A$2:$A$65000=A2),
--(All!$B$2:$B$65000=B2),
--(All!$D$2:$D$65000=D2),All!$C$2:$C$65000)

Cant get it on one line, but it is one formula.

That should do it. You can then filter to exclude zero tons if that
helps.

Tom

Wow!

My concerns are 1; it only addresses one sheet and not the ten that OP has
indicated and 2; it rather mutilates the data. My philosophy is to
manipulate data on a separate sheet so that new data can be pasted into the
old data sheet.

I suspect that a complete solution would be needed rather than detailed
support on specific issues in this case.

Tom

I got one sheet. I did one sheet..... Had I gotten 10 I very well may have
consolidated FIRST.....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tompl" wrote in message
...
My concerns are 1; it only addresses one sheet and not the ten that OP has
indicated and 2; it rather mutilates the data. My philosophy is to
manipulate data on a separate sheet so that new data can be pasted into
the
old data sheet.

I suspect that a complete solution would be needed rather than detailed
support on specific issues in this case.

Tom

If I could solve this I would, but I dont think I can.

Tom