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
-
Lachlan Stead ✭✭✭✭✭✭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?
-
Ryan Sides ✭✭✭✭✭✭
@Lachlan Steadis 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 videoabout 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.
-
Ryan Sides ✭✭✭✭✭✭
You're welcome@Chevon Brownell! Please let us know if you run into any further issues. Happy to help.
Answers
-
Lachlan Stead ✭✭✭✭✭✭
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?
-
Ryan Sides ✭✭✭✭✭✭
@Lachlan Steadis 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 videoabout 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.
-
Lachlan Stead ✭✭✭✭✭✭
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!
-
Ryan Sides ✭✭✭✭✭✭
You're welcome@Chevon Brownell! Please let us know if you run into any further issues. Happy to help.
Help Article Resources
Categories
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T02:58:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"
