Google Sheets Query – How to Keep Cells Paired with Query Results

google sheetsgoogle-sheets-query

The "colors" sheet has data populated from query ("items" sheet) and users are allowed to write comments on each row.

Problem: if row order is changed on "items" sheet, the "colors" sheet row order
will be lost and any previous comment will not match the correct row.

How can I prevent this? Is there a way to keep paired query row results and comments? Maybe using timestamp as a reference?

At the moment sheet "colors" contains a correct pairing, once a row is inserted between "items", the pairing is lost

Suggested setup

Use two sheets, Import and Entry

Import sheet
- Use to import the data to your spreadsheet,
  - Use IMPORTRANGE on this sheet
Entry sheet
- Use to capture, insert comments
  - Add a column for key/id. Add the key/id manually, by writing one by one, using paste as values, a macro/script, add-on or an app that uses the Google Sheets API.
    - Related threads:
  - Use a lookup formula to get the required values from the import sheet by using the key/id column for the lookup, you could use VLOOKUP, INDEX/MATCH among other methods.
    - Related threads:
      - Align the rows of one sheet with the rows of an imported sheet
  - Add the columns that will hold the comments

What will happen if a row is deleted from the source sheet (the one that was imported)?
- The cells on the Entry Sheet that use lookup will show #N/A but the key/id and the columns that hold the comments will remain in place.

I suggest vlookup in combination with filter. Here is an example:

=arrayformula(vlookup(filter(Master!D2:D, Master!E2:E="o"), {Data1!B:B, Data1!A:E}, {2,3,4,5,6}, false))

Step by step:

filter(Master!D2:D, Master!E2:E="o") takes the Id s from Master column D where column E is marked "o".
{Data1!B:B, Data1!A:E} prepares an array for lookup. This is only needed because your Id is not in the first column of the range; so an array has to be created with Id in the first column.
vlookup searches for the Ids returned by filter and returns the corresponding columns A-E from Data1
arrayformula ensures this all happens at once, no need to paste the formula row by row.

You might stitch two ranges together and control the sort order with something like:

=QUERY({A1:F10,G1:I10},"select * order by Col1 desc")

changing desc to asc to suit.