Best Practices for Copying Multiple Sheets

S.Stone

I have about 10 source sheets that I need to copy to a new sheet. They have most of the same columns, and they are tracking the same type of information for different projects.

I've attempted to merge them onto one report, but there are duplicate columns (and nearly 1,700 rows), and I don't know how to unify things so the merged reports line up. I'm thinking it might make more sense in the long run to get everything copied to a new sheet first, and then drive new reports from that central location.

The thing is, everyone needs to continue to update those source sheets directly, so my only options are to copy the information over while keeping it live - I cannot move everyone to the new combined sheet.

So I'm looking for best practices to move information from 10 sheets to a new central (combined) sheet, ensuring the latest information is always copied over from each individual sheet.

I just don't know if I should try the automation to copy rows (do those rows update for changes?) or if I should try INDEX/MATCH (But will that work with so many rows? Can I INDEX/MATCH a full sheet and have it copy below another INDEX/MATCH section - meaning if new rows are added, it will adjust?).

I appreciate any help/advice/recommendations!

Matt Johnson

Hi @S.Stone

I would go with the one report idea. Once the report is built, put all duplicate columns together (I assume the information is the same but the column name is different) and then decide what the name should be and change it on all the source reports. Also, you might find the same named column appears more than once. This happens when the column type is different on the source sheet. So this method will also help sync that up among the source sheets as well. You could also group the report by source sheet which would be kinda cool.

INDEX/MATCH won't new rows and would only be a one way street on the information flow.

I hope that helps.

Matt

Matt Johnson

Hi @S.Stone

I would go with the one report idea. Once the report is built, put all duplicate columns together (I assume the information is the same but the column name is different) and then decide what the name should be and change it on all the source reports. Also, you might find the same named column appears more than once. This happens when the column type is different on the source sheet. So this method will also help sync that up among the source sheets as well. You could also group the report by source sheet which would be kinda cool.

INDEX/MATCH won't new rows and would only be a one way street on the information flow.

I hope that helps.

Matt

S.Stone

@Matt Johnson

Thanks for your reply!

So I've got duplicate columns showing up - there are two ID columns, for instance, and two Project columns. They have the same name, but they're showing up as separate columns on the merged report.

Based on your comment, it seems like I need to go back to those separate reports and unify the column types and that might solve my issue.

Also great to know the INDEX/MATCH won't account for new rows - I won't waste time spinning my gears over that today.

Any recommendations to group those columns together another way, or is the best thing to just modify the source sheets so the column types and column names are the same?

Anything else I should watch out for that might trip me up?

Matt Johnson

Your best bet is to unify the Sheet's column names, hope the people in charge of those sheets are ok with that, and you're all set. In the event that 2 columns are named the same and are different types, and they need to stay different types, I would urge the users to change one of the column names to something else. It will make life easier when viewing the report. Good luck!