Comparing two columns in excel

[Marcke]

I'm wondering how to compare two excel sheets that come from different sources.

Both sheets have an ID, and I'd like to match the ID before I start comparing values.

How do I match the ID's? Is there some kind of matching function? Or should I do vlookup or something?

[GTO]

Greetings Marke,

"...Both sheets have an ID..."

Respectfully, first we'd have to know whether to expect the ID number or alpha-numeric to be in the same cell/spot.

Mark

[Marcke]

Greetings Marke,

"...Both sheets have an ID..."

Respectfully, first we'd have to know whether to expect the ID number or alpha-numeric to be in the same cell/spot.

Mark

That's not necessarily the case, as values might be added or deleted.

[GTO]

...Both sheets have an ID, and I'd like to match the ID before I start comparing values...


That's not necessarily the case, as values might be added or deleted.

Sorry, I'm afraid you lost me. I hate to appear (or actually be...) thick headed, but I am just not 'getting' what you are trying to relay. Is the goal to first (programatically) search ea sheet for the ID's, and once found, check for match? Is there a pattern to the ID's?

Thanks,

Mark

[Marcke]

Sorry, I'm afraid you lost me. I hate to appear (or actually be...) thick headed, but I am just not 'getting' what you are trying to relay. Is the goal to first (programatically) search ea sheet for the ID's, and once found, check for match? Is there a pattern to the ID's?

Thanks,

Mark
Hey Mark,

My case is the following: I'm comparing a online database extract with an offline version. I'm checking which rows have changed in the database.
As random rows might be deleted and new ones added, there's no one to one mapping between the two sheets I'll have.

Therefore I could take an ID of a field, and then browse through all other ID's until there's a match. Then take the next field, .... But that would mean there's n2 complexity. I'd like to reduce that complexity.

[Bob Phillips]

Just use conditional formatting on the id fields of each sheet to flag missing. Use a formula of

=ISNA(MATCH(A1,Sheet2!$A:$A,0))

on sheet 1, and

=ISNA(MATCH(A1,Sheet1!$A:$A,0))

on sheet 2

[Marcke]

What takes the least CPU-resources?

I guess it's comparing if something's inequal? Or should I check if something's equal?

[Bob Phillips]

It is immaterial.

[Marcke]

Ok, thanks.

I tried to do the following:

If shBaseline.Cells(jRow, jCol).Value = shRequirements(iRow, iCol).Value Then

I get an error though. Any obvious mistakes?

[GTO]

Hey Mark,

Presuming that "shBaseline" adn "shRequirements" are the codenames of two different worksheets, then .Cells is missing from shRequirements.

Maybe?

Mark

[Marcke]

That was a stupid mistake, but didn't see it when reviewing the code though.

Thanks for your help.