Pulling data into a "master sheet"

Hello all,

I am trying to pull select data from multiple sheets into one "master sheet", however I am struggling to find a working method. I am unable to use reports due to them not connecting with datashuttle. I have tried VLOOKUP, Copy Row automation, INDEX/MATCH and a few more but i can't get them to pull all of the data. In google sheets I would use the function FILTER.

Any suggestions are greatly appreciated!! Thank you in advance.

Find more posts tagged with

Support

Solution Center

Automations

Accepted answers

All comments

Genevieve P.

Hi @Eponine

Would you be able to provide more information, ex. screen captures of the source sheets, how many source sheets there are, what the Matching Unique Identifier is between all the sheets, what you're looking to bring in? This would be really helpful, but please block out sensitive data.

You can nest multiple INDEX(MATCH statements within the same formula by using an IFERROR. Then if there's a NO MATCH error because it doesn't find the data is Sheet 1, you can have it look in Sheet 2.

For example:

=IFERROR(INDEX({Column to Return Sheet 1}, MATCH([Match Value]@row, {Match Value Sheet 1}, 0)), INDEX({Column to Return Sheet 2}, MATCH([Match Value]@row, {Match Value Sheet 2}, 0)))

Cheers,

Genevieve

Eponine

Hi @Genevieve P.

Thank you for your feedback, there are 8 source sheets and from each I am looking to pull the; Task name, finish date & stage.

Screen Shot 2022-01-25 at 10.26.27 am.png

I need a formula that will look at the entire sheet, find the task name "Title Registration Issued", then pull in the relevant data from the finish and stage columns. This would work perfectly with VLOOKUP, except, that formula only takes the first row that has "Title Registration Issued" in it and for each source sheet there could be upwards of 10 "Title Registration Issued" rows all with different data.

Thanks again for any suggestions!

Genevieve P.

Hi @Eponine

The scenario you're describing is exactly what Reports are built to be a solution for: in a Report you can select all 8 source sheets and bring into one space any of the rows that match your filter value (ex. "Title Registration").

There currently isn't a simple solution for a formula to bring back all this data while checking 8 sheets and numerous matching rows; you would likely hit cell reference limits.

Do you have access to DataMesh as well as Data Shuttle? DataMesh could take the data you have stored in a Report (which would be filtering across your 8 sheets) and then bring that into a destination Sheet for you, that you could then use in your Data Shuttle offload workflow.

Cheers,

Genevieve

Eponine

Hi @Genevieve P.

I have tried DataMesh in the past, however there is a field that asks what to do with duplicates. This section only has 2 options;

- Pick 1st Match will use the first matching lookup value from the source sheet.
- Ignore Entries will skip duplicate lookup values entirely.

If there was a way to turn this filter off that would be a great solution! Currently when I run this it only bring in some of the data as most of the data will contain duplicates (task name, project name, stage, etc.).

Thank you,

Genevieve P.

Hi @Eponine

Is there a way you could use a column that won't have duplicate entries as your Lookup column? For example, what about an auto-number system column, or a column that has a formula which adds together cells that will make the value unique?

Eponine

Hi @Genevieve P.

Unfortunately, that isn't possible. I have managed to work around this issue by exporting all of my source sheets then filtering them using Google Sheets.

I really appreciate all your help Genevieve!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.