Look up Problem

[Chris]

Hi,

I have a question and tried to find it through the history of the
groups but no avail. The problem is this, please see example of my
data sheet.

Customer Depot Pallets

Customer 1 Town 1 12
Customer 1 Town 2 11
Customer 2 Town 3 12
Customer 2 Town 3 19
Customer 1 Town 1 7

I Need a look up table that can produce results as per below

Customer 1 Town 1 19
Customer 1 Town 2 11
Customer 2 Town 3 31

Thus performing a two reference lookup on Customer, then Depot to
produce a sum of the total pallets under these conditions.

Please can someone out there help me with this problem as its starting
to get annoying and really could do with sorting it.

Many thanks in advance,

Chris

 

[A.W.J. Ales]

Chris,

Look at Data / Pivot table to produce this outcome :

Data :

Customer Town Pallets
A 1 12
A 2 11
B 3 12
B 3 19
A 1 7



Sum of Pallets Town
Customer 1 2 3 Grand Total
A 19 11 30
B 31 31
Grand Total 19 11 31 61



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Harlan Grove]

Thus performing a two reference lookup on Customer, then Depot to
produce a sum of the total pallets under these conditions.

....

The Pivot Table answer you've already received is probably the simplest
solution, but FWLIW there's also a purely formula solution. If your source
data were in a range named TBL (which included only the data records and not
the column headings), and if the result table were to begin in cell A2 on
some other worksheet, try these formulas on that other worksheet.

A2:
=INDEX(TBL,1,1)

B2:
=INDEX(TBL,1,2)

C2:
=SUMPRODUCT((INDEX(TBL,0,1)=A2)*(INDEX(TBL,0,2)=B2)*INDEX(TBL,0,3))

A3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(A$2:A2,INDEX(TBL,0,1))
*COUNTIF(B$2:B2,INDEX(TBL,0,2)),0),1)

B3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(A$2:A2,INDEX(TBL,0,1))
*COUNTIF(B$2:B2,INDEX(TBL,0,2)),0),2)

C3:
=SUMPRODUCT((INDEX(TBL,0,1)=A3)*(INDEX(TBL,0,2)=B3)*INDEX(TBL,0,3))

Select A3:C3 and fill down as far as needed. The formulas will evaluate to
#N/A when you've exhausted the data in TBL.

 

[A.W.J. Ales]

Harlan,

It does work but as far as I'm concerned is almost TO clever for me. If
you appreciate an (almost) pure formula setup, I prefer the next solution.

Suppose the data (excluding the heading) is in the area : A2:C6
Customer Town Pallets
A 1 12
A 2 11
B 3 12
B 3 19
A 1 7

and you want to start the overview in the cell H1 :
In cells H2:H3 give all possible customers (here only two)
In cells I1:K1 give all possible cities (here three)

Now in cell I2 the formula :
=SUMPRODUCT(($A$2:$A$6=$H2)*($B$2:$B$6=I$1);$C$2:$C$6)
Copy this formula over the area : I2:K3.

The result is then almost the same as in a pivot table and has the advantage
that it is automaticly updated if the data changes (which is not - as far as
I know - the case for pivot tables).

De disadvantage is that you have to fill in your self the possible customers
( A and B) and cities (1 ; 2 and 3)

1 2 3
A 19 11 0
B 0 0 31


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Thus performing a two reference lookup on Customer, then Depot to
produce a sum of the total pallets under these conditions.

...

The Pivot Table answer you've already received is probably the simplest
solution, but FWLIW there's also a purely formula solution. If your source
data were in a range named TBL (which included only the data records and not
the column headings), and if the result table were to begin in cell A2 on
some other worksheet, try these formulas on that other worksheet.

A2:
=INDEX(TBL,1,1)

B2:
=INDEX(TBL,1,2)

C2:
=SUMPRODUCT((INDEX(TBL,0,1)=A2)*(INDEX(TBL,0,2)=B2)*INDEX(TBL,0,3))

A3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(A$2:A2,INDEX(TBL,0,1))
*COUNTIF(B$2:B2,INDEX(TBL,0,2)),0),1)

B3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(A$2:A2,INDEX(TBL,0,1))
*COUNTIF(B$2:B2,INDEX(TBL,0,2)),0),2)

C3:
=SUMPRODUCT((INDEX(TBL,0,1)=A3)*(INDEX(TBL,0,2)=B3)*INDEX(TBL,0,3))

Select A3:C3 and fill down as far as needed. The formulas will evaluate to
#N/A when you've exhausted the data in TBL.

 

[Harlan Grove]

Suppose the data (excluding the heading) is in the area : A2:C6
Customer Town Pallets
A 1 12
A 2 11
B 3 12
B 3 19
A 1 7

Table above reformatted for monospaced fonts.

and you want to start the overview in the cell H1 :
In cells H2:H3 give all possible customers (here only two)
In cells I1:K1 give all possible cities (here three) ....
De disadvantage is that you have to fill in your self the
possible customers ( A and B) and cities (1 ; 2 and 3)

1 2 3
A 19 11 0
B 0 0 31

....

Customers and cities can be generated by formula using a variation on the
approach I used in previous response. Given your data (excluding headings)
in a range named TBL, and the results beginning in a range with cell H1
blank but the cell above the distinct customers and to the left of the
distinct towns, try

H2:
=INDEX(TBL,1,1)

H3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(H$2:H2,INDEX(TBL,0,1)),0),1)

I1:
=INDEX(TBL,1,2)

J1: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF($I1:I1,INDEX(TBL,0,2)),0),2)

Select H3 and fill down until the formula returns #N/A. Select J1 and fill
right until the formula returns #N/A. Then use the following formula to fill
out the table.

I2:
=SUMPRODUCT((INDEX(TBL,0,1)=$H2)*(INDEX(TBL,0,2)=I$1)*INDEX(TBL,0,3))

Select I2 and fill as far down as the column H formulas and as far right as
the row 1 formulas. Note the column H and row 1 formulas above are actually
simpler than the formulas for customer and town in my previous response
because redundancy in the result table is allowed.

If one knows what one's doing, pivot tables are never necessary. They are a
convenience feature, nothing more.

 

[A.W.J. Ales]

Harlan,

I didn't check your function this time but i believe you that it works.

I'm however - if possible - fond of a simple approach.
If I have to think very hard about what's going on in a formula, I tend - as
lazy as I am and as said : if possible - to look if a more simple formula is
possible.

I do appreciate your formula's, however a formula with functions four levels
deep more or less "frighten me away". If absolutely necessary I will
take such a formula, but only if I really can't avoid them.

This in no way means that I don't admire your skills, just that I'm possibly
not that good (and maybe just to lazy)

Any way : In our discussion we seem to have lost chris who originally posted
the question.

Thanks for this "discussion" and I think we will sometime meet again here.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Suppose the data (excluding the heading) is in the area : A2:C6
Customer Town Pallets
A 1 12
A 2 11
B 3 12
B 3 19
A 1 7

Table above reformatted for monospaced fonts.

and you want to start the overview in the cell H1 :
In cells H2:H3 give all possible customers (here only two)
In cells I1:K1 give all possible cities (here three) ...
De disadvantage is that you have to fill in your self the
possible customers ( A and B) and cities (1 ; 2 and 3)

1 2 3
A 19 11 0
B 0 0 31

...

Customers and cities can be generated by formula using a variation on the
approach I used in previous response. Given your data (excluding headings)
in a range named TBL, and the results beginning in a range with cell H1
blank but the cell above the distinct customers and to the left of the
distinct towns, try

H2:
=INDEX(TBL,1,1)

H3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(H$2:H2,INDEX(TBL,0,1)),0),1)

I1:
=INDEX(TBL,1,2)

J1: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF($I1:I1,INDEX(TBL,0,2)),0),2)

Select H3 and fill down until the formula returns #N/A. Select J1 and fill
right until the formula returns #N/A. Then use the following formula to fill
out the table.

I2:
=SUMPRODUCT((INDEX(TBL,0,1)=$H2)*(INDEX(TBL,0,2)=I$1)*INDEX(TBL,0,3))

Select I2 and fill as far down as the column H formulas and as far right as
the row 1 formulas. Note the column H and row 1 formulas above are actually
simpler than the formulas for customer and town in my previous response
because redundancy in the result table is allowed.

If one knows what one's doing, pivot tables are never necessary. They are a
convenience feature, nothing more.

 

[Chris]

Thanks for your help, you have both contributed to the answer to my question.

Harlan,

I didn't check your function this time but i believe you that it works.

I'm however - if possible - fond of a simple approach.
If I have to think very hard about what's going on in a formula, I tend - as
lazy as I am and as said : if possible - to look if a more simple formula is
possible.

I do appreciate your formula's, however a formula with functions four levels
deep more or less "frighten me away". If absolutely necessary I will
take such a formula, but only if I really can't avoid them.

This in no way means that I don't admire your skills, just that I'm possibly
not that good (and maybe just to lazy)

Any way : In our discussion we seem to have lost chris who originally posted
the question.

Thanks for this "discussion" and I think we will sometime meet again here.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Suppose the data (excluding the heading) is in the area : A2:C6
Customer Town Pallets
A 1 12
A 2 11
B 3 12
B 3 19
A 1 7

Table above reformatted for monospaced fonts.

and you want to start the overview in the cell H1 :
In cells H2:H3 give all possible customers (here only two)
In cells I1:K1 give all possible cities (here three) ...
De disadvantage is that you have to fill in your self the
possible customers ( A and B) and cities (1 ; 2 and 3)

1 2 3
A 19 11 0
B 0 0 31

...

Customers and cities can be generated by formula using a variation on the
approach I used in previous response. Given your data (excluding headings)
in a range named TBL, and the results beginning in a range with cell H1
blank but the cell above the distinct customers and to the left of the
distinct towns, try

H2:
=INDEX(TBL,1,1)

H3: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF(H$2:H2,INDEX(TBL,0,1)),0),1)

I1:
=INDEX(TBL,1,2)

J1: [array formula]
=INDEX(TBL,MATCH(0,COUNTIF($I1:I1,INDEX(TBL,0,2)),0),2)

Select H3 and fill down until the formula returns #N/A. Select J1 and fill
right until the formula returns #N/A. Then use the following formula to fill
out the table.

I2:
=SUMPRODUCT((INDEX(TBL,0,1)=$H2)*(INDEX(TBL,0,2)=I$1)*INDEX(TBL,0,3))

Select I2 and fill as far down as the column H formulas and as far right as
the row 1 formulas. Note the column H and row 1 formulas above are actually
simpler than the formulas for customer and town in my previous response
because redundancy in the result table is allowed.

If one knows what one's doing, pivot tables are never necessary. They are a
convenience feature, nothing more.