Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lists
We have a situation here as follows;
For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#2
|
|||
|
|||
Hi!
This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#3
|
|||
|
|||
Hi Biff,
Let me know if this is better. A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT A B C driver B max driver C albertson 7-11 driver D cooper driver E ov "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#4
|
|||
|
|||
Biff, what I need the formula to do is as follows:
Lets assume row 10 will list out the customers for driver B Then it will need to look up to see the first cell in Column B that has a value greater than zero and if this is true then look to see if column F has a value greater than zero and when both are true it will return the value in coulmn A. The net result will be all the values that meet both conditions listed out horizontlly for each driver without any blanks in the middle. Thanks Ron "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#5
|
|||
|
|||
Hi!
Based on your sample table these are the results I get: DriverB..........Albertson..........Max DriverC..........7-11 DriverD..........Cooper DriverE...........OV OK, here's the details: A1:F1 are the headers: Customer, DriverB, DriverC, DriverD, DriverE, XXX Used named ranges as follows: Customer =$A$2:$A$10 DriverB =$B$2:$B$10 DriverC =$C$2:$C$10 DriverD =$D$2:$D$10 DriverE =$E$2:$E$10 XXX =$F$2:$F$10 Extract data to another table: A15 = DriverB A16 = DriverC A17 = DriverD A18 = DriverE Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER in B15: =INDEX(Customer,SMALL(IF(INDIRECT($A15)<"",IF(XXX 0,ROW(INDIRECT("1:"&COUNTA(Customer))))),COLUMN(A :A))) Copy across to enough cells that will hold all possible matches. How many will that be? 5? 10? Then copy down to row 18. Cells that don't hold a match will return a #NUM! error. To hide those I used conditional formatting. Select the entire range of formulas, B15:??18 Goto FormatConditional Formatting Formula is: =ISERROR(B15) Set the font color to be the same as the background fill color OK out. Here's a sample file: Ron_Lists.xls http://s48.yousendit.com/d.aspx?id=0...92VWDX31OBQ5TY That link expires in 7 days or 25 downloads, whichever occurs first. Biff "Ron" wrote in message ... Biff, what I need the formula to do is as follows: Lets assume row 10 will list out the customers for driver B Then it will need to look up to see the first cell in Column B that has a value greater than zero and if this is true then look to see if column F has a value greater than zero and when both are true it will return the value in coulmn A. The net result will be all the values that meet both conditions listed out horizontlly for each driver without any blanks in the middle. Thanks Ron "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#6
|
|||
|
|||
Sorry Biff,
I am not familiar with Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER. Can you explain this for me? thanks "Biff" wrote: Hi! Based on your sample table these are the results I get: DriverB..........Albertson..........Max DriverC..........7-11 DriverD..........Cooper DriverE...........OV OK, here's the details: A1:F1 are the headers: Customer, DriverB, DriverC, DriverD, DriverE, XXX Used named ranges as follows: Customer =$A$2:$A$10 DriverB =$B$2:$B$10 DriverC =$C$2:$C$10 DriverD =$D$2:$D$10 DriverE =$E$2:$E$10 XXX =$F$2:$F$10 Extract data to another table: A15 = DriverB A16 = DriverC A17 = DriverD A18 = DriverE Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER in B15: =INDEX(Customer,SMALL(IF(INDIRECT($A15)<"",IF(XXX 0,ROW(INDIRECT("1:"&COUNTA(Customer))))),COLUMN(A :A))) Copy across to enough cells that will hold all possible matches. How many will that be? 5? 10? Then copy down to row 18. Cells that don't hold a match will return a #NUM! error. To hide those I used conditional formatting. Select the entire range of formulas, B15:??18 Goto FormatConditional Formatting Formula is: =ISERROR(B15) Set the font color to be the same as the background fill color OK out. Here's a sample file: Ron_Lists.xls http://s48.yousendit.com/d.aspx?id=0...92VWDX31OBQ5TY That link expires in 7 days or 25 downloads, whichever occurs first. Biff "Ron" wrote in message ... Biff, what I need the formula to do is as follows: Lets assume row 10 will list out the customers for driver B Then it will need to look up to see the first cell in Column B that has a value greater than zero and if this is true then look to see if column F has a value greater than zero and when both are true it will return the value in coulmn A. The net result will be all the values that meet both conditions listed out horizontlly for each driver without any blanks in the middle. Thanks Ron "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#7
|
|||
|
|||
Normally, after you type in a formula you hit the ENTER key.
For an array formula (a formula that operates on more than one element), instead of just hitting ENTER you must use the key combination of CTRL, SHIFT and ENTER. Type the formula. Hold down the CTRL and SHIFT keys, then hit ENTER. If done properly Excel with enclose the formula in squiggly braces: { } The squiggly braces denote an array. You must use the key combo to do this. You cannot just type them in. Note also that if you edit an existing array formula, you must re-enter it with the key combo. If you read some posts here you may see people referring to CSE which is short for CTRL,SHIFT,ENTER. Biff "Ron" wrote in message ... Sorry Biff, I am not familiar with Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER. Can you explain this for me? thanks "Biff" wrote: Hi! Based on your sample table these are the results I get: DriverB..........Albertson..........Max DriverC..........7-11 DriverD..........Cooper DriverE...........OV OK, here's the details: A1:F1 are the headers: Customer, DriverB, DriverC, DriverD, DriverE, XXX Used named ranges as follows: Customer =$A$2:$A$10 DriverB =$B$2:$B$10 DriverC =$C$2:$C$10 DriverD =$D$2:$D$10 DriverE =$E$2:$E$10 XXX =$F$2:$F$10 Extract data to another table: A15 = DriverB A16 = DriverC A17 = DriverD A18 = DriverE Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER in B15: =INDEX(Customer,SMALL(IF(INDIRECT($A15)<"",IF(XXX 0,ROW(INDIRECT("1:"&COUNTA(Customer))))),COLUMN(A :A))) Copy across to enough cells that will hold all possible matches. How many will that be? 5? 10? Then copy down to row 18. Cells that don't hold a match will return a #NUM! error. To hide those I used conditional formatting. Select the entire range of formulas, B15:??18 Goto FormatConditional Formatting Formula is: =ISERROR(B15) Set the font color to be the same as the background fill color OK out. Here's a sample file: Ron_Lists.xls http://s48.yousendit.com/d.aspx?id=0...92VWDX31OBQ5TY That link expires in 7 days or 25 downloads, whichever occurs first. Biff "Ron" wrote in message ... Biff, what I need the formula to do is as follows: Lets assume row 10 will list out the customers for driver B Then it will need to look up to see the first cell in Column B that has a value greater than zero and if this is true then look to see if column F has a value greater than zero and when both are true it will return the value in coulmn A. The net result will be all the values that meet both conditions listed out horizontlly for each driver without any blanks in the middle. Thanks Ron "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#8
|
|||
|
|||
I got it all working. One problem I can not figure out. I added another
range name where that column included the quantities. I then applied the same formula except changed the range names and I would get #N/A. Can I not just copy the formaul and make the changes and then CTRL SHIFT and ENTER? "Biff" wrote: Normally, after you type in a formula you hit the ENTER key. For an array formula (a formula that operates on more than one element), instead of just hitting ENTER you must use the key combination of CTRL, SHIFT and ENTER. Type the formula. Hold down the CTRL and SHIFT keys, then hit ENTER. If done properly Excel with enclose the formula in squiggly braces: { } The squiggly braces denote an array. You must use the key combo to do this. You cannot just type them in. Note also that if you edit an existing array formula, you must re-enter it with the key combo. If you read some posts here you may see people referring to CSE which is short for CTRL,SHIFT,ENTER. Biff "Ron" wrote in message ... Sorry Biff, I am not familiar with Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER. Can you explain this for me? thanks "Biff" wrote: Hi! Based on your sample table these are the results I get: DriverB..........Albertson..........Max DriverC..........7-11 DriverD..........Cooper DriverE...........OV OK, here's the details: A1:F1 are the headers: Customer, DriverB, DriverC, DriverD, DriverE, XXX Used named ranges as follows: Customer =$A$2:$A$10 DriverB =$B$2:$B$10 DriverC =$C$2:$C$10 DriverD =$D$2:$D$10 DriverE =$E$2:$E$10 XXX =$F$2:$F$10 Extract data to another table: A15 = DriverB A16 = DriverC A17 = DriverD A18 = DriverE Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER in B15: =INDEX(Customer,SMALL(IF(INDIRECT($A15)<"",IF(XXX 0,ROW(INDIRECT("1:"&COUNTA(Customer))))),COLUMN(A :A))) Copy across to enough cells that will hold all possible matches. How many will that be? 5? 10? Then copy down to row 18. Cells that don't hold a match will return a #NUM! error. To hide those I used conditional formatting. Select the entire range of formulas, B15:??18 Goto FormatConditional Formatting Formula is: =ISERROR(B15) Set the font color to be the same as the background fill color OK out. Here's a sample file: Ron_Lists.xls http://s48.yousendit.com/d.aspx?id=0...92VWDX31OBQ5TY That link expires in 7 days or 25 downloads, whichever occurs first. Biff "Ron" wrote in message ... Biff, what I need the formula to do is as follows: Lets assume row 10 will list out the customers for driver B Then it will need to look up to see the first cell in Column B that has a value greater than zero and if this is true then look to see if column F has a value greater than zero and when both are true it will return the value in coulmn A. The net result will be all the values that meet both conditions listed out horizontlly for each driver without any blanks in the middle. Thanks Ron "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
#9
|
|||
|
|||
Hi Biff,
We use your file, added one column in and rekey your formula in the cell but it doesn't work. it show"#N/A". the purpose of this new column is to tell the qty of that customer take. Can you show us the right way to do it? The final result should have the customer name and the qty they take that day. Please help! "Ron" wrote: I got it all working. One problem I can not figure out. I added another range name where that column included the quantities. I then applied the same formula except changed the range names and I would get #N/A. Can I not just copy the formaul and make the changes and then CTRL SHIFT and ENTER? "Biff" wrote: Normally, after you type in a formula you hit the ENTER key. For an array formula (a formula that operates on more than one element), instead of just hitting ENTER you must use the key combination of CTRL, SHIFT and ENTER. Type the formula. Hold down the CTRL and SHIFT keys, then hit ENTER. If done properly Excel with enclose the formula in squiggly braces: { } The squiggly braces denote an array. You must use the key combo to do this. You cannot just type them in. Note also that if you edit an existing array formula, you must re-enter it with the key combo. If you read some posts here you may see people referring to CSE which is short for CTRL,SHIFT,ENTER. Biff "Ron" wrote in message ... Sorry Biff, I am not familiar with Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER. Can you explain this for me? thanks "Biff" wrote: Hi! Based on your sample table these are the results I get: DriverB..........Albertson..........Max DriverC..........7-11 DriverD..........Cooper DriverE...........OV OK, here's the details: A1:F1 are the headers: Customer, DriverB, DriverC, DriverD, DriverE, XXX Used named ranges as follows: Customer =$A$2:$A$10 DriverB =$B$2:$B$10 DriverC =$C$2:$C$10 DriverD =$D$2:$D$10 DriverE =$E$2:$E$10 XXX =$F$2:$F$10 Extract data to another table: A15 = DriverB A16 = DriverC A17 = DriverD A18 = DriverE Enter this formula as an array with the key combo of CTRL,SHIFT,ENTER in B15: =INDEX(Customer,SMALL(IF(INDIRECT($A15)<"",IF(XXX 0,ROW(INDIRECT("1:"&COUNTA(Customer))))),COLUMN(A :A))) Copy across to enough cells that will hold all possible matches. How many will that be? 5? 10? Then copy down to row 18. Cells that don't hold a match will return a #NUM! error. To hide those I used conditional formatting. Select the entire range of formulas, B15:??18 Goto FormatConditional Formatting Formula is: =ISERROR(B15) Set the font color to be the same as the background fill color OK out. Here's a sample file: Ron_Lists.xls http://s48.yousendit.com/d.aspx?id=0...92VWDX31OBQ5TY That link expires in 7 days or 25 downloads, whichever occurs first. Biff "Ron" wrote in message ... Biff, what I need the formula to do is as follows: Lets assume row 10 will list out the customers for driver B Then it will need to look up to see the first cell in Column B that has a value greater than zero and if this is true then look to see if column F has a value greater than zero and when both are true it will return the value in coulmn A. The net result will be all the values that meet both conditions listed out horizontlly for each driver without any blanks in the middle. Thanks Ron "Biff" wrote: Hi! This (usually !) isn't too difficult but I would need to see a better representation of your table: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 Really can't figure that out! Biff "Ron" wrote in message ... We have a situation here as follows; For example in column A we have customers and column B-E are the four routes and column F will have a vlue be greater than zero if they are taking particular item. We want to summarize horizontally the customers who have a value greater than zero in column F, by each driver. So our net result will be a list for each driver that will contain only the customers on his route that have a value greater than zero in column F Example: A B C D E F safeway 2 albertson 3 1 cooper 4 1 ov 5 1 iga 6 7-11 7 1 max 8 1 chevron 9 esso 10 FINAL RESULT driver B max driver C albertson 7-11 driver D cooper driver E ov Basically I would like a seperate horizontal list for each driver which contains only the customers that are on his route (IE. have a value in the column for that particular driver) and have a value in column F. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return dynamic lists? | Excel Worksheet Functions | |||
Comparing multiple lists | Excel Worksheet Functions | |||
Comparing 2 Customer Lists to Identify Shared Customers | Excel Worksheet Functions | |||
How to delete duplicate records when I merge two lists (deleting . | Excel Worksheet Functions | |||
Aligning Two Lists in Excel | Excel Discussion (Misc queries) |