Match Data on different lines
 

Hi.

I have a document with bills imported from two different databases.
I'm trying to compare the value from for example C2 with H2 and if it isn't
a match i need to compare C2 with H3, H4 etc, until it fines a match (the
problem is that the data from one database misses about 500 of 2000 bills).

Is this possible to do with a macro or advanced filter?

Henrik

Match Data on different lines
 

Henrik,

This formula

=MATCH(C2,H:H,False)

will return the row number of the first match, or an Error if there is no match. You can then use
that number to return another value, like this, from, say, column I:

=INDEX(I:I,MATCH(C2,H:H,False))

You can also trap the error to tidy things up:

=IF(ISERROR(MATCH(C2,H:H,False)),"No match found",MATCH(C2,H:H,False))

HTH,
Bernie
MS Excel MVP


"Henrik T" <Henrik wrote in message
...
Hi.

I have a document with bills imported from two different databases.
I'm trying to compare the value from for example C2 with H2 and if it isn't
a match i need to compare C2 with H3, H4 etc, until it fines a match (the
problem is that the data from one database misses about 500 of 2000 bills).

Is this possible to do with a macro or advanced filter?

Henrik

Match Data on different lines
 

Do the two imported files have a matching field - e.g. invoice number,
account number, etc.? If they don't, this will be entirely manual, since we
don't know why H2 would match C2 etc. Assuming they have a matching
field(s), you could:

=IF(ISNA(Vlookup(Value To Match, Range of data on second sheet with matching
column the first column, X (replace X with the column that has the $ value
for the row we match on), False)), "No Match", IF (Vlookup(Value To Match,
Range of data on second sheet with matching column the first column, X
(replace X with the column that has the $ value for the row we match on),
False) = Cell on First sheet that has $ value to compare with, "Data Values
Equal", "Data Values Not Equal"))

Basically, you will first see if we have a match on the key field - if we
don't, the ISNA test will return the "No Match" message. If we do match on
the key field, then it will compare the $ values and either return "Data
Values Equal" or "Data Values Not Equal". You could, of course, get even
fancier and replace the "Not equal" text with the variance of the two values.

"Henrik T" wrote:

Hi.

I have a document with bills imported from two different databases.
I'm trying to compare the value from for example C2 with H2 and if it isn't
a match i need to compare C2 with H3, H4 etc, until it fines a match (the
problem is that the data from one database misses about 500 of 2000 bills).

Is this possible to do with a macro or advanced filter?

Henrik