Forum Discussion
Synch 2 columns
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
- 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?
- where do I insert the functions?
And the answers are
- 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.
- 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. 😉
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...
- Create "helper columns" for each column in which subsequent changes have been made.
- 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)
- Then copy and paste special (values and format) from the "helper column" into the appropriate original column.
- 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.
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.
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.
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.