vlookup double entries... help!!!

[xair]

Hi,

I am trying to reconcile two accounts using the vlookup function. I have
column with a dollar amount in each excel sheet. I run a vlookup to compare
the two columns and give me the dollar amount in one sheet next to the
corresponding dollar amount in the second sheet. So I can find entries that
are there in one account and not in the other.

This works fine untill two or more consecutive amounts are the same in
column 1. IN this case the same dollar amount repeats itself even if it is
only present in one of the two sheets.

So then I have to go back and manually look at each dollar amount that
repeats its self more than once and make sure it is there in both sheets.

Does any one have any idea how I can get around this problem?
I hope I expnained this one clearly enough, let me know if you have any
questions.

Thanks!

 

[Joel]

The last time I saw this problem is was due to the number of decimal places
that was shown on the worksheet. The data was really 1.0 and 1.5. The
worksheet was formated to show zero decimal places so both numbers appears as
1. The graph showed two data points with the value 1 instead of 1.0 and 1.5.

Go back and reformat your column so that more decimal places are displayed
in the worksheet.

 

[xair]

Hey Joel,

Actually the numbers are infact the same. for example:

column1 column2 vlookup
130 130 130
256 256 256
500 256 256
345 500 500
400 345 345
111 N/A
400 400


So i look at the vlookup column and it tells me that 130 is there in both
columns, 111 is not there in column 1 because there is a N/A, and that 256 is
there twice in both columns.

The 256 is the problem because it is actually only there in column 1 once,
and I cant tell that buy looking at the vlookup column. I have to go through
both the columns and make sure that if there is a dollar amount that repeats
its self in one column (like 256), it is in fact there twice in the second
column as well.

Since my data has thousands of numbers, this is a problem. Any ideas on how
I could do this? may be another way if not vlookup.

Thanks!

 

[Joel]

On esolution would be to use countif and make sure the total from countif is
the same in both columns. How do you know if the first occurance of the
match is wrong or the second one is the one that doesn't match? When you
don't get the number in both column you don't know where the error is , only
that there is an error.

 

[xair]

I usually run the vlookup twice, once running column A against column B, and
then column B against column A. That way if #N/A appears infront of a number
in column A, i know that number is not there is column B. and vice versa.

But since #N/A dosent appear in the case where a number (eg 256) is there
twice in column B and once in column B, i never know there is that
discrepency untill i go and look at and compare each repeated number manually
in both columns.

I still dont understand how countif could help me. Could you explain that a
bit?

Thanks.

 

[Joel]

Instead of vlookup try this

if(countif(A1:A1000,B1)=countif(B1:B1000,B1),"Matched","UnMatched")

Then copy the formula down the entire column.

Matched will indicate you have the same number of 256 in Column A and Column
B. You can put what ever formula or String instead of Matched and UnMatched.
Unmatched will indicate that you are missing a number from one of the
columns.