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
16
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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:
Upvote 0
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)
 
Upvote 0
Solution
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...
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top