Formula to populate a column for every non-zero cell in a data set

FlowersinExcel

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
  1. 2016
Platform
  1. 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:
Mr.Excel question.xlsx
ABCDEFG
1Program 1Program 2Program 3Program 4Program 5TOTAL
2Name 10.30.40.7
3Name 20.10.150.10.50.85
4Name 30.150.20.50.151
5Name 40.60.130.020.75
6Name 50.50.5
7Name 611
8Name 70.20.50.7
9Name 81315
Input
Cell Formulas
RangeFormula
G2:G9G2=SUM(B2:F2)


And my simple calculation
Cell Formulas
RangeFormula
C1:H1C1=Input!B1
C2:G9C2=$B2*(Input!B2/Input!$G2)
A2:A9A2=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
ABC
1NameAmountProgram
2Name 142.86Program 1
3Name 157.14Program 2
4Name 223.53Program 1
5Name 235.29Program 2
6Name 223.53Program 3
7Name 2117.65Program 4
8Name 345.00Program 1
9Name 360.00Program 2
10Name 3150.00Program 3
11Name 345.00Program 4
12Name 4320.00Program 1
13Name 469.33Program 2
14Name 410.67Program 4
15Name 5500.00etc…
16Name 6600.00
17Name 7200.00
18Name 7500.00
19Name 8160.00
20Name 8480.00
21Name 8160.00
Output
Cell Formulas
RangeFormula
A2:A21A2=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:B21B2=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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
The formula below worked with your data sample (only 5 programs). Adjust the ranges for your real case.

C2 copied down
=INDEX(Calculation!$C$1:$G$1,AGGREGATE(15,6,(COLUMN(Calculation!$C$1:$G$1)-COLUMN(Calculation!$C$1)+1)/(INDEX(Calculation!$C$2:$G$9,MATCH(A2,Calculation!$A$2:$A$9,0),0)=B2),COUNTIFS(A$2:A2,A2,B$2:B2,B2)))

M.
 

FlowersinExcel

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You're welcome. Glad to help :)

M.
@Marcelo Branco, I may still need your help. I've tried to transfer these formulas to the actual spreadsheet, rather than the mock version I shared here, and two things are happening.
One, whenever I press enter if I'm in the formula bar, it opens my windows file explorer with a cue to update values (i.e., the files explorer pop-up says Update Values in the top left corner where it would normally say Save As, for example, if I had created a pop-up by pressing the Save As button).

And two, I get #NAME error. So, I might need to understand this formula better in able to make it work in my actual spreadsheet. Do you see anything obviously wrong that I am doing? Here's my transferred formula, which appears in cell C2 in of the real output sheet, where wage allocation is another sheet in the workbook (instead of the Calculation sheet in my mock-up):

=INDEX('wage allocation'!$C$4:$CE$300,CEILING(AGGREGATE(15,6,((COLUMN(‘wage allocation’!$C$4:$CE$300)-COLUMN(‘wage allocation’!$C$4))+((ROW(‘wage allocation’!$C$4:$CE$300)-ROW(‘wage allocation’!$C$4))*COLUMNS(‘wage allocation’!$C$4:$CE$300)+1))/--(‘wage allocation’!$C$4:$CE$300<>0),ROWS($C$2:C2)),COLUMNS(‘wage allocation’!$C$4:$CE$300))/COLUMNS(‘wage allocation’!$C$4:$CE$3000),MOD(AGGREGATE(15,6,((COLUMN(‘wage allocation’!$C$4:$CE$300)-COLUMN(‘wage allocation’!$C$4))+((ROW(‘wage allocation’!$C$4:$CE$300)-ROW(‘wage allocation’!$C$4))*COLUMNS(‘wage allocation’!$C$4:$CE$300)+1))/--(‘wage allocation’!$C$4:$CE$300<>0),ROWS($C$2:C2))-1,COLUMNS(‘wage allocation’!$C$4:$CE$300))+1)

What am I doing wrong?
Thanks so much for your help!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I'm afraid I can't help you. The formula you are using is completely different from the formula I suggested. In your formula I noticed some Excel functions that don't appear in mine, like CEILING, MOD and then I couldn't understand the logic of your formula.

I probably didn't understand your problem or the real case is very different from the one described in post #1.

M.
 

FlowersinExcel

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I'm afraid I can't help you. The formula you are using is completely different from the formula I suggested. In your formula I noticed some Excel functions that don't appear in mine, like CEILING, MOD and then I couldn't understand the logic of your formula.

I probably didn't understand your problem or the real case is very different from the one described in post #1.

M.
Good point. Those were from another thread, so I'll go ask there. Thanks for the quick reply, though - you are still very helpful!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I think i understand you problem - you are using very complicated formulas to get the values in Columns A and B of the sheet Output

I suggest something like this

Sheet Input is OK

On sheet wage calculation use a helper column (observe the gray area) to make the formulas simpler
J1 copied down until the last name
=COUNTIF(C$1:G1,">0")

Cell Formulas
RangeFormula
C1:H1C1=Input!B1
C2:G9C2=$B2*(Input!B2/Input!$G2)
A2:A9A2=Input!A2
J1:J9J1=COUNTIF(C$1:G1,">0")


On sheet Output try this - note the formulas in columns A, B and C

Cell Formulas
RangeFormula
B2:B21B2=INDEX(INDEX('wage calculation'!C$2:G$9,MATCH(A2,'wage calculation'!$A$2:$A$9,0),0),AGGREGATE(15,6,(COLUMN('wage calculation'!C$1:G$1)-COLUMN('wage calculation'!C$1)+1)/(INDEX('wage calculation'!C$2:G$9,MATCH(A2,'wage calculation'!A$2:A$9,0),0)<>0),COUNTIF(A$2:A2,A2)))
C2:C21C2=INDEX('wage calculation'!$C$1:$G$1,AGGREGATE(15,6,(COLUMN('wage calculation'!$C$1:$G$1)-COLUMN('wage calculation'!$C$1)+1)/(INDEX('wage calculation'!$C$2:$G$9,MATCH(A2,'wage calculation'!$A$2:$A$9,0),0)=B2),COUNTIFS(A$2:A2,A2,B$2:B2,B2)))
A2:A22A2=IF(ROWS(A$2:A2)>'wage calculation'!$J$9,"",INDEX('wage calculation'!A$2:A$9,MATCH(ROWS(A$2:A2)-1,'wage calculation'!$J$1:$J$9,1)))


Be sure the sheet names are correct
Try to adapt this solution to your real case.

M.
 
Solution

FlowersinExcel

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I tried it and it's working! I have a harder adaptation that I'll try tomorrow afternoon, but the first easy adaptation worked beautifully. THANK YOU! Thank you thank you thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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
Top