phoffric
asked on
Merge two Excel sheets into one using a unique key
I am given an Excel Spreadsheet with a single sheet, "Original", filled with Abbreviations (mistakenly, I called it Acronym). I added more abbreviations in a "New" sheet. Now I want to merge the two sheets into a "Merged" sheet. Since my "New" abbreviations, LOL and ACK, are duplicated in the "Original", I do not want to include them in the "Merged" sheet.
I can do this manually as follows: Append the "New" rows to the "Original" rows, and sort by "Acronym" (C-Column). Then delete adjacent rows that have the same "Acronym" to avoid duplicates. How can I do this without this manual (and error-prone process)?
<<EDIT>> Hmm, I better have the font of the new entries in the merged sheet color-coded so that the person I deliver the document to can readily understand the additions.
The table is sorted by Term (but that is not the most essential point, since once the unsorted merge is done, I can sort the B-Column).
Since I am on a closed network, I cannot add plug-ins, and if this requires a script vs. using Excel wizards, then I'll have to type the script in.
Thanks,
Paul
I can do this manually as follows: Append the "New" rows to the "Original" rows, and sort by "Acronym" (C-Column). Then delete adjacent rows that have the same "Acronym" to avoid duplicates. How can I do this without this manual (and error-prone process)?
<<EDIT>> Hmm, I better have the font of the new entries in the merged sheet color-coded so that the person I deliver the document to can readily understand the additions.
The table is sorted by Term (but that is not the most essential point, since once the unsorted merge is done, I can sort the B-Column).
Since I am on a closed network, I cannot add plug-ins, and if this requires a script vs. using Excel wizards, then I'll have to type the script in.
Thanks,
Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Darn it..
One acronym can have different Term meanings. Is there a way to just identify the duplicates for my inspection? I could remove based upon Term, but more likely there will be a slight difference in text (like punctuation, case, whitespace, slight wording differences).
One acronym can have different Term meanings. Is there a way to just identify the duplicates for my inspection? I could remove based upon Term, but more likely there will be a slight difference in text (like punctuation, case, whitespace, slight wording differences).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh, yeah.. Not Lots of Laughs. :)
That VBA looks like it should work but why write code when you don't have to?
Add another column with the repeat count value using the same countif in the code: In D2 add the formula: =COUNTIF($C:$C,C2)
Then copy and repeat that down the D column.
That will show you how many are repeated and let you decide which ones to remove. Granted, you do lose the automatic removal in the VBA code but the Remove Duplicates button will take care of those.
Add another column with the repeat count value using the same countif in the code: In D2 add the formula: =COUNTIF($C:$C,C2)
Then copy and repeat that down the D column.
That will show you how many are repeated and let you decide which ones to remove. Granted, you do lose the automatic removal in the VBA code but the Remove Duplicates button will take care of those.
ASKER
Thank you. Both of you have answered the question very quickly. Now I will ask the more refined question.
I have not idea whether I am closing this correctly with this newly improved closing mechanism - intent is to split the points.
I have not idea whether I am closing this correctly with this newly improved closing mechanism - intent is to split the points.
ASKER
Looks like I closed too soon. Will ask new question because I think I will need further help.
ASKER
ASKER
New Question where Acronyms are on separate sheets:
https://www.experts-exchange.com/questions/29112742/Excel-How-to-identify-duplicate-keys-in-two-different-sheets.html
https://www.experts-exchange.com/questions/29112742/Excel-How-to-identify-duplicate-keys-in-two-different-sheets.html
ASKER
The Term, aka, Short Term, allows me to reduce the verbiage even further, so I can get away with Laugh Out Loud.
Let me give the Remove Duplicates a try.