Insert rows based on missing column values, fill cells in new rows based on criteria

  • Some background if it helps - this is a tree inventory dataset, and each tree should have been measured 4 times in 4 different years. Many were not, either because they had fallen down or were erroneously ignored. Each row is a different measurement "round". I need to create new rows where they were missed, for each tree, to aid in regression analysis.


    I've been able to successfully insert new rows where sequential values are missing in Column D (denoting Measurement Round) using a code adapted from this thread: http://www.ozgrid.com/forum/sh…00184&p=772716#post772716


    I would like to fill in values each time a new row is created - filling down the values from Columns A, B, & E in the previous row, filling Column D with the sequential value that was identified as missing, and filling Column C with a year associated with that value (i.e. 1=1997, 2=2002, 3=2008, 4=2016).


    What I want to fill in Column L might be more complex.. (so no worries if it would take too much time) The value should equal "100" unless it is in the middle (Measurement Round 2 or 3), and in subsequent years the cell value equals "99" for that tree. In that case, Column L should equal "99" (100=fallen down, 99=dead but standing).


    Here is what I've got:



    And here is a small portion of the dataset:


    forum.ozgrid.com/index.php?attachment/71690/


    Please let me know if I can provide other info.
    Thanks!

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    Think this does the first bit. Not sure about col L. Do you mean it should be 100 for 1, 99 for 2 and 3, and 100 for 4? Fairly sure that it isn't right as doesn't fit with existing data.

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    This is great, thanks very much.


    Sorry for not explaining the second part well. Column L should only be a 99 if the row being inserted is a 1, 2, or 3; and the following 2, 3 or 4 (with a matching Column E value) is a 99. Else, it should always be 100.


    I couldn't actually find an occurrence that issue in the first portion I included, but I did in this snippet:forum.ozgrid.com/index.php?attachment/71713/


    I tried this If statement below, but it frequently adds 99 where it should be 100.


  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    Does that mean that a 1 should only be 99 if the following 2 is a 99 OR that a 1 should only be 99 if the following 2 or 3 or 4 is a 99?

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    The latter. And likewise, a 2 should only be a 99 if the following 3 or 4 is a 99; a 3 should only be a 99 if the following 4 is a 99.

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    OK, will take a look at this if you're still looking. Can you try this, still not 100% sure I've got it right

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    Thanks for the reply, I am still looking.
    That is very close to what I want to do, but I've been unable to tweak it without fully understanding the expression.


    It is correct in inserting a 99 where there is a gap in the middle (i.e. if 2 is missing and 3 is a 99, 2 should be a 99), but incorrect in making 2 a 99 every time 2, 3, and 4 are added. If all 3 are being added, all should be 100s.

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    Ah, the mist was clearing but now it's descending again.


    Are these the rules
    a 1 should only be a 99 if the following 2, 3 or 4 is a 99
    a 2 should only be a 99 if the following 3 or 4 is a 99
    a 3 should only be a 99 if the following 4 is a 99
    But if 2,3 and 4 are being added all should be 100

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    Can you check this?

  • Re: Insert rows based on missing column values, fill cells in new rows based on crite


    You've done it, sir!
    Or at least as close as I can ask for with this giant mess of a dataset.
    Many thanks, you've saved me from dozens of hours of mind-numbing manual entries and/or confusion.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!