|
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
Wow!
|
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
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 |
Follow up question about consolidating dups and sums
If I could solve this I would, but I dont think I can.
Tom |
| All times are GMT +1. The time now is 12:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com