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.