Excel help: File matching

Factual Questions
1

Let’s say I have two Excel files. Column A is the key. Columns B, C, D, and E in File 1 have to match columns B, C, D, and E in File 2.

Is there a way to tell Excel to basically say:


If File_1_Column_A matches File_2_Column_A
   File_1_Column_B    =    File_2_Column_B
   File_1_Column_C    =    File_2_Column_C
   File_1_Column_D    =    File_2_Column_D
   File_1_Column_E    =    File_2_Column_E
End-If

I can do this of course in a different program, but it would be nice if Excel could do it.

If it can be done, can you post how to do it?

Thanks.

2

Try vlookup. I’m not sure if it’ll do what you want, but it seems to me it’s pretty close.

3

I should point out that I don’t know how to write code or whatever it is in Excel.

4

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.

5

Something like

if ‘\path[file1.xls]Sheet1’!A1 = ‘\path2[file2.xls]Sheet1’!A1

maybe? That’s how you refeer to cells in other spreadsheets, anyway.

6

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.

7

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.

www.seigle.net/ExcelFile1.xls
www.seigle.net/ExcelFile2.xls

8

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.

Would the whole thing be something like this?

if ‘\path[file1.xls]Sheet1’!A1 = ‘\path2[file2.xls]Sheet1’!A1, ‘\path[file1.xls]Sheet1’!B1 = ‘\path2[file2.xls]Sheet1’!B1, ‘\path[file1.xls]Sheet1’!C1 = ‘\path2[file2.xls]Sheet1’!C1, ‘\path[file1.xls]Sheet1’!D1 = ‘\path2[file2.xls]Sheet1’!D1

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.

9

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.

FWIW, here’s how I’m doing it outside of Excel:


*********************************************************************
*********************************************************************
JOB INPUT(FILEA KEY PREV-AR FILEB KEY CUR-AR) FINISH(Z-REC)
*********************************************************************
*********************************************************************
      
IF MATCHED     
   OUT-AR            = PREV-AR
   OUT-NAME          = PREV-NAME
   OUT-NAME2         = PREV-NAME2
   OUT-ADDR1         = PREV-ADDR1   
   OUT-ADDR2         = PREV-ADDR2
   OUT-CITY          = PREV-CITY
   OUT-STATE         = PREV-STATE
   OUT-ZIP           = PREV-ZIP
   OUT-REST          = CUR-REST
   PUT FILEC           
   COUNTER1          = COUNTER1 + 1
ELSE 
IF NOT MATCHED FILEA
   NOT-REC           = CUR-REC    
   PUT NONMATCH       
   COUNTER2          = COUNTER2 + 1 
END-IF   
END-IF
10

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?

11

Column A is in file 1.

Column B and C are in file 2.

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.

12

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.

13

I’m sorry it caused a crash.

Here is the formula you need in B1 of the second file. $D$22 may be some higher or lower number, use whatever your last row is.

Copy to C1, and change the last 2 to a 3. Copy to D2 and change it to 4.

=VLOOKUP($A1,’[ExcelFile1.xls]Sheet1’!$A$1:$D$22,2,FALSE)

14

Ah. Of course I’d have to Copy and Paste special. I forgot about that.

OK I tried this, but there’s an error in it somewhere.

=vlookup(Sheet1 !A,‘H:\Input_File_Prev.xls’,Sheet1 !D,TRUE)

15

That looks like a lot more than one error. Sheet1 !A? What is that? I think you want a range in there - like A1 through A25.

I also think there are some missing/stray single quotes in there.

16

Not quite following what that’s doing, but I pasted it into the first cell of a test file:

=VLOOKUP($A1,[‘H:\800326_Prev.xls’]Sheet1!$A$1:$D$22,2,FALSE)

It doesn’t like the file name.

Anyway, why FALSE? Shouldn’t it be TRUE? That is, if A1 in the current file matches any cell in Column A that would be TRUE, wouldn’t it?

17

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.

18

You’re asking me?? I have no idea what I’m doing. We don’t use Excel that way here. :stuck_out_tongue:

19

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

20

I just realized I FTP’d in ASCII mode instead of binary. Sorry. I assume it’s a moot point now but I will re-post if you would find it helpful.