Use DataMesh to combine multiple sheets

cmore04

I'm trying to combine multiple sheets into one master list, what is the best way do the config for this please?

I want it to pull in all values from source sheets, and if the source sheets gets updated I want it to automatically update the master target sheet. Sometimes the source sheets have duplicates so I want it to pull those in as well.

Thank you!

Paul Newcome

Have you looked into creating a row report?

cmore04

Hi Paul!

Yes I tried, but my source sheets are too large for the row report to work, I have some source sheets that are 800 rows.

Genevieve P.

Hi @cmore04

Reports can actually hold more rows than individual Sheets, so it should be no problem to have 800 rows! 🙂

This would be a more robust solution compared to formulas or cell links. If you're not seeing all your data in a Row Report, it may be that you need to tab over to the next page. Reports will display 2,500 rows at one time. After this, you'll need to page over to see additional results.

Cheers,

Genevieve

cmore04

Thanks! But another reason I need it to be in a sheet is because I need to find out the duplicates across multiple sheets, I've found a formula to help me identify them but I know how I can do this if I don't have one master sheet not report... is DataMesh not the best for this?

Genevieve P.

Hi @cmore04

Thank you for clarifying your scenario a little more!

DataMesh will eliminate duplicates, meaning that if you had the same Unique Identifier on two sheets, only one sheet would bring in that information. For the second "meshing" of data, the workflow will either Skip the duplicate or update the first match it finds (meaning it would replace that row information from Sheet 1 with the new row information for Sheet 2).

In a Report, you can Group by that unique identifier and then add a Summary of COUNT if that helps; that way you can quickly see values that have more than 1 row across all of your sheets. However this is where you'll come into an issue with the length of the Report. Grouping will only allow you to expand rows if the rows are less than 2,500 all together. If you're reaching this limit, I would suggest using Filters in your Report to break out your data into two categories and work with it that way.

An alternate option would be to use an automatic copy row workflow in each of your sheets to bring over your newly added data. However the information wouldn't be live or linked, and you wouldn't have source sheet links the way you would in a Report.

If we've still misunderstood why you'd prefer a sheet, it would be helpful to know the full scenario and how much of the row data you're looking to have displayed in this Master sheet (e.g. is it just the unique identifier and one other column, or all columns?)

Thanks!

Genevieve