FlowersinExcel
New Member
 Joined
 Dec 6, 2019
 Messages
 11
 Office Version

 2016
 Platform

 Windows
Hi there,
I am creating a spreadsheet that adds rows of information for every nonzero cell in a data set on another sheet. I have an Input sheet where I enter how each employee's time is allocated (across 50 potential programs, or columns). That is then converted into dollar amounts in the Calculation sheet, and then in the Output sheet I have to turn it into columns of Name, Program, and Amount, with one unique row for every allocation. So if a person works in three programs, they get three rows in this Output tab, listing different programs & amounts. Right now I do this manually, painfully, and it takes ages. With help from a Mr.Excel guru I found formulas to populate the Name & Amounts, but not one to populate the Program column. Is there a formula that will help me autopopulate that?
Here is an example of my simple input:
And my simple calculation
And the final output tab, where the Name & Amount columns have formulas but the Program column is still manually entered...this is where I need help with a formula.
I thought about using V/HLOOKUP formulas, but the values aren't always unique, because a person who spend 50% of their time in Program 1 and 50% in Program 2 will have repeat values.
I appreciate any ideas you all have  thanks so much!
I am creating a spreadsheet that adds rows of information for every nonzero cell in a data set on another sheet. I have an Input sheet where I enter how each employee's time is allocated (across 50 potential programs, or columns). That is then converted into dollar amounts in the Calculation sheet, and then in the Output sheet I have to turn it into columns of Name, Program, and Amount, with one unique row for every allocation. So if a person works in three programs, they get three rows in this Output tab, listing different programs & amounts. Right now I do this manually, painfully, and it takes ages. With help from a Mr.Excel guru I found formulas to populate the Name & Amounts, but not one to populate the Program column. Is there a formula that will help me autopopulate that?
Here is an example of my simple input:
Mr.Excel question.xlsx  

A  B  C  D  E  F  G  
1  Program 1  Program 2  Program 3  Program 4  Program 5  TOTAL  
2  Name 1  0.3  0.4  0.7  
3  Name 2  0.1  0.15  0.1  0.5  0.85  
4  Name 3  0.15  0.2  0.5  0.15  1  
5  Name 4  0.6  0.13  0.02  0.75  
6  Name 5  0.5  0.5  
7  Name 6  1  1  
8  Name 7  0.2  0.5  0.7  
9  Name 8  1  3  1  5  
Input 
Cell Formulas  

Range  Formula  
G2:G9  G2  =SUM(B2:F2) 
And my simple calculation
Mr.Excel question.xlsx  

A  B  C  D  E  F  G  H  
1  Salary  Program 1  Program 2  Program 3  Program 4  Program 5  TOTAL  
2  Name 1  100  42.86  57.14  0.00  0.00  0.00  
3  Name 2  200  23.53  35.29  23.53  117.65  0.00  
4  Name 3  300  45.00  60.00  150.00  45.00  0.00  
5  Name 4  400  320.00  69.33  0.00  10.67  0.00  
6  Name 5  500  0.00  0.00  0.00  0.00  500.00  
7  Name 6  600  600.00  0.00  0.00  0.00  0.00  
8  Name 7  700  0.00  200.00  500.00  0.00  0.00  
9  Name 8  800  160.00  480.00  0.00  160.00  0.00  
Calculation 
Cell Formulas  

Range  Formula  
C1:H1  C1  =Input!B1 
C2:G9  C2  =$B2*(Input!B2/Input!$G2) 
A2:A9  A2  =Input!A2 
And the final output tab, where the Name & Amount columns have formulas but the Program column is still manually entered...this is where I need help with a formula.
Mr.Excel question.xlsx  

A  B  C  
1  Name  Amount  Program  
2  Name 1  42.86  Program 1  
3  Name 1  57.14  Program 2  
4  Name 2  23.53  Program 1  
5  Name 2  35.29  Program 2  
6  Name 2  23.53  Program 3  
7  Name 2  117.65  Program 4  
8  Name 3  45.00  Program 1  
9  Name 3  60.00  Program 2  
10  Name 3  150.00  Program 3  
11  Name 3  45.00  Program 4  
12  Name 4  320.00  Program 1  
13  Name 4  69.33  Program 2  
14  Name 4  10.67  Program 4  
15  Name 5  500.00  etc…  
16  Name 6  600.00  
17  Name 7  200.00  
18  Name 7  500.00  
19  Name 8  160.00  
20  Name 8  480.00  
21  Name 8  160.00  
Output 
Cell Formulas  

Range  Formula  
A2:A21  A2  =INDEX(Calculation!$A$2:$A$60,CEILING(AGGREGATE(15,6,((COLUMN(Calculation!$C$2:$BX$60)COLUMN(Calculation!$C$2))+((ROW(Calculation!$C$2:$BX$60)ROW(Calculation!$C$2))*COLUMNS(Calculation!$C$2:$BX$60)+1))/(Calculation!$C$2:$BX$60<>0),ROWS($A$2:A2)),COLUMNS(Calculation!$C$2:$BX$60))/COLUMNS(Calculation!$C$2:$BX$60),1) 
B2:B21  B2  =INDEX(Calculation!$C$2:$BX$60,CEILING(AGGREGATE(15,6,((COLUMN(Calculation!$C$2:$BX$60)COLUMN(Calculation!$C$2))+((ROW(Calculation!$C$2:$BX$60)ROW(Calculation!$C$2))*COLUMNS(Calculation!$C$2:$BX$60)+1))/(Calculation!$C$2:$BX$60<>0),ROWS($B$2:B2)),COLUMNS(Calculation!$C$2:$BX$60))/COLUMNS(Calculation!$C$2:$BX$60),MOD(AGGREGATE(15,6,((COLUMN(Calculation!$C$2:$BX$60)COLUMN(Calculation!$C$2))+((ROW(Calculation!$C$2:$BX$60)ROW(Calculation!$C$2))*COLUMNS(Calculation!$C$2:$BX$60)+1))/(Calculation!$C$2:$BX$60<>0),ROWS($B$2:B2))1,COLUMNS(Calculation!$C$2:$BX$60))+1) 
I thought about using V/HLOOKUP formulas, but the values aren't always unique, because a person who spend 50% of their time in Program 1 and 50% in Program 2 will have repeat values.
I appreciate any ideas you all have  thanks so much!