Align imported data with manual input data in a Google sheet

0
votes

I have two sheets, one with product names (sorted alphabetically) in column A, stock levels in column B.

Sheet 1

enter image description here

The other 2nd sheet with the same products in column A and stock levels in column B but populated by "importrange" but with columns C and D manually populated with customer names.

Sheet 2

enter image description here

What I'd like to do is, if a new product is introduced in the 1st sheet and it is positioned alphabetically in (let's say) row 3, then have a new row appear in the 2nd sheet.

Sheet 2 ideally

enter image description here

Currently, if a new product is introduced, the 2nd sheet will show the product and stock level, but the manually entered data in columns C and D will then be out of sync by one row.

Sheet 2 currently

enter image description here

Is there a way of automatically inserting a full row when the new product is imported to the 2nd sheet if I'm using "importrange"? If there is, can someone please help me here with the formula or code.

1
google-sheetsimportrange
this wont rly be possible unless you introduce some ID system to it. then you can use simple vlookup in arrayformula – player0

1 Answers

0
votes

Basically what the OP is asking is how to keep the spatial/positional relation between two data sets. One alternative is to adopt the use of "keys" and "foreign keys".

  1. Let says that a row represents a record.
  2. A key is an unique value that used to identify a record.
  3. A foreign key is a value added to a record to relate it with a record from another data set

Applying the above to the question case:

  1. On Sheet 1 add a new column to hold the key for each row. When a new record is added the key of the other records should not be changed and a new key should be assigned to the new record.

  2. Create a table of for the values of Sheet 2, columns C and D, and add to each record a key.

    NOTE: This is the table were you should do the manual input.

  3. Create a relation table having two columns one for the keys of the records on Sheet 1 and another for the records of Sheet 2, Columns C and D.

    NOTE: I will not discuss here the arity of a relation concept as it will make the answer too long.

  4. On Sheet 2, use IMPORTRANGE to get the values from Sheet 1, including the keys for each record.

  5. Add a lookup formula (VLOOKUP / INDEX-MATCH) to get the related records from the table created on step 2 by using the relation table created on step 3.

Related