How to lookup data in a table based on a condition, then create a new table with the data from multiple rows of the previous table?

[scotte19]

Hi,

I have a piece of software that creates reports for us in an excel format. See below table
This table shows us what product we have built and how many of. In the end column this is product built, when this changes to zero the product has been changed.
What I need to do is every time we see a zero in that column and the line above has a value >0 take that row of data and insert it into a new table.
There could be as many as 15 of these product changes so the new table needs to be able to insert a new line below the previous one.

As a minimum I want to insert the data shown with black marks into the new table.
Can someone point me in the right direction, don't have much excel experience.

Thanks for your help

 

[Legacy 143009]

Assuming Date Column is A, starting from second row:
This formula won't work if you have more than 1 zeros consecutively.

=IFERROR(OFFSET(INDEX(A$2:A$1000,SMALL(IF(($G$2:$G$1000)=0,MATCH(1,1*(($G$2:$G$1000)=0),0)),ROWS($A$1:A1))),-1;0),"")

Paste this formula to first cell in your 2nd table.
This is an array formula so pressing Enter is not enough. Hit Ctrl+Shift+Enter together.
Drag right and down. Don't touch to ROWS function.

 

[scotte19]

Thanks for the advice, I've pasted that in and it comes up with an error with the formula.
Unfortunately I will have more than 1 zero consecutively due to the how the data is output.

 

[Legacy 143009]

Assuming your data is from Column A to G,
Assuming your data starts from 2nd row,
Assuming you are going to paste the formula in H2:

=IFERROR(INDEX(A:A,MATCH(INDEX($G:$G,AGGREGATE(15,6,ROW($G$3:$G$1000)/(($G$3:$G$1000=0)*($G$2:$G1000<>0)),ROWS(H$3:H3))-1),$G:$G,0)),"")

This not an array formula. You may paste and use directly. It includes consecutive zeros.
Have a nice day!