Using DataMesh to Split One Line Onto Multiple Lines (for Forms Entries and Other Uses)

Lucas Rayala
Lucas Rayala ✭✭✭✭✭✭
edited 02/29/24 in Best Practice

This is a problem I've heard a few times so I'm answering it in a separate post for reference in long form. I'm using the example from @WtaylorW's question over here. The basic need is to take one line and split it onto multiple lines. In this example you have a form that's inputting multiple names in different columns, and you want a single line entry for each name.

image.png

Here's how to do it with DataMesh:

You'll need two sheets, called "Form Entry" and "Final Entries" for this example. The form responses, as expected, will load directly into the Form Entry sheet, and a single DataMesh configuration will port the entries into the Final Entries sheet. You can do this with a column formula that rotates a unique ID and name field. Read on!

Final Entries - Sheet Setup

The Final Entries sheet will contain the first seven columns from the above example, up to the "Name" column. It will contain one additional column called "UniqueEmpID".

Form Entry - Sheet Setup

The Form Entries sheet will look the same as the above example, except for the addition of these two columns:

  • RowID: a column with the type "Row ID" to create a unique ID.
  • UniqueEmpID: this is the rotating unique ID column, created with the below column formula (see immediately below the formula for an explanation). Note I've created this column with the same name on both sheets, but only the Form Entry sheet has a formula.
=IF(AND(NOT(ISBLANK([Employee Name 1]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "A")=0), RowID@row+"A", 
IF(AND(NOT(ISBLANK([Employee Name 2]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "B")=0), RowID@row+"B", 
IF(AND(NOT(ISBLANK([Employee Name 3]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "C")=0), RowID@row+"C", 
IF(AND(NOT(ISBLANK([Employee Name 4]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "D")=0), RowID@row+"D", ""))))

Let me explain the first line of the formula first. The IF statement is looking for two things:

  • is there a name in the Employee 1 column?
  • in the Final sheet, is there an entry in the UniqueEmpID column equal to the RowID with the letter A appended to it? For instance, if it's row 12, the formula is looking for the unique ID "12A". The COUNTIF counts instances of the ID, therefore a count of "0" would mean it's not on the target page.

If there is an name present on the form entry and the unique ID is not yet on the Final sheet, then the formula will create the unique employee ID (the "12A" in the above example).

However, if the employee ID is on the Final sheet, then it will go to the next line, which repeats the process for Employee 2, except that it appends a "B" instead of an "A". This continues down the list of employees. This is all structured for the DataMesh config and explained at the bottom of this post.

Your name column is going to have the same formula as the UniqueEmpID column, except instead of the output being RowID@row+"A", it will be [Employee Name 1]@row. That formula is:

=IF(AND(NOT(ISBLANK([Employee Name 1]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "A")=0), [Employee Name 1]@row, 
IF(AND(NOT(ISBLANK([Employee Name 2]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "B")=0), [Employee Name 2]@row, 
IF(AND(NOT(ISBLANK([Employee Name 3]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "C")=0), [Employee Name 3]@row, 
IF(AND(NOT(ISBLANK([Employee Name 4]@row)), COUNTIFS({Final Sheet UniqueEmpID}, RowID@row + "D")=0), [Employee Name 4]@row, ""))))

DataMesh Configuration Setup

Setup your DataMesh configuration as follows:

  • source sheet = Form Entry sheet, with Lookup Value "UniqueEmpID"
  • target sheet = Final Entries sheet, with the Lookup Value "UniqueEmpID"
  • for Data Fields, each column will copy its corresponding column (it doesn't matter if you include UniqueEmpID or not in the Data Field). Make sure to include the "Name" column, but DO NOT reference the Employee Name 1, 2, 3, etc columns from the Form Entries sheet.
  • set your options for the config as noted in the below screenshot
image.png

What Happens

When a form submission lands on the Form Entry sheet, the UniqueEmpID formula will first look for the unique ID for Employee 1 (example, "12A") on the Final Entries sheet (in the corresponding UniqueEmpID column). If it doesn't see it, that cell will display the unique ID "12A". Similarly, the Name cell will do the same, except it will display the name of Employee 1.

The DataMesh config is set to "Copy and Add Data" -- that means if the unique ID 12A doesn't exist in the final sheet (which we know it doesn't because the formula checked), it will add that unique ID and the rest of the information in the corresponding columns.

At this point, the unique ID now exists in the Final Entries sheet, so the UniqueEmpID formula will now execute the entry for employee 2, triggering the DataMesh again, etc.

When I've setup similar processes, it works surprisingly quickly, spinning through the different options in moments and copying the data for all entries in a few seconds, but it's DataMesh, so results will vary.

You'll have to modify this slightly for your own purposes, of course. I hope this is helpful to some people. Let me know if you have questions!

Tags:
· Share on FacebookShare on Twitter

Comments

Categories