Add a sequential row number

KevinMio8

I have two sheets that I use to 1. collect data and then 2. combine the data. The first sheet allows users to submit data via a form, there is always a unique reference number associated with their data submission in the first sheet that will be in the key column, they can submit updated data as many times as they need. I use these submissions to to generate a row number via an auto row number column, this row number is then used by a helper column to do an index/distinct check of the key column and populates another column with the unique entries. As there are multiple submissions bt the reviewers there are always more row numbers than unique entries.

Sheet 2 uses one of two “data consolidation” methods, index-match or Join-distinct. As long as I manually enter a row number as a reference for the formulas to use to do a cross sheet reference (back to the first sheet) all works well. In many cases I will auto populate the row number on the second sheet for 100 or more unused rows. I am trying to see if there is a formula that might add a new row number on sheet two when it sees that the row above has been populated by the index-match or join distinct column formula. Any ideas would be greatly appreciated.

Brian_Richardson

There's not a formula that will create new rows automatically. You have a couple of options to do this, the best would be to use Data Mesh if you have it. With Data Mesh, you can watch sheet 1, and when there's a new unique/distinct value appearing in the unique entries column (use that column as your key column for Data Mesh) then Data Mesh will add a row to Sheet 2 for that unique/distinct value with whatever other data you'd like to populate. You can do that by setting Data Mesh to do Copy and Add as the method.

If you don't have Data Mesh then this is trickier. You can use Copy Row automation from Sheet 2 to a third sheet, then Move Row from the third sheet back to Sheet 2, to generate new rows. However, the rub is that you cannot trigger automations on cross-sheet references.

Setup a row on Sheet 2 that watches Sheet 1 for recent new distinct values, perhaps doing a date check to see if something has a modified date or created date within the last day. If found, have a column that brings the new distinct value into the row.

In Sheet 2 setup a Copy Row automation that runs on a schedule, not triggered, and use a condition to check for the presence of a new distinct value in the row that you've set aside for copying. If it's populated, run a Copy Row to Sheet 3. Then Sheet 3 can have a triggered automation on row add to Move Row back to Sheet 2, thereby creating a new row.

If you need to add multiple rows, but aren't sure how many each time, you could preset multiple rows on Sheet 2 to be your blank rows and adjust your formula to look for new distinct values on Sheet 1 that don't match values already on Sheet 2.

Long story short…you should get Data Mesh if you don't already have it :-)