Concatenating results of Conditional statement

E

Emily Warren

Can anyone figure out how to write a formula for the following
problem?

WORKSHEET 1:
Column A = Project Title
Column B = Role

Each project may have multiple rows if a staff member works on the
project in different roles.

WORKSHEET 2:

Concatenate role names found via conditional statement

Column A Column B

Project 1 Project Manager
Project 1 Developer
Project 2
Project 3 Business Anyalyst

I'd like to take any/all roles performed on a certain project and
concatenate them in a cell on another worksheet.
 
A

Ashish Mathur

Hi,

The question is not cleat. What is the purpose of worksheet 1. Be clear
about the questions and please post before/after examples

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
E

Emily Warren

Okay, what I would like to do is take any roles worked on a certain
project from each of our staff members' timesheets and concatenate the
names of those roles in a cell on another worksheet. So for example,
Amy's timesheet would include a row for Project A with the role
"Developer" and another row for Project A with the role "Team". I
would like these to have these roles show up in a cell next to Amy's
name under the Project A section. It would look like this: "Developer,
Team". Example of how I'd like it to look:

Worksheet 1: Amy's timesheet

Column A Column B

PROJECT ROLE
Project A Developer
Project A Team
Project B
Project C Business Anyalyst

Worksheet 2: All staff hours worked on a given project

Column A Column B

PROJECT ROLE
Project A Developer, Team

Hope this is clear enough. Let me know if you need more information.
 
E

Emily Warren

Hi,

Download and install the following addin -http://www.download.com/Morefunc/3000-2077_4-10423159.html.  Thereafter you
may use the following array formula (Ctrl+Shift+Enter) in cell E5

=SUBSTITUTE(MCONCAT(IF(Sheet1!$B$4:$B$7=D5,Sheet1!C4:C7),","),",FALSE","")

B4:B7 of sheet1 has the project titles
C4:C7 of sheet 1 has the Roles
D5 of sheet2 has the project title

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com













- Show quoted text -
Click to expand...

Thanks very much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function with unknown quantities? 1
Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
Copying from one sheet to another 7
Nesting MATCH functions? Or other solution? 2
Formulas appear in cell instead of formula result 3
How do I count instances of a reference? 1
looking up multiple values in an arrray 5
Linking Spreadsheets 1

Top