Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
LOOP - Adddress List -to email | Excel Discussion (Misc queries) | |||
Refresh a Validation List? | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Creating a list from an existing list. | Excel Worksheet Functions |