Excel: How to compare data in two columns

Does it have to be VB?

You can use Conditional Formatting for this.

See attached, I have changed the value in D3 to show the highlight in work.
Reconcil-03-16-17--002-.xlsx

Hi Rob,

Yes, it has to be in VB.

Also, the spreadsheet I attached, contains only two rows. There will be a lot more rows to run through. This was just an example.

Please let me know if you can do it in VB.

Why does it have to be in VB when there is already a perfectly suitable feature for this?

For the additional rows, the range to which the Conditional Format is applied will simply be expanded from A2:D3 to A2:D??

How do you expand the range for conditional format?

Never mind, I found it. Let me  test it out.

In the conditional format rules window, to the right of each rule is a range selector tool. Simply click that and highlight the range to which the rule needs applying or type it in manually.

The one thing to remember is that the row referred to in the rule has to be the first row of the range. In this instance the rule states:

=$A2<>$D2

Therefore the Applies to range has to start at row 2 otherwise the format will be offset.

Hi Rob,

The conditional formatting works well when there are no exceptions. As I just found out the following type of exception is possible in the future. For example, if two or more records, see below, have the same "Name.ID:" then dollar amount in column "A" for those two or more records needs to be added together and compared to dollar amount in column "D" for the first record with the same ID. If total is the same then we are good, otherwise we highlight.

Amount      Comment1                                                                        Amount
$135.00           Name.ID:1012926|IBRIDGE_Purchase.ID:1796689      $710.00
$575.00           Name.ID:1012926|IBRIDGE_Purchase.ID:1796691            

Thanks!

Ok, the rule would have to include a SUMIF function rather than a direct one to one comparison. Not at PC at the minute.

Hi Steve,

Please find attached... I have added helper column which will determine What needs reconciliation and highlights the text. Doesn't matter how long your data would be.
Reconcil-03-16-17-v1.xlsm

Hi Shums,

Thanks a lot for a quick response! If you could change the macro a bit so if it's the same "Name.ID" for two or more records then it shouldn't be errored out. See attached spreadsheet.

As you can see in attached spreadsheet, the first two rows after column name, have the same "Name.ID", which is 1012926, and when we add two amounts in column "A", which is $135.00 and $575.00, then we get $710 which is correct amount reflected in column "D".

In this situation, we don't want to error out anything. Same applies to row 4 and 5 since those have the same "Name.ID" as well.

Now, row 6 and 7 have the same "Name.ID" but a different sum when you add the amounts in column "A" and compare it to the number at column "D" row 6.

Another discrepancy is in row 8 where there is only one record but amounts are not matching because column "D" is blank.

Those are the scenarios for now. Also, there maybe 3 records with the same  "Name.ID" so we need to calculate amount for all of them and compare to column "D". Out of those three records the first one always as the total in column "D".

I know that's a lot of explanation. Sorry...
Book1.xlsx

Hi Rob,

thanks for your response. your original solution worked well, it just needs to be enhanced. i'll wait for your response back.

Thanks Shums! Let me test it a little bit and see if there might be any other scenarios but I think it works so far!

Apologies for delay coming back to you, dentist appointment this morning!

See attached updated file. I have added a helper column to identify the name.ID value and then used that in the SUMIF function.

Thanks
Rob H
CF-with-SUMIF.xlsx

Thanks Rob! Very nice and concise solution but would require an extra step in specifying how many rows to process, so I'm gonna go ahead with Shums's solution.

Thanks a lot!

You're Welcome Steve! Glad I was able to help