Skip navigation

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

299 Views 6 Replies Latest reply: Mar 19, 2013 1:04 PM by cathy fasano RSS
cathy fasano Level 2 Level 2 (340 points)
Currently Being Moderated
Mar 19, 2013 7:52 AM

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?

  • Wayne Contello Level 6 Level 6 (12,580 points)

    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.

  • Wayne Contello Level 6 Level 6 (12,580 points)

    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).

  • Wayne Contello Level 6 Level 6 (12,580 points)

    Cathy,

     

    You can ue the function vlookup() to identify data in the table on the right like:

    Screen Shot 2013-03-19 at 12.32.52 PM.png

    In the table on the left, "Summary":

    B2=IF(ISBLANK(A2), "", SUMIF(Data :: A, "="&A2, Data :: B))

     

    select B2 and fill down as needed

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.