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