Multiple values in one cell

S

Sharon Mann

OK, I'm a nurse new to Excel and here's what I need to do:

On one worksheet I've got a range of cells that form a table where a phone
person puts an "X" in the second column if that row applies to the person
she's talking to. I'll use "Mental Status" as an example:


Alert x
Oriented x
Forgetful
Confused
Agitated
Depressed x



Now on another worksheet (that will be a form taken out into the field) I
have a cell with the text "Mental Status:"
What I want to do is have the cell just to the right of this be populated
with all the words from the first table that have an "X" marked by them.

In other words, in the example, it would look like:

Mental Status: Alert Oriented Depressed

So what formula can I use to accomplish this?

Thanks so much,
Sharon
 
P

pdberger

Sharon --
By Monday, a power user will come along with a more elegant solution,
probably using an array formula. Just in case they don't here's a "brute
force" approach that I tested, so I know it works:

A B
1 Alert, X
2 Anxious,
3 Obnoxious,
4 Belligerent, X

Here's the complicated, boring part. Remember to keep track of the
parentheses, and note that you'll see a lot of double quotation marks - "".
These mean that the formula is either finding nothing or putting nothing into
a cell.

A B
10 Mental Status: =concatenate(=if(B1<>"",A1,""),if(b2<>"",A2,""))

This formula says, "If you find anything in cell B1, get what you find in
cell A1. Do the same with B2 and A2. Concatenate the two of them (which is
why I put a comma & space after each in cells A1 and A2). Take this long
sequence of text and put it in cell B10." You'd continue with B3, B4, B5, to
get all the comments you want to make.

Again, I'm SURE there's an easier way, I just don't know it.

HTH

Peter
 
R

Ron Rosenfeld

OK, I'm a nurse new to Excel and here's what I need to do:

On one worksheet I've got a range of cells that form a table where a phone
person puts an "X" in the second column if that row applies to the person
she's talking to. I'll use "Mental Status" as an example:


Alert x
Oriented x
Forgetful
Confused
Agitated
Depressed x



Now on another worksheet (that will be a form taken out into the field) I
have a cell with the text "Mental Status:"
What I want to do is have the cell just to the right of this be populated
with all the words from the first table that have an "X" marked by them.

In other words, in the example, it would look like:

Mental Status: Alert Oriented Depressed

So what formula can I use to accomplish this?

Thanks so much,
Sharon
Click to expand...

Assumption:

Your mental status conditions and 'x's are in Sheet1!A1:B6

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Try the following **array** formula. (To enter an array-formula, after
typing or pasting it into the formula bar, hold down <ctrl><shift> while
hitting <enter>. Excel will place braces {...} around the formula):

=TRIM(MCONCAT(IF(Sheet1!B1:B6="x",Sheet1!A1:A6,"")," "))


--ron
 
Top