I am not sure which excel function to use.

I need to create a formula that looks for a # in a cell and returns a
corresponding person's initials. If there are multiple occurences I need to
concatenate the initials into one cell separated by a comma.

In row 1, i have the person initials
In row 2, I have the #'s
In row 3, the formula

Therefore,

A B C D E
1 PH IS CF MS AS Row 1

2 # # # Row 2

The formula result in row 3 should be PH,CF,AS

Can anyone help?
--
J

In A3: =IF(A2="#",A1,"")
Copy A3 to E3

Then in F3:
=SUBSTITUTE(TRIM(A3&" "&B3&" "&C3&" "&D3&" "&E3)," ",",")
will return the required concat result
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"James" wrote:
I am not sure which excel function to use.

I need to create a formula that looks for a # in a cell and returns a
corresponding person's initials. If there are multiple occurences I need to
concatenate the initials into one cell separated by a comma.

In row 1, i have the person initials
In row 2, I have the #'s
In row 3, the formula

Therefore,

A B C D E
1 PH IS CF MS AS Row 1

2 # # # Row 2

The formula result in row 3 should be PH,CF,AS

Can anyone help?
--
J

Max,

That is absolutely brilliant. Works like a dream. Thank you so much.

Is it a formula that will automatically update itself?
--
J


"Max" wrote:

In A3: =IF(A2="#",A1,"")
Copy A3 to E3

Then in F3:
=SUBSTITUTE(TRIM(A3&" "&B3&" "&C3&" "&D3&" "&E3)," ",",")
will return the required concat result
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"James" wrote:
I am not sure which excel function to use.

I need to create a formula that looks for a # in a cell and returns a
corresponding person's initials. If there are multiple occurences I need to
concatenate the initials into one cell separated by a comma.

In row 1, i have the person initials
In row 2, I have the #'s
In row 3, the formula

Therefore,

A B C D E
1 PH IS CF MS AS Row 1

2 # # # Row 2

The formula result in row 3 should be PH,CF,AS

Can anyone help?
--
J

Is it a formula that will automatically update itself?

All formulas update automatically, unless the calc mode is set
to/inadvertently at manual mode. Perhaps check the calc mode via clicking:
Tools Options Calculation tab. Ensure "Automatic" is selected OK

Take a moment to click the "Yes" button from where you're reading this
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"James" wrote:
Max,

That is absolutely brilliant. Works like a dream. Thank you so much.

Is it a formula that will automatically update itself?
--
J