Grouping or linking data I added on a sheet to its corresponding filtered data brought over from another sheet

Question

The small towing company I work for uses Google sheets for our calls logs. I filter logged calls so that all police calls are copied from the "General Log" sheet and show up on a different sheet "Police Calls" so we can see what vehicles are in our storage lots. I then need to add columns and enter additional information to the lines for each police call. I add last six of vin, owner name, insurance co. , etc.

My problem is when a dispatcher forgets to log a police call or another dispatcher logs their police call before an earlier police call is logged it bumps all the filtered info down a line, but my manually entered data stays on the line I entered it on.

Is there a way to group the filtered data and entered data so that the added info stays with the correct filtered info from the general log regardless of any added calls?

Answer 1

You need to define a unique value / key / id for each row on the data that is being filtered. You should manually enter this unique value into the manually entered data and use it to retrieve the related values.

There are many functions that could be used to make this work, like VLOOKUP. If you need to retrieve multiple values you might use VLOOKUP together with ARRAYFORMULA and set the columns to be retrieved using an array of column indexes.

Rough example formula

=ARRAYFORMULA(VLOOKUP(A1,Sheet1!A:Z,{2,3,4},0))

It's worth noting that spreadsheets are not relational databases / database management systems (dbms). Still, they might be helpful while the data structure stays simple. Sometimes using scripts could save you from having to move to dbms, but still they will work while the data structure doesn't become too complex and the data set doesn't grow too much.