Help!!!

[ag22]

Hi,

Once again, I need your expertise. The sheet 1 contains the data. On the the sheet 2, I need to fill up the information on Project column. For example, Christan = Project A, Donna = Project A and Project B. What would be the formula to do this action? Thanks again.

Sheet 1
Project A Project B
Donna Shiela
Neil Sharon
Christian Ogie
Mel Neil
Larry Donna
Shiela Jose
Jerry Rhian


Sheet 2
Name Project
Christian
Donna
Jerry
Jose
Larry
Mel
Neil
Ogie
Rhian
Sharon
Shiela

 

[Domenic]

Assumptions...

Sheet1!A2:B8 contains the source data

Sheet2!A2:A12 contains the llist of names

Formula..

Sheet2!B2, copied down and over to Column C:

=IF(COLUMNS($B2:B2)<=COUNTIF(Sheet1!$A$2:$B$8,$A2),INDEX(Sheet1!$A$1:$B$1,SMALL(IF(Sheet1!$A$2:$B$8=$A2,COLUMN(Sheet1!$A$2:$B$8)-COLUMN(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[pgc01]

Hi ag22

Another solution

In Sheet2!B2

=SUBSTITUTE(IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),"Project A","")&IF(ISNUMBER(MATCH(A2,Sheet1!B:B,0)),"Project B",""),"APr","A and Pr")

Copy down

Hope this helps

Book1
	A	B	C	D
1	ProjectA	ProjectB
2	Donna	Shiela
3	Neil	Sharon
4	Christian	Ogie
5	Mel	Neil
6	Larry	Donna
7	Shiela	Jose
8	Jerry	Rhian
9
Sheet1

Book1
	A	B	C	D
1	Name	Project
2	Christian	ProjectA
3	Donna	ProjectAandProjectB
4	Jerry	ProjectA
5	Jose	ProjectB
6	Larry	ProjectA
7	Mel	ProjectA
8	Neil	ProjectAandProjectB
9	Ogie	ProjectB
10	Rhian	ProjectB
11	Sharon	ProjectB
12	Shiela	ProjectAandProjectB
13
14
Sheet2