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.