Follow up question about consolidating dups and sums
in [Functions]
|
Prev: Data moving 2
Next: Excell mistake in addition
From: Don Guillett on 3 Apr 2010 16:28 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(LU!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!A3"))) It can also be done using a defined name for the sheets. =SUMPRODUCT(SUMIF(INDIRECT(ms&"!$a1:$z1"),"ttl",INDIRECT(ms&"!a"&ROW(A2)&":z"&ROW(A2)&""))) or you can use macros - Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "TriciaZ" <TriciaZ(a)discussions.microsoft.com> wrote in message news:788163CD-828A-48CB-9C12-7E278234E88A(a)microsoft.com... > 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 >> >>
From: tompl on 3 Apr 2010 17:39 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) Can't 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
From: Don Guillett on 3 Apr 2010 18:16 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 dguillett(a)gmail.com "tompl" <tompl(a)discussions.microsoft.com> wrote in message news:E0673E5E-FB57-48A0-8345-0223AD35DA6D(a)microsoft.com... > 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) > > Can't 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 >
From: tompl on 3 Apr 2010 19:06 Wow!
From: tompl on 3 Apr 2010 19:32
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 |