You can always create a third file that does the comparison row by row, column by column, and report any mismatches. Should take all of 1 minute to do. That’s the easiest way IMO.
It isn’t real code. Vlookup is just a function and I agree that it will probably work. Vlookups are a very useful skill for data manipulation and comparisons. You should teach it to yourself. It should only take an hour or two and you will find uses for it all over the place. That type of thing is why I only have to work half as hard as my coworkers for five times the results.
VLOOKUP is the way to do this. See the following two files for an example. ExcelFile1 has the base data, ExcelFile2 shows the formula to use to refer to it.
The other method would be to write VBA in Excel but that’s probably overkill for this problem.
Ah. I’d never heard of it before. I opened the Help menu and am printing off the section on vlookup…
OK, I’ve printed it off and skimmed it. It doesn’t have an example of what I want to do, which is to replace stuff in several columns with the same columns from another file if the value in the first column matches.
I’m not sure where they’re putting the formulas. Somewhere in the single file they’re using?
Basically I can click buttons in Excel, and I can do formulas. I don’t know any of the Power User stuff.
What’s the exclamation point? Would that go into the first cell, and then be copied for the whole file? What if the sorted files don’t match? For example, File 1 has A0001, A0003, A0004 and A0005, and File 2 has A0001, A0002, A0003, A0004 and A0005. Will the B, C and D column values from File 2 replace the ones in File 1 for all of them? Or will it try to match File 1 A0003 to File 2 A0002 because of their positions in the file?
EDIT:
CookingWithGas: I’ll have a look at that. I clicked on Open File on the first link and it’s not doing anything. I don’t know if it locked up the browser, or if it just takes forever to download.
CookingWithGas: It crashed my browser when I tried to open the first one. I went back and saved them both, and then tried to open them. I get a message saying the files are corrupt.
I think we need some clarification of what you’re trying to do, because that seems to slightly contradict the OP. It looks to me like you’re saying there
for each row in file A
if there's a row in file B with the same key /* i.e. same first column */
write file A's row to file C
increment count of matching rows
else
write file A's row to file D
increment count of non-matching rows in file A
That is, you want a list of the rows in file A where the entry in the first column is the same as any entry in the corresponding column in file B. Right?
In the column to the right of A, do a vlookup(“column A”,“columnsBandC”,2,false) only with the appropriate syntax; this will copy the value in column C (the second column in the “columnsBandC” range) to the column where you wrote the lookup whenever the value in B matches that in A. Make sure that both files are sorted by columns A and B respectively.
Once the lookup is done, copy that whole column and do a “paste special” as “values” so it doesn’t try to update itself, takes up less space, etc.
Almost. If there’s a match, I’m taking some information from FILEA and some information from FILEB from each row; not the whole row from FILEB. In the code I posted, this is from a program that reads a text file; so REST is the rest of the file, which contains several rows.
This is what I do: Open the Excel file. Get rid of the commas. Save as Comma delimited. Open the .csv file in Access. Export to a text file. Do the same with the file I want to match to. Run Easytrieve to compare the two text files and write two output files. Open the output .txt files in Excel. Modify the non-matches and cut-and-paste them into the matched file. Sort and save as Excel.
I was hoping there would be a way of doing that without so many steps. Like just tell Excel to look at the other file and bring over selected cells when the value in the first column matches, and keep the original data when they don’t.
Apparently vlookup can do that, but I’ve never used it and Microsoft’s ‘Help’ is as unhelpful as most of their documentation.
Also, have you tried the function wizard? That might help you build your lookup formula instead of trying to manually translate from our template here.
There’s a function wizard? I just followed along with this little yellow box that popped up (like when you’re filling in a cell with the same information as in another cell and you can just click on it).