SOLVED

Synch 2 columns

Copper Contributor

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?

9 Replies

@Vonni95 

 

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...

 

  1. Create "helper columns" for each column in which subsequent changes have been made.
  2. Using a unique identifier (see bullet below) as the key reference, use VLOOKUP or XLOOKUP, nested in an IF function, to compare each column in the old with the comparable column in the new and populate the new "helper column" with the most reliable data.
    • this assumes that there's at least one column in both the "newer" and the "older" file that is reliably linked with the data that have changed as well as with the email/name that got messed up, and that that column contains unique data (employee ID, SSN, whatever)
  3. Then copy and paste special (values and format) from the "helper column" into the appropriate original column.
  4. Then delete the helper column.

 

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.

@mathetes 

 

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.

or you could upload a representative sample of your two sheets (devoid of confidential info) and we could demonstrate.

@mathetes 

 

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.

@Vonni95 

 

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.

@mathetes 

 

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:

  1. If I have 16 columns in my sheet, I only need 1 helper column in total right (for the email column)? Not 1 helper column for EACH of the 16 columns?
  2. where do I insert the functions?
  3. Please help me parse this function so I can better understand what's it's doing: 
    =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))
    I get that A2 is the Company; "Emails Sorted Alphabetically" is calling that sheet name; "Original Unsorted" is the sheet name; E2 is the Street; F is the Street helper column.

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.

 

best response confirmed by Vonni95 (Copper Contributor)
Solution

@Vonni95 

 

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.

  • the $A$2:$F$6 refers to the range in which the VLOOKUP function finds that which it is looking for.
  • the 5 tells it to look at the fifth column in the range $A$2:$F$6
  • the 0 tells it to look for exactly the value that has been entered as the first argument in the VLOOKUP function.

 

Here then is the  basic VLOOKUP formula without its being nested in the IF function (more on that to follow).

  • VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0)   And each argument there is as follows:
  • A2 refers to the content of cell A2 (in this case it is Company ID), and it's this value that is going to be used to find the desired row in the range that follows, where the desired answer resides
  • 'Emails Sorted Alphabetically'!$A$2:$F$6 refers to the range in which the lookup function is going to search first for the value in A2, in this case looking in a different tab, that named 'Emails Sorted Alphabetically'!   (the exclamation point is added as part of the syntax when referring to a different sheet by name)
  • and from there, the 5 and 0 operate as noted above.

 

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

 

  1. If I have 16 columns in my sheet, I only need 1 helper column in total right (for the email column)? Not 1 helper column for EACH of the 16 columns?
  2. where do I insert the functions?

And the answers are

  1. Yes, you only need one "temporary helper column" IF it's only the email address column that is sorted so as to be out of synch with everything else.
  2. It doesn't matter where you put that helper column because you're going to delete it once you've gotten the email addresses correctly aligned with the rest of the content in the row and copied and pasted them (using "Paste Special....Values) into the column where they belong. You could insert one column directly adjacent to the sorted/incorrect column, or you could place it at the end of all the other columns. You just need to make sure that the various references in the IF(VLOOKUP.....)) are correct.

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. ;)

 

@mathetes 

 

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

@Vonni95 

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.

1 best response

Accepted Solutions
best response confirmed by Vonni95 (Copper Contributor)
Solution

@Vonni95 

 

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.

  • the $A$2:$F$6 refers to the range in which the VLOOKUP function finds that which it is looking for.
  • the 5 tells it to look at the fifth column in the range $A$2:$F$6
  • the 0 tells it to look for exactly the value that has been entered as the first argument in the VLOOKUP function.

 

Here then is the  basic VLOOKUP formula without its being nested in the IF function (more on that to follow).

  • VLOOKUP(A2,'Emails Sorted Alphabetically'!$A$2:$F$6,5,0)   And each argument there is as follows:
  • A2 refers to the content of cell A2 (in this case it is Company ID), and it's this value that is going to be used to find the desired row in the range that follows, where the desired answer resides
  • 'Emails Sorted Alphabetically'!$A$2:$F$6 refers to the range in which the lookup function is going to search first for the value in A2, in this case looking in a different tab, that named 'Emails Sorted Alphabetically'!   (the exclamation point is added as part of the syntax when referring to a different sheet by name)
  • and from there, the 5 and 0 operate as noted above.

 

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

 

  1. If I have 16 columns in my sheet, I only need 1 helper column in total right (for the email column)? Not 1 helper column for EACH of the 16 columns?
  2. where do I insert the functions?

And the answers are

  1. Yes, you only need one "temporary helper column" IF it's only the email address column that is sorted so as to be out of synch with everything else.
  2. It doesn't matter where you put that helper column because you're going to delete it once you've gotten the email addresses correctly aligned with the rest of the content in the row and copied and pasted them (using "Paste Special....Values) into the column where they belong. You could insert one column directly adjacent to the sorted/incorrect column, or you could place it at the end of all the other columns. You just need to make sure that the various references in the IF(VLOOKUP.....)) are correct.

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. ;)

 

View solution in original post