Macro to compare data in two workbooks

0
votes

I need some help on a task that I am working on

Here is a quick background of what I want to achieve.

  1. I have two workbooks, say WB1 and WB2.

  2. In WB1 I have some names of students in column B and their total marks in column C. The names are not sorted and are in any random order.

  3. In WB2 also I have the same kind of information but the total number of names may be more or less (say in WB1 I have data for 15 students but in WB2 I may have data for 13 or 17 students). But WB1 is my reference.

Now, what I want is to compare the marks of students in a third workbook named "Result.xls". I want to have all the names from WB1 copied to Result.xls in Column A and corresponding to each student's name I want a "match"/"does not match" string in column B based on if the marks in WB1 for a student matched with the marks of that student in WB2.

I hope I am clear enough.

Thanks in advance. Harpal [email protected]

2
vbaexcelexcel-formula

2 Answers

1
votes

I appreciate that you might be looking for a VBA solution but it can be done with a formula. Of course, you might use VBA to create a formula.

IF(VLOOKUP(A2,[WB1.xlsx]Sheet1!$A$2:$B$4,2,0)=VLOOKUP(A2,[WB2.xlsx]Sheet1!$A$2:$B$5,2,0),"match","does not match")
0
votes

You can do that with vba.

Your logic is already right so lookup:

Dim exc as object

Set exc = createobject("excel.application") Exc.open yourpath

Then lookup and compare with

N = activeworkbook.name

i = 1

Do until workbooks(n).sheets("yoursheetname").cells(i,2)=""

Compare the different workbooks with if

Loop

So just start coding... and learn.

There should be as well a way with the excel functions offset, index, vvlookup and so on.

I just show you the way.:-)

You do the work