Macro or formula to populate a row in one sheet only if data entered in another is non-zero

[FlowersinExcel]

Hi there,
I am trying to create a spreadsheet that will automatically add rows of information for every cell with info in another sheet. In the first sheet, which is the Input sheet, I enter where the person'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 Dollars, with an entry 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. Is there a formula or VBA that will help me auto-populate the output tab?
Here is an example of my simple input:

Mr.Excel question.xlsx
	A	B	C	D	E	F	G	H
1		Program 1	Program 2	Program 3	Program 4	Program 5	TOTAL	Actual spreadsheet has 50 columns of data
2	Name 1	0.3	0.4				0.7
3	Name 2			1			1
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	…
8	Actual spreadsheet has 140 employees
Input

Cell Formulas
Range	Formula
G2:G6	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	0.00	0.00	200.00	0.00	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	…
8	Actual spreadsheet has 140 employees
Calculation

Cell Formulas
Range	Formula
C1:H1	C1	=Input!B1
C2:G6	C2	=$B2*(Input!B2/Input!$G2)
A2:A8	A2	=Input!A2

And then the final output, this is where I need help:

Mr.Excel question.xlsx
	A	B	C
1	Name	Amount	Program
2	Name 1	42.86	1
3	Name 1	57.14	2
4	Name 2	200.00	3
5	Name 3	45.00	1
6	Name 3	60.00	2
7	Name 3	150.00	3
8	Name 3	45.00	4
9	Name 4	320.00	1
10	Name 4	69.33	2
11	Name 4	10.67	4
12	Name 5	500.00	5
Output

Cell Formulas
Range	Formula
B2	B2	=Calculation!C2
B3	B3	=Calculation!D2
B4	B4	=Calculation!E3
B5	B5	=Calculation!C4
B6	B6	=Calculation!D4
B7	B7	=Calculation!E4
B8	B8	=Calculation!F4
B9	B9	=Calculation!C5
B10	B10	=Calculation!D5
B11	B11	=Calculation!F5
B12	B12	=Calculation!G6

To complicate this, programs people work for change, so the formulas need to be able to change too (I thought about index(match,match), or v&hlookups, but I wouldn't know how to make those work here).
Thank you so much for your help!

 

[Dossfm0q]

Fulff Method

Transpose every x amount of columns to rows, keeping cell of first column on each row

What I try to achieve is to convert my rows to columns, but a bit different that mostly done It has to be chopped in to 3 or 4 columns that has to be moved to a row on a new sheet, but each row needs to has to start with the first cell of the original row. Hard to explane but I think these...

www.mrexcel.com

Sub jerxjac()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long

   Ary = Worksheets("Calculation").Range("A2").CurrentRegion.Value
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 5)

   For r = 2 To UBound(Ary)
      For c = 3 To 8
         If Ary(r, c) <> 0 Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            For nc = 2 To 2
               Nary(nr, nc) = Ary(r, c + nc - 2)
            Next nc
         End If
      Next c
   Next r
   Sheets("Output").Range("A2").Resize(nr, 3).Value = Nary
End Sub

 

[Dossfm0q]

what About

Ignoring Zero.xlsx
	A	B	C
1	Name	Amount	Program
2	Name 1	42.86	1
3	Name 1	57.14	2
4	Name 2	200.00	1
5	Name 3	45.00	1
6	Name 3	60.00	2
7	Name 3	150.00	3
8	Name 3	45.00	4
9	Name 4	320.00	1
10	Name 4	69.33	2
11	Name 4	10.67	3
12	Name 5	500.00	1
Output

Cell Formulas
Range	Formula
A2:A12	A2	=INDEX(Calculation!$A$2:$A$6,CEILING(AGGREGATE(15,6,((COLUMN(Calculation!$C$2:$G$6)-COLUMN(Calculation!$C$2))+((ROW(Calculation!$C$2:$G$6)-ROW(Calculation!$C$2))*COLUMNS(Calculation!$C$2:$G$6)+1))/--(Calculation!$C$2:$G$6<>0),ROWS($A$2:A2)),COLUMNS(Calculation!$C$2:$G$6))/COLUMNS(Calculation!$C$2:$G$6),1)
B2:B12	B2	=INDEX(Calculation!$C$2:$G$6,CEILING(AGGREGATE(15,6,((COLUMN(Calculation!$C$2:$G$6)-COLUMN(Calculation!$C$2))+((ROW(Calculation!$C$2:$G$6)-ROW(Calculation!$C$2))*COLUMNS(Calculation!$C$2:$G$6)+1))/--(Calculation!$C$2:$G$6<>0),ROWS($B$2:B2)),COLUMNS(Calculation!$C$2:$G$6))/COLUMNS(Calculation!$C$2:$G$6),MOD(AGGREGATE(15,6,((COLUMN(Calculation!$C$2:$G$6)-COLUMN(Calculation!$C$2))+((ROW(Calculation!$C$2:$G$6)-ROW(Calculation!$C$2))*COLUMNS(Calculation!$C$2:$G$6)+1))/--(Calculation!$C$2:$G$6<>0),ROWS($B$2:B2))-1,COLUMNS(Calculation!$C$2:$G$6))+1)
C2:C12	C2	=COUNTIF($A$2:A2,A2)

 

[FlowersinExcel]

Thank you SO MUCH for this! I apologize for the long delay in my response; we had some internal changes that put this project on hold for a while but now I'm back at it. I tried the formulas instead of the macro, and it mostly worked - thank you! The formulas for pulling the name and the amount worked, but not the one for pulling the program. I imagine i'd need another Index Ceiling type formula for that? Or I might try it with just an index match type formula...