Follow up question about consolidating dups and sums
in [Functions]
|
Prev: Data moving 2
Next: Excell mistake in addition
From: TriciaZ on 3 Apr 2010 13:31 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 3 Apr 2010 14:18 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 3 Apr 2010 14:41 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 3 Apr 2010 15:49 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 3 Apr 2010 16:16
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 > > |