List
 

I have a worksheet such as below:

bob 2 1
pat 3
sam 4 3
ron 5 5
don 6
jus 7 6
rik 8 6

In column A I have names and in column B and C we have values. I would like
to create a list going horizonatly that only includes the names in Column A
that have values in both column B and C.

So my net result should loook like this:
Bob sam ron jus rik

Thanks

The easiest way would be to use datafilteradvanced filter, assume the
original table is in A1:C8 (with headers), in for example H2 (H1 should be
empty) put

=AND(B2<"",C2<"")

where B2 is the first value in the second column and C2 in the third column

apply filter and copy to another location (select the cell from the dialogue
box),
then in the criteria range use

$H$1:$H$2

for list range use

$A$1:$C$8

click OK

That will give you a new list wth the values that did pass the filter, then
just select the names and copy them. select an empty cell and do editpaste
special and transpose. Of course I assume the the real table is much larger

Regards,

Peo Sjoblom


"Pong" wrote:

I have a worksheet such as below:

bob 2 1
pat 3
sam 4 3
ron 5 5
don 6
jus 7 6
rik 8 6

In column A I have names and in column B and C we have values. I would like
to create a list going horizonatly that only includes the names in Column A
that have values in both column B and C.

So my net result should loook like this:
Bob sam ron jus rik

Thanks

You could turn on the autofilter Data/Filter/Autofilter. filter columns B
and C using "nonblanks".

then in another worksheet hightlight enough cells to accomodate the number
of names in your filtered list (your example shows 5 names, so select a range
that is 1 row by 5 columns). Enter

=TRANSPOSE(A1:A4) where A1:A4 is the range that corresponds to the names
showing in your filtered list. Hit Control+Shift+Enter.

Now you can hardcode your names by selecting copy, then
Edit/PasteSpecial/Values.

Also, make sure your original list has headers, otherwise I think Autofilter
will skip your first line of data.


"Pong" wrote:

I have a worksheet such as below:

bob 2 1
pat 3
sam 4 3
ron 5 5
don 6
jus 7 6
rik 8 6

In column A I have names and in column B and C we have values. I would like
to create a list going horizonatly that only includes the names in Column A
that have values in both column B and C.

So my net result should loook like this:
Bob sam ron jus rik

Thanks