• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 49
  • Last Modified:

Poplulate Data down a column in Excel based on multiple criteria

Need to find a way for Data to populate data down a column based on previous lines and
reset on multiple parameters

The issue I have is that value in column 'RunT' is correct when there is a value in 'RunLastR' other than 0.

If 'RunLastR' is = 0 then I need the value that was last reported where 'RunLastR' <>0 from the  Value in 'RunT', and
copied down the 'RunT' column until the next Non 0 value in the 'RunLastR'

if the next row shows a non 0 in RunLastR that becomes the record that repeats down the column for 'RunT'

The Data needs to reset Based on StockCode, AllInvWhse...

If we need to add few columns to get this to work I am ok with that

Sounds confusing as hell but it is the best I can do
InventoryData.xlsx
0
Michael Katz
Asked:
Michael Katz
  • 11
  • 11
1 Solution
 
Martin LissOlder than dirtCommented:
Can you produce a sheet that has the first couple of problems corrected?
0
 
NerdsOfTechTechnology ScientistCommented:
pseduo-code here should help an expert make a macro to match your specifications.

1. for loop to build the array based on groups of description column.
2. for loop through each group
3. RESET 'LAST' value (flag) to 0
4. for loop through group rows
5. find FIRST value <> 0; once a value is found, make 'LAST' flag the value of the current row. Move to next row.
6. if NOT the END OF GROUP, AND if LAST value is non-zero:
6A. if current row value is 0, replace with LAST, LAST remains the same value. Move to next row. Repeat 6.
6B. if current row value <> 0, replace LAST with current value. Move to next row. Repeat 6.
0
 
Martin LissOlder than dirtCommented:
Is this what you want? Click the blue button.
29094383.xlsm
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Michael KatzAuthor Commented:
I think this may be more helpful
InventoryData2.xlsx
0
 
Martin LissOlder than dirtCommented:
I don't know if you clicked the 'Correct RunT' button in my preveious attachment, but it seems that it produces what you want for that data but in your new file the data is different, including the fact that there is no 'RowN' column as in the previous one.

Try the attach workbook, again clicking the 'Correct RunT' button.
29094383a.xlsm
0
 
Michael KatzAuthor Commented:
I am clicking on the Blue Button and nothing happens
0
 
Martin LissOlder than dirtCommented:
I downloaded the file I uploaded and it works for me. As a test, change cell G2 to 3 and then click the button. When I do that cells G3 and G4 also change to 3.
0
 
Michael KatzAuthor Commented:
This looks great.

How can I copy this to my actual spreadsheet?
0
 
Martin LissOlder than dirtCommented:
If you attach it I can do it for you. Or you can
  • Go to the IDE (by clicking Alt+f11)
  • Copy the 'CorrectRunT' sub that you'll find in Module1
  • In your real workbook go to the IDE and if you don't already have a module, choose Insert->Module from the menu
  • Paste what you copied into the new, or any existing, module.
  • Make the target sheet active, place the cursor in the 'CorrectRunT' sub and press F5.

This assumes that "G" is  the 'RunT' column and that "I" is the 'RunLastR' column. If not then change the 'G' and 'I' references in the code.
0
 
Martin LissOlder than dirtCommented:
Note that you will not have a button. If you want one I can explain how to add one or how you can execute the code without going to the IDE.
0
 
Michael KatzAuthor Commented:
Crap.. I tried to do this on my own.. Failed   :(
0
 
Michael KatzAuthor Commented:
0
 
Martin LissOlder than dirtCommented:
Here you go. Note that the file extension was changed to xlsm so that it can contain code. You can execute the code without going to the IDE in two ways:
  1. Click the button, or
  2. Click Ctrl+Shift+C

If the second method is satisfactory you can delete the button.
2006.xlsm
0
 
Michael KatzAuthor Commented:
Hi there,

after some testing it looks like there may be a problem if the StockCode Changes (columnA)
the value of RunT should not come from the previous stockCode. At the Start of a New StockCode or a New
Warehouse on a StockCode, the RunT Calculation must start over

I have enclosed a screen shotBrief sample
0
 
Michael KatzAuthor Commented:
if ColumnF (GlPeriod) is 1 and LastRunR  = 0 then the RunT must be 0
0
 
Martin LissOlder than dirtCommented:
if the StockCode Changes (columnA)
the value of RunT should not come from the previous stockCode. At the Start of a New StockCode or a New
Warehouse on a StockCode, the RunT Calculation must start over
  • Please define "New Warehouse". Is it when either column 'C' or 'D' changes?
  • Please tell me a row number where RunT is wrong because the stock code changed.
  • Please tell me a row number where RunT is wrong because the warehouse changed.
0
 
Michael KatzAuthor Commented:
Sorry I am not good at explaining things...

ColumnC.. AllInvWhs is the Warehouse...it changes after GlPeriod (ColumnF) 12

So the Warehouses always change after ColumnF is = 12

So I believe that the best answer for your row numbers is that rows 26,38, 50, 62, 74 (every 12 ..based on a monthly move)
0
 
Martin LissOlder than dirtCommented:
Thanks. What about "Please tell me a row number where RunT is wrong because the stock code changed"?
0
 
Michael KatzAuthor Commented:
Row 26
0
 
Martin LissOlder than dirtCommented:
0
 
Michael KatzAuthor Commented:
this looks perfect.. Very impressive.. thank you very much
0
 
Michael KatzAuthor Commented:
Martin is very very patient..It would appear, he is also a nice guy
0
 
Martin LissOlder than dirtCommented:
Thanks for the compliments and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now