Lists

[Ron]

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.

[Biff]

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" <Ron@discussions.microsoft.com> wrote in message
news:D3BF19E4-9109-4204-9F83-3744D176C79A@microsoft.com...
> 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.

[Ron]

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" <Ron@discussions.microsoft.com> wrote in message
> news:D3BF19E4-9109-4204-9F83-3744D176C79A@microsoft.com...
> > 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.
>
>
>

[Ron]

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" <Ron@discussions.microsoft.com> wrote in message
> news:D3BF19E4-9109-4204-9F83-3744D176C79A@microsoft.com...
> > 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.
>
>
>

[Biff]

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 Format>Conditional 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" <Ron@discussions.microsoft.com> wrote in message
news:1CD5DA46-EA0B-4273-8992-6D5D2B335E6B@microsoft.com...
> 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" <Ron@discussions.microsoft.com> wrote in message
>> news:D3BF19E4-9109-4204-9F83-3744D176C79A@microsoft.com...
>> > 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.
>>
>>
>>

[Ron]

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 Format>Conditional 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" <Ron@discussions.microsoft.com> wrote in message
> news:1CD5DA46-EA0B-4273-8992-6D5D2B335E6B@microsoft.com...
> > 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" <Ron@discussions.microsoft.com> wrote in message
> >> news:D3BF19E4-9109-4204-9F83-3744D176C79A@microsoft.com...
> >> > 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.
> >>
> >>
> >>
>
>
>

[Biff]

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" <Ron@discussions.microsoft.com> wrote in message
news:434D672A-5828-4F39-BF69-136BB77F637F@microsoft.com...
> 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 Format>Conditional 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" <Ron@discussions.microsoft.com> wrote in message
>> news:1CD5DA46-EA0B-4273-8992-6D5D2B335E6B@microsoft.com...
>> > 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" <Ron@discussions.microsoft.com> wrote in message
>> >> news:D3BF19E4-9109-4204-9F83-3744D176C79A@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>