Look up Headache

[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

 

[Haldun Alay]

Hi,

Did you try Pivot Table feature of Excel. You can easily group your data with Pivot Table.

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Chris" <[email protected]>, iletide sunu yazdi 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

 

[Rob Bovey]

Hi Chris,

A pivot table will give you what you are looking for. It seems a lot
harder than it really is to create one, but just follow the steps below.
After you've done it a few times it becomes second nature.

1) Select the worksheet with your data table on it.
2) Choose Data/Pivot Table..... from the Excel menu.
3) In Step 1 of the Pivot Table wizard, accept all the defaults and click
Next.
4) In Step 2 of the Pivot Table wizard, click in the range box and highlight
your entire table, including the headers, and click Next.
5) In Step 3 of the Pivot Table wizard choose a place to put your pivot
table and click Finish.
6) You will now be looking at an empty pivot table with the Pivot Table
toolbar floating over it. Click and drag "Pallets" from the toolbar and drop
it on the Data Items area. Drop "Customers" and "Depot" on the Row Fields
area right next to each other. You will now have your base pivot table.
7) Right-click over your pivot table and choose Table Options from the
shortcut menu. Uncheck the two "Grand Totals" checkboxes in the upper left
corner of the PivotTable Options dialog and click OK.
8) Right-click over the row entry "Customer 1 Total" and choose Field
Settings from the shortcut menu. In the PivotTable Field dialog click the
"None" option button under Subtotals.

This should give you a table that looks exactly the way you want. If you
want to get rid of the "PivotTable" and just have the table structure left,
select the whole range where the PivotTable is located, choose Edit/Copy,
followed by Edit/Paste Special/Values.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Chris]

Thanks, I will give that a try.