Finding data from second table with key in first table, with loop

[Samgold]

Hello,
I have attached sample files of my orders table, order line items table and the result that I am looking for.
We get orders with a unique order number. The orders table has the high level particulars - like order number, time placed, customer id, customer name etc. etc., Order total amount and so on.
There is an order line items table that gives the details for each order - like products purchased, qty, line item total. This table also contains two additional items - SHIPPING and DISCOUNT.
I would like to extract the SHIPPING amount for each order (if it exists) or make Charge for SHIPPING = 0, and put it in the RESULTS TABLE. The Same goes for DISCOUNT.

SO the RESULTS TABLE is the ORDERS TABLE with two extra columns, SHIPPING and DISCOUNT.

I dont know how to loop through using index and match to cycle through all the line items for each order (and the number will be different for different orders) to find the SHIPPING cost or the DISCOUNT if it exists.

Thanks for any guidance in advance.
Sam

 

[Nebu]

Hi:

Formula in Yellow cells.

Thanks

 

[Samgold]

Hi:

Formula in Yellow cells.

Thanks

 

[Samgold]

That does give a great result and is almost what I am looking for. Reason for hesitation is this.
Using constructs like Sheet2!$A$2:$A$12 in the formula assumes that you know the length of the table (12 in this case). But I do not know the length of the column, because it depends on the number of orders and the number of line items for each order.
Is it possible to get the length of the order line item sheet on the fly, and use it almost like a variable?

I really dont know much but learning as I go - so please forgive if my question is a inexperienced person question.
Thanks in advance
Sam

 

[Nebu]

Hi:

Have included the named ranges to make the rows dynamic.

Thanks

 

[GraH - Guido]

Hi Samgold,
If you'd use table (CTRL+T on the range) and structured references (just select the column), there is no need to use named ranges.

 

[bosco_yip]

Or………………..

In M2 copied over to N2 and all copied down :

=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$I2,Sheet2!$B:$B,M$1)

Remark :

Using the whole column reference in SUMIFS, of which typically uses the "used range" only, so that shouldn't be any less efficient and also no need to use dynamic range.

Regards
Bosco

 

[Peter Bartholomew]

I like the Tables option suggested by Guido (though I use Ctrl+L so that I can follow it with Ctrl+Shift+L to get rid of the filter buttons). Because @Nebu had already introduced the Names 'Amount' etc. I simply redefined them to refer to the structured reference.

I found @Nebu's use of MMULT to act as an array formula wrapper interesting though, in the event, I decided that I prefer LOOKUP since it also does the INDEX and the MATCH.

I also included a column that uses a Named Formula as an alternative strategy to get over the issue of cell-based formulas making such a dog's breakfast of any formula involving an array.

This brings the technology level up to Office2007, sooner or later we will need to start looking at the impact of the new dynamic arrays.

 

[Samgold]

Hello all
Thanks for all your help.
All methods seem very good and I am looking at them carefully to understand the logic behind them.

In both cases it appears that the item_code is assumed to be the Name of the column (which is a logical assumption).

However, in my case the column name is Shipping but the item code is S-AND-H.
So I changed the calculation to IFERROR(INDEX(Amount,MATCH(1,MMULT(($I4=Order_NO)*("Shipping"=Item_Code),1),0)),0) - for our test files and I will change it to S-AND-H for the real work.
In the same way I changed the calc to =SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$I2,Sheet2!$B:$B,"Shipping") in the SUMIFS suggestion.

Both of them work.

Is there a downside to just putting what I need in double quotes?

Thank you all very very much.
Sam

 

[Samgold]

I like the Tables option suggested by Guido (though I use Ctrl+L so that I can follow it with Ctrl+Shift+L to get rid of the filter buttons). Because @Nebu had already introduced the Names 'Amount' etc. I simply redefined them to refer to the structured reference.

I found @Nebu's use of MMULT to act as an array formula wrapper interesting though, in the event, I decided that I prefer LOOKUP since it also does the INDEX and the MATCH.

I also included a column that uses a Named Formula as an alternative strategy to get over the issue of cell-based formulas making such a dog's breakfast of any formula involving an array.

This brings the technology level up to Office2007, sooner or later we will need to start looking at the impact of the new dynamic arrays.

PETER--

I like your structured approach. I found your definitions in Name Manager. I will study it some more as well. And try to hard code the item code I am looking for - which may be different from the column heading.

Thanks very much.
SAM

 

[Peter Bartholomew]

Sam
There is no problem with hardcoding provided you are sure you will not need to change the value in use. If you need greater flexibility the criterion can be placed in its own range, outside the table. The SUMIFS offers the most flexible approach because it will allow you to have multiple criteria in a range/array and then SUM the output from each, in effect, giving you an OR condition.