Poor Man's Datamesh
Prior to getting Datamesh, I devised a solution to transfer data from one page to another, without having matching columns. This process uses an intermediary sheet. It's kind of a pain to set up, but it works well and requires little to no maintenance. I still find some very small use cases where this comes in handy. Thought I would share.
Below is an example of a source and destination sheet, with an intermediate "cleaner" sheet. Requirements:
- The source sheet and the cleaner must have a reference column with shared unique IDs. I'll tell you how to manage that below.
- The cleaner sheet must have the same column headers as the target sheet.
Setting up matching unique IDs between source and cleaner sheets
You may have an active sheet with data constantly being added. You may not even have unique IDs associated with the data, or they may be unpredictable. What you need is a predictable unique Id in the source sheet. To get that, set up a Row ID column. This will provide a sequential row ID for every incoming row. In the above example the row IDs are 1-5.
Then, in the cleaner sheet, add a list of numbers to cover both your known rows (i.e. "1-5") and enough future rows so that you aren't having to constantly go back and add more. I usually load about 3,000 at a time, and I don't have to look at them for a few months. You can easily create a column of numbers in excel and then copy and paste them into your cleaner's reference column, 500 at a time.
Mirroring Data in your Cleaner Sheet
Now that you have a unique id to use as a reference, use cross-sheet column formulas to bring over the data you need, in the format you need it in. In the cleaner sheet example, I merge the first and last name into one column to match the target sheet. I also remove the color from the house information. The formula for "Client Name" would look something like this:
=INDEX(COLLECT({source_firstname}, {source_rowid}, [Extra Column]@row),1) + " " + INDEX(COLLECT({source_lastname}, {source_rowid}, [Extra Column]@row),1)
Send the Data Via Automation
Use an automation to transfer the data to the target sheet. A few things to keep in mind:
- You may need to set some conditional cross-sheet logic to trigger your information to appear at the correct time. For instance, you may only want the data to transfer once a checkbox on the source sheet has been checked. You can set your cleaner sheet up so that it only populates data if the corresponding checkbox is checked, or other condition is met.
- It's up to you whether you want to use a MOVE or COPY command. I've found that using a MOVE command is nice because it essentially deletes the row ID from the cleaner sheet and makes it impossible to accidentally send more. On occasion, I've added an ID back in to re-do a transfer. Using "COPY" command would keep the row ID on the cleaner page, but you may need to have further logic to keep from re-triggering the automation and sending the row again.
- You can't trigger an automation using a formula (they do this to keep from accidentally triggering an automation repeatedly). However, you can set a time-based automation to send based off a formula column. I had one instance where I set up 12 of the same time-based automations, each for a different hour. Classy, I know.
Handy Trick
Remember, your cleaner column headers must exactly match your destination column headers. And your move command will send every column, including the row ID column on your cleaner page. However, move automations can't overwrite column formulas. So if you name your row ID column in your cleaner sheet the same as a column in your target sheet that's got a column formula assigned, all the data will copy or move over except the row ID, as that will essentially fail to send.
Use Cases
There are plenty of use cases for this trick. I originally devised it because I had to have a continuous merge of data from three separate sheets that didn't look at all alike. I used a cleaner sheet to parse all that data. In that case, I actually had three sets of unique IDs, one to match each sheet. Complicated? Yes. But it solved an impossible problem, so there you go.
Comments
-
For the record, the use of an intermediary sheet is useful even if you have DataMesh but want to filter the data that moves into another sheet based on a condition or checkbox. Thank you!!
-
Hi Jennifer, for datamesh, when I need to filter the results I usually use a helper column that only populates my unique ID when a condition is met. So if it’s based on a checkbox, the helper column would have a formula that said something like:
=IF(checkbox@row=1, uniqueid@row, “”)
