Combine 2 sheets into one
I have 2 sheets, one with customer location data and one with parcel attributes. I would like one sheets that has the data from both sheets using the parcel number to match the information. (It is listed as Parcel # on one sheet and External System Link on the other sheet.) I am not even sure how to attempt this. Is it even possible? I hope I don't have to do it manually as there are approximately 3000 rows of information. I have uploaded the sheets to show the different columns but left the column data blank for privacy.
Best Answers
-
On the sheet you decide is 'primary' (or the one you want to keep), can you not create the additional columns for the info you want to bring across, use index or vlookup formula's to bring it through, then select all data in the combined sheet, ctrl+c then right click and use 'paste special', then select 'values'. This will return all data to static values within the combined sheet.
Not sure if that helps?
-
@Lachlan Stead is spot on. To add a few quick tips to that…
Use the “copy row” function (right click the row and you’ll see it there) to copy 1 row to the other sheet. This way, you’re not manually creating each of those columns.
Here’s a quick explainer video about how to use index/match formula to get your data from one sheet to the other and matched up nicely. Then like Lachlan said, just copy and paste as values once you have all of your data pulling in correctly.
Let us know how it turns out for you.
-
You're welcome @Chevon Brownell ! Please let us know if you run into any further issues. Happy to help.
Answers
-
On the sheet you decide is 'primary' (or the one you want to keep), can you not create the additional columns for the info you want to bring across, use index or vlookup formula's to bring it through, then select all data in the combined sheet, ctrl+c then right click and use 'paste special', then select 'values'. This will return all data to static values within the combined sheet.
Not sure if that helps?
-
@Lachlan Stead is spot on. To add a few quick tips to that…
Use the “copy row” function (right click the row and you’ll see it there) to copy 1 row to the other sheet. This way, you’re not manually creating each of those columns.
Here’s a quick explainer video about how to use index/match formula to get your data from one sheet to the other and matched up nicely. Then like Lachlan said, just copy and paste as values once you have all of your data pulling in correctly.
Let us know how it turns out for you.
-
Very good point Ryan - copying 1 row would big a big time saver here
-
Thank you. This all helped me. I was not too familiar with index and match before this. I was also worried about having to recreate all the columns, but the "copy row" option helped. I appreciate your help!
-
You're welcome @Chevon Brownell ! Please let us know if you run into any further issues. Happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 509 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi! I cannot figure this out. I have a "% Complete" column (0, 25, 50, 75, 90, 100, Cancelled). I made a simple checkbox column that should be checked if the item's "% Complete" value is equal to anyt…
- This formula keeps coming up as #UNPARSEABLE. I have checked the name of the columns, they are correct. Any ideas? I am trying to show the days remaining in a project. =IF([% Complete]@row = 1, "", NE…
- Hi All! I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas? =IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF(…
