FlowersinExcel
New Member
- Joined
- Dec 6, 2019
- Messages
- 16
- Office Version
- 2016
- Platform
- Windows
Hi there,
I am creating a spreadsheet that adds rows of information for every non-zero 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 auto-populate 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 non-zero 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 auto-populate 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!
