Data mesh: Merging between sheets

Babitha

I have 3 sheets: 2 of them have their own forms to collect data, and 1 master sheet. The 2 individual sheets have questions which are similar to each other and questions which are completely different (they are 2 forms of questionnaires). The master sheet is used to merge the answers of the similar questions and all other answer into one place (so it has the answers to both sheets).

I used DataMesh to update and overwrite the data for each person depending on which form is filled out. But I've run into a problem where when the data mesh updates, it updates everything into either one of the form instead of the most recent update for each row.

As in, each row is a different person. If person 1 filled in Form 1 first. The data from Form 1 is datamesh'ed to the master sheet for the corresponding columns of form 1 and the columns that are similar to form 2. Couple days later Person 1 filled in Form 2 and this is datamesh'ed to master sheet for the corresponding columns of form 2 + overwrite (if needed) the similar questions from form 1 columns (so the answers are the most up to date). The problem happens when, if Person 2 did Form 2 first and then Form 1, after updating through datamesh, both person will show the overwritten data (or updated data) from either form 1 or form 2. I want there to be a division in updating—as in, only update if there is a change, and update to the more recent entry for each individual row.

Any ideas?

Jason Tarpinian

So if I'm understanding, you have 2 forms where some questions overlap, and you want to take the most recent of those overlaps? If so, read on, if not, my apologies for not understanding.

So if I'm understanding, what you'd want to do first is create another sheet where you copy rows to automatically as they are added to your (2) form sheets. So you would have something that looks like below.

And now you have a list of all the data that you need to report on, you just want to pull the latest answer from each question? Knowing that the newest data will be on the bottom of the sheet, we can use an INDEX(COLLECT to pull the value that we want.

=INDEX(COLLECT([Q2]:[Q2], [Form user]:[Form user], [DataMesh Sheet - Create User Names]@row , [Q2]:[Q2], <>""), COUNTIFS([Form user]:[Form user], [DataMesh Sheet - Create User Names]@row , [Q2]:[Q2], <>""))

So what that formula says is we want to create a range of the Q2 column, where Form user is Jason, but only where the answer wasn't blank, giving us 2 values. The COUNTIFS just does that second part then to get us the latest answer, of those 2 fields that match our original criteria, return the 2nd value.

If this isn't exactly what you were looking for, hopefully it could at least give you some ideas on how to proceed. Good luck!