Jun 02 2020 07:59 AM
I have a document that, somewhere along the way, has one column of emails that got alphabetically sorted by a colleague but did not synch with the other columns when sorted. So now that email column is completely out of synch with the names those emails are attached to. There's 1200+ records so I can't just go back easily and manually synch them.
I can't just unsort as this document was changed weeks or months ago and only now was noticed and there is no little triangle at the column header either, indicating that it was sorted.
We do have an unsorted version but the problem is that other unrelated changes were made since that version so we would lose those changes if we reverted to that version.
So my question ... is there a way to merge those two versions and synch the name + email columns successfully?
Jun 02 2020 08:44 AM - edited Jun 02 2020 08:45 AM
There may be an easier way to do this than mine, so let's see if others weigh in with some of the methods that Excel has come up with in the years since I was a true active user (I've resumed, and am loving the recent abilities, e.g., Dynamic Array Functions, but there's still a lot that is like magic)....
Anyway, to your question: Yes, it should be possible.
Starting with your unsorted, old file...
That's not elegant, but it would work and not be too time consuming. Let's see if there's a more elegant magic wand you could wave over the whole mess.
Jun 02 2020 08:53 AM
Thank you for the idea. I have basic-to-scratching-the-surface-of-intermediary use of Excel only so I will wait to see if someone comes up with a magic wand suggestion. If not, I will give your idea a go and do my best.
Jun 02 2020 08:55 AM
Jun 02 2020 09:19 AM
That's a good idea. I did up a sample book with 2 sheets - the original unsorted and the sorted emails. Please note there are many other fields in the real sheet (addresses, titles, etc) if that matters at all. They are all properly synched with the names. The only column out of synch is the emails.
Jun 02 2020 10:07 AM
So I did this first as we'd talked of it, keeping the old as the primary one to be updated. I added in fake addresses to use, and showed how simple formulas could be used to fix new street and city addresses, keeping the original sort of name and email as is. There are comments in each sheet describing what I did.
If in fact, the ONLY thing in your real world that you need to restore is to re-align the email address within the row where it belongs, if all those other changes stayed with the right person, IF that's true, then the same logic can apply, it's just that you'd do it the other way around. That wasn't what I understood you to be saying. But I added a solution in that direction to...
You'll find comments on the various sheets.....let me know if you have questions.
Jun 04 2020 07:57 AM - edited Jun 04 2020 09:21 AM
Thank you so very much for working up that example.
I'm working hard to wrap my head around using these functions but I have a few questions:
=IF(VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0)='Original Unsorted '!E2,'Original Unsorted '!E2,VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0)) |
But what is $F$6,5,0? I know the 6,5 refer to cells but what is the 0? And what is the purpose of the F6,5,0 in the function?
Thanks again for your help.
Jun 04 2020 09:30 AM - edited Jun 04 2020 09:36 AM
Solution
You need to familiarize yourself with the VLOOKUP function. I'll try to answer your question, but the best way for you to get comfortable with it is to create your own opportunities to use it, to try it out, to learn how to use it. And a way to help in that is to use one of the many Internet resources. Here's one that is good:
https://exceljet.net/excel-functions/excel-vlookup-function
So you asked: But what is $F$6,5,0? I'm not sure what the 6,5,0 refers to and why it's in the function.
And the first thing I'll say is that it isn't 6,5,0, but $F$6,5,0 and not even just that, but $A$2:$F$6,5,0. So let me break that down and then we'll go back and look further at the full context in which $A$2:$F$6,5,0 appears.
Here then is the basic VLOOKUP formula without its being nested in the IF function (more on that to follow).
Now let's put all that in context:
=IF(VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0)='Original Unsorted '!E2,'Original Unsorted '!E2,VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0))
The IF function basically is saying, "Do the lookup and compare the address you find with what you find with the address in the original unsorted tab. IF they're the same, keep what's there in the original. ELSE put in the new.
Again, I'd refer you to that ExcelJet site to do some research on the IF function as well.
You also asked
And the answers are
I'd be happy to write the correct formula for you if you choose to post the actual format of your actual sheets, having first rendered any personal info anonymous. But see if you can follow my descriptions above. Just create a backup sheet first. You can't then "break something" any more than it's already broken. ;)
Jun 04 2020 04:31 PM
Thank you for that very exhaustive description! And also for the link.
I initially had some partial success and managed to get the correct unsorted individual emails, but only one at a time. I eventually figured out that I could simply replicate/sequence the column just as one would for sequential numbering. This produced the entire set of several hundred records. I verified a dozen or so records so far and all have checked out
SO Mathetes, you are my new hero. In addition to marking that reply as Best Response, I would be VERY happy to send you a Starbucks card for your time and energy if you allow me to PM you for your contact info (is that allowed here? Not sure. It's my first time on this forum). You have saved me countless days/weeks of manual work and helped me look like a boss at work. LOL
Jun 05 2020 05:48 AM
I'm glad it worked. You're very welcome.
The reward is in your response. And, frankly, I get pleasure out of explaining things, helping people learn Excel, which I think is just an amazing program.
Jun 04 2020 09:30 AM - edited Jun 04 2020 09:36 AM
Solution
You need to familiarize yourself with the VLOOKUP function. I'll try to answer your question, but the best way for you to get comfortable with it is to create your own opportunities to use it, to try it out, to learn how to use it. And a way to help in that is to use one of the many Internet resources. Here's one that is good:
https://exceljet.net/excel-functions/excel-vlookup-function
So you asked: But what is $F$6,5,0? I'm not sure what the 6,5,0 refers to and why it's in the function.
And the first thing I'll say is that it isn't 6,5,0, but $F$6,5,0 and not even just that, but $A$2:$F$6,5,0. So let me break that down and then we'll go back and look further at the full context in which $A$2:$F$6,5,0 appears.
Here then is the basic VLOOKUP formula without its being nested in the IF function (more on that to follow).
Now let's put all that in context:
=IF(VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0)='Original Unsorted '!E2,'Original Unsorted '!E2,VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0))
The IF function basically is saying, "Do the lookup and compare the address you find with what you find with the address in the original unsorted tab. IF they're the same, keep what's there in the original. ELSE put in the new.
Again, I'd refer you to that ExcelJet site to do some research on the IF function as well.
You also asked
And the answers are
I'd be happy to write the correct formula for you if you choose to post the actual format of your actual sheets, having first rendered any personal info anonymous. But see if you can follow my descriptions above. Just create a backup sheet first. You can't then "break something" any more than it's already broken. ;)