Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Headache
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Headache
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" , iletide sunu yazdi om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Headache
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" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look up Headache
Thanks, I will give that a try.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm a leaner wiht a headache!! | Excel Worksheet Functions | |||
Frequency Headache | Excel Worksheet Functions | |||
headache! :) unique_id's | Excel Worksheet Functions | |||
Vlookup Headache | Excel Worksheet Functions | |||
Combining two Functions Headache | Excel Worksheet Functions |