Macro to replace the VLOOKUP function

[Benoitwysocki]

Hi everyone,
I am currently working on a macro to apply a Vlookup on two different excel sheets.
Actually, I wish to make sure that What is in sheet 1 is also in sheet 2. For this I have to make sure that for the same name the amount in the worksheet 1 is the same in work sheet 2.
Example if I have securityABC = $100 in worksheet 1 I want to make sure that I have a securityABC =$100.
I have two questions:
1) The number of lines is very huge, is there any limit that will constraint me with a macro ?
2) What code should I use to do this reconciliation btw the 2excelsheets and how I can highlights discrepancies if there is any?

The objective is to replace the manual check that I do with a Vlookup.
Feel free to provide me your opinion
Best,

Ben

 

[Nutster]

You don't need a VBA Macro to solve this. That will turn out to be much slower than using the built-in VLOOKUP.

Let's start with 2 worksheets (as you say): Sheet1 and Sheet2. The first two columns of each sheet is the name and value.
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]$49.95[/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]$16.99[/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]$12.49[/TD]
[/TR]
</tbody>[/TABLE]
and so on.

Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]$49.95[/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]$12.99[/TD]
[/TR]
[TR]
[TD]Item D[/TD]
[TD]$19.99[/TD]
[/TR]
</tbody>[/TABLE]
etc.

What you do, is add another column after the first two in Sheet1. This new column will show the price in the other sheet. If the item does not exist in the other sheet, it will be left blank.
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Price[/TD]
[TD]Sheet2 Price
[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]$49.95[/TD]
[TD]$49.95
[/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]$16.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]$12.49[/TD]
[TD]$12.99[/TD]
[/TR]
</tbody>[/TABLE]
and so on. The formula in C2 ($49.95: the Sheet2 price for Item A) is:

=IfError(VLOOKUP(A2, Sheet2!$A:$B, 2, False),"")

The VLOOKUP looks for the value from A2 (Item A) in the first column of the range in the second argument. If it finds it, it returns column 2 of that row. The False at the end tells VLOOKUP to check every item in a linear search until it finds what it is looking for. If VLOOKUP cannot find the value in the first column, it returns #N/A. The IFERROR function looks at the value from its first argument and if it returns an error, like #N/A, then it returns the second argument instead, which in this case is blank. Copy this formula and paste in the entire column for your table.

To make it more obvious that the prices are different, you could add a conditional format. Select the Sheet2 Price column (C). Click on Data - Conditional Format. Choose Highlight Cell Rules - Equal To. Click on the selection button on the end of the first text-box and click on A2. Make sure the row number does not have a dollar sign on it. The text-box should contain =$A2 . Click OK.

You will notice that the cells that are highlighted are the ones that match; we want to change the conditional format so that the highlighted cells are the ones that do not match. Click on Conditional Format again and select Manage Rules. Make sure the rule you just created is selected and click on Edit Rule. Find the combo box that says "equal to". Click on the drop-down button and select "not equal to". Click Ok. Keep clicking Ok until you are back at your worksheet. All the mismatches should now be highlighted in red.

I had you go through the Conditional Format twice as that was the easy way to assign that style for the mismatches.
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Price[/TD]
[TD]Sheet2 Price[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]$49.95[/TD]
[TD]$49.95
[/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]$16.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]$12.49[/TD]
[TD]$12.99[/TD]
[/TR]
</tbody>[/TABLE]

You could then filter for red text on column C to find the mismatches.

 

[Benoitwysocki]

Hi,

I really appreciate the time you took to answer to my message. The point is I was not precise enough in my previous message. It is not a short and simple excel sheet whereby I have to reconcile and execute a VLOOKUP.
The excel file contains much more data ( around 18columns and 200lines minimum)
I have to do a Vlookup 250 since I have 250 files. It is a huge loss of time.
To be more accurate I am doing a reconciliation of cash between the bank and the accounting of a company.
I have to make sure that every payments in bank is recorded in accounting and vis et versa.
This is why i was hoping to have suggestion on a code line that allow me to just in put the data from the bank, the data from the custody and to reconcile them with a macro.
Thus, I will need only few minutes by reconciliation (250 to do...) rather hours with VLOOKUP.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID NUMBER[/TD]
[TD]BANK[/TD]
[TD]ACCOUNTING[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Knowing that I have to check the payment date and the executive date (two different dates) as well as the currency.
It takes a lot of time to do formula for each situation. This is why If you would have any codes to built a macro, it will be very welcome
Thanks,
Ben

 

[sleek12]

Not exactly a macro but try using this :_
FindAll