looking up data from one table and putting it into another table

Question

Level 2

393 points

looking up data from one table and putting it into another table

I'm stumped as to what you would even call what I'm trying to do...

I am using a poorly-designed and poorly-executed database application which allows me to dump out the data in a series of "reports" which I can get as tab-separated rows/columns of numbers, and then I can open those up in Numbers without problems.

(I don't think it really matters the details of what the data is, but I will explain just because it makes it easier to explain the problem. I am a girl scout leader, and this software tracks girl scout cookie transactions. You have cookies moving in and out of the troop from/to the girl scout council and other troops, and then you have cookies moving between the troop and the individual girl scouts.)

In my case, I have one table which has all of the transactions of between the girls and the troop. In another table, I have ALL of the transactions, but in each line that is a girl-troop or troop-girl transaction the quantity of the transaction is zero. (Did I mention that it is poorly-designed and poorly-executed?)

I am trying to get my books to balance -- the sum of cookies that went in and out of the troop has to equal the sum of cookies that went to and from the girls and it doesn't and I can't find my mistake -- and I need to be able to see all of the transactions in one list so I can watch the running total move over time and find the transaction that I missed, entered twice, or entered with the wrong value.

So what I want to do is to add a column to the all-transaction table, and then go down the orderID column in that table and look up that orderID in the other table, and get the total out of the total column and put it in the first table. I am totally stumped as to how to do this.

What I tried was to set the cell in my new column like this:

=SUMIF(I2:I92,GirlOrderReport :: L2:L72,GirlOrderReport :: AC2:AC72)

where column I has the orderID in the TroopOrder table, column L has the orderID in the GirlOrder table, and column AC in the GirlOrder table has the number of cookie boxes in the order.

The error is "SUMIF requires that all range arguments be the same size"

Am I close? Completely confused?

Posted on Mar 19, 2013 7:52 AM

Reply

Answer 1

Mar 19, 2013 8:14 AM in response to cathy fasano

Cathy,

=SUMIF(I2:I92,GirlOrderReport :: L2:L72,GirlOrderReport :: AC2:AC72)

should be:

=SUMIF(I2:I72,...

I omitted the last part to highlight the typo. The size of the range MUST be the same so rows 2 - 72 are used for the conditions and should be the same size range for the sum.

Reply

Answer 2

cathy fasano Author

Level 2

393 points

Mar 19, 2013 9:06 AM in response to Wayne Contello

The two different tables are two different lengths -- the table that I am getting the data from has 71 rows with troop/girl-girl/troop transactions, while the table I am trying to get the data into has 91 rows -- the 71 rows which correspond to the 71 rows in the other table plus the 20 rows that contain troop-troop/troop-council transactions.

The problem is that in the bigger table, all 71 of the common rows have the wrong value (zero), and I am trying to correct the values by looking them up in the smaller table, which has the right values.

Here's a screen shot of the two tables side-by-side that illustrates the data:

I'm trying to get the T2 column in the long table to say 142,142,141,-1,-1,etc... lokking up the data from the short table.

Reply

Answer 3

Mar 19, 2013 10:03 AM in response to cathy fasano

Can you comment on whether the OrderID occurs more than once in either or both list? Also can you comment on whether, in the table on the left, the non-zero values correspond to COUNCIL and TROOP transactions (as opposed to GIRL).

Reply

Answer 4

cathy fasano Author

Level 2

393 points

Mar 19, 2013 10:27 AM in response to Wayne Contello

OrderID is the primary key for each transaction, and that column is once in each table. The longer table

-- has the orderID in column I

-- In the "Total" column, each of the rows which shows a "Total" of zero is a GIRL transaction, and the correct total appears in the other (shorter) table, using the OrderID. Every non-zero value is a COUNCIL/TROOP transaction.

In the longer table, the GIRL row totals are all wrong but the COUNCIL/TROOP row totals are right. The shorter table only has GIRL rows, and all those rows are correct.

So the new column in the longer table is what I want to contain the correct number, which I want to fetch from the other table, using the OrderID as the field that needs to match up.

Reply