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

FlowersinExcel

New Member
Joined
Dec 6, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGH
1Program 1Program 2Program 3Program 4Program 5TOTALActual spreadsheet has 50 columns of data
2Name 10.30.40.7
3Name 211
4Name 30.150.20.50.151
5Name 40.60.130.020.75
6Name 50.50.5
7
8Actual spreadsheet has 140 employees
Input
Cell Formulas
RangeFormula
G2:G6G2=SUM(B2:F2)

And my simple calculation
Cell Formulas
RangeFormula
C1:H1C1=Input!B1
C2:G6C2=$B2*(Input!B2/Input!$G2)
A2:A8A2=Input!A2

And then the final output, this is where I need help:
Mr.Excel question.xlsx
ABC
1NameAmountProgram
2Name 142.861
3Name 157.142
4Name 2200.003
5Name 345.001
6Name 360.002
7Name 3150.003
8Name 3 45.004
9Name 4320.001
10Name 469.332
11Name 410.674
12Name 5500.005
Output
Cell Formulas
RangeFormula
B2B2=Calculation!C2
B3B3=Calculation!D2
B4B4=Calculation!E3
B5B5=Calculation!C4
B6B6=Calculation!D4
B7B7=Calculation!E4
B8B8=Calculation!F4
B9B9=Calculation!C5
B10B10=Calculation!D5
B11B11=Calculation!F5
B12B12=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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Fulff Method

VBA Code:
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
 
Last edited:

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
what About

Cell Formulas
RangeFormula
A2:A12A2=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:B12B2=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:C12C2=COUNTIF($A$2:A2,A2)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,278
Messages
5,600,695
Members
414,400
Latest member
Damocles2021

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