Return a text value where value is greater than 0

[stuartmacdonald]

I have a table of projects and a list of staff who have days allocated to each.
I'm now looking to develop tables of all the projects each person is working on that can be used for other purposes.
I need these tables to update as new projects are added to the source table.
I started out with Sumproduct, but can't get it to return text, and have been playing around with Index & Match, but cannot get it working...
Any ideas....

Source table

[TABLE="width: 337"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Person_A[/TD]
[TD]Person_B[/TD]
[TD]Person_C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Example_A[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Example_B[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Example_C[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


Result I'm looking for

Person_A
[TABLE="width: 209"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Person_A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Example_A[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Example_C[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Person_B
[TABLE="width: 209"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Person_B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Example_A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Example_B[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Example_C[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Person_C
[TABLE="width: 209"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Person_C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Example_B[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Example_C[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Sheet: source

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]Person_A[/td][td]Person_B[/td][td]Person_C[/td][/tr]
[tr][td]

2​

[/td][td]Example_A[/td][td]

20​

[/td][td]

5​

[/td][td]

0​

[/td][/tr]
[tr][td]

3​

[/td][td]Example_B[/td][td]

0​

[/td][td]

15​

[/td][td]

5​

[/td][/tr]
[tr][td]

4​

[/td][td]Example_C[/td][td]

5​

[/td][td]

5​

[/td][td]

20​

[/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Define Lrow in the Name Manager as referring to:

=MATCH(REPT("z",255),source!$A:$A)

Define projects as referring to:

=source!$A$2:INDEX(source!$A:$A,Lrow)

Define data as referring to:

=source!$B$2:INDEX(source!$D:$D,Lrow)

Sheet: person_a

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]person_a[/td][td]

2​

[/td][/tr]
[tr][td]

2​

[/td][td]Example_A[/td][td]

20​

[/td][/tr]
[tr][td]

3​

[/td][td]Example_C[/td][td]

5​

[/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][/tr]
[/table]

In B1 just enter:

=COUNTIFS(INDEX(source!B:D,0,MATCH(A1,INDEX(source!B:D,1,0),0)),">0")

In A2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$2:A2)>$B$1,"",INDEX(projects,SMALL(IF(INDEX(data,0,MATCH($A$1,INDEX(source!$B:$D,1,0),0))>0,
    ROW(projects)-ROW(INDEX(projects,1,1))+1),ROWS($A$2:A2))))

In B2 just enter and copy down:

=IF($A2="","",VLOOKUP($A2,source!$A:$D,MATCH($A$1,INDEX(source!$A:$D,1,0),0)))