Return a text value where value is greater than 0

stuartmacdonald

New Member
Joined
May 26, 2009
Messages
48
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

ABCD
1Person_APerson_BPerson_C
2Example_A2050
3Example_B0155
4Example_C5520

<tbody>
</tbody>


Result I'm looking for

Person_A

AB
1Person_A
2Example_A20
3Example_C5
4

<tbody>
</tbody>

Person_B
AB
1Person_B
2Example_A5
3Example_B15
4Example_C5

<tbody>
</tbody>

Person_C
AB
1Person_C
2Example_B5
3Example_C20
4

<tbody>
</tbody>
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sheet: source

Row\Col
A​
B​
C​
D​
1​
Person_APerson_BPerson_C
2​
Example_A
20​
5​
0​
3​
Example_B
0​
15​
5​
4​
Example_C
5​
5​
20​
5​

Define Lrow in the Name Manager as referring to:
Rich (BB code):
=MATCH(REPT("z",255),source!$A:$A)

Define projects as referring to:
Rich (BB code):
=source!$A$2:INDEX(source!$A:$A,Lrow)

Define data as referring to:
Rich (BB code):
=source!$B$2:INDEX(source!$D:$D,Lrow)

Sheet: person_a

Row\Col
A​
B​
1​
person_a
2​
2​
Example_A
20​
3​
Example_C
5​
4​

In B1 just enter:
Rich (BB code):
=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:
Rich (BB code):
=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:
Rich (BB code):
=IF($A2="","",VLOOKUP($A2,source!$A:$D,MATCH($A$1,INDEX(source!$A:$D,1,0),0)))
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top