How to Combine multiple inspection reports into one row

Kelly Ospina

We have a complicated project where we're collecting rooftop inspections with one platform, using Zapier to push it over into a Smartsheet, and from there I need to move these inspections into Salesforce as leads with the Salesforce connector.

The problem is, each roof section is coming in as one row. So we will have a single location with ten different inspections on ten different buildings. But it's only ONE lead. Here is a photo of some of the fields from my sheet.

The yellow rows really comprise ONE lead, so I need to find a way to aggregate them. There are multiple columns in this sheet, including a number of Yes/No questions and a URL to the report link.

However, not every location will have multiple roofs, so whatever I do will not apply to every single row.

Does anyone have any thoughts on how to consolidate these rows so I can create a single Salesforce Lead?

Paul Newcome

What would you expect it to look like once it is consolidated? Do you have something in the sheet that indicates which "lead" each row is part of?

Kelly Ospina

Hi Paul, there is a column with a Geocode that comes in for each row. That would be what we could use to identify which rows need to be combined. It gets pulled in from a pin drop, so it's more consistent than some of the other fields which rely on the person filling the forms out to be consistent.

Ideally, I'd like to combine all these "Gray Schools" (and the one "Grady") into one row with the different roof sections listed together. Then I can use the connector to push that row into Salesforce as a single lead.

Paul Newcome

Can you provide an example (manually entered) of the expected output?

I am assuming we only use the address portion as an identifier since each of those three rows mentioned have different lat/long?