can excel check one column against another

  • Thread starter Thread starter lil'bit
  • Start date Start date
L

lil'bit

Hi. I have a spreadsheet with columns such as A, B, C, D, filled with
numbers. Each row across such as row 1, then row 2, then row 3, etc of
numbers is totaled in column E. Countless rows, each of which is
totaled per row and then ends with a grand total at bottom of column E.
Next comes columns F, G, H, also filled with numbers (but different
numbers), which is totaled per row in Column I. At the bottom of Column
I is a grand total. Even though arrived at using different sets of
numbers, -both grand totals - in columns E and I must match.

Sometimes they don't match. Ouch.

So here is my question. How can Excel find, flag, notify or whatever
the error for me without me looking at each and every entry? There are
many many many rows. Gulp. Help.

Thank you.
 
Hi Gary"s Student, thanks for replying. Yes they =. But when looking at
so many numbers, the eyes play tricks and I was hoping excel could
highlite or flag the different totals as an error .
 
Hi,
Try using conditional formatting.
Start by selecting your data, then go to FORMAT/CONDITIONAL FORMATTING,
select FORMULA IS and type:
=$E2<>$I2
choose your formatting......say red background.

in this example 2 is the first row of data, if different change it.

Hope this helps!
JG
 
You need to give us more info. How has it been wrong in the past - what can
go wrong with the data at a detail level such that the totals don't agree.
As Gary said, does Col E have to equal I in every row?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Hip Hip Hooray. Pinmaster, it worked and I found the error in a flash.
You saved these tired eyes from much more work. :)

Thank you so much. Have a nice weekend.


Ken wrote
what can go wrong with the data at a detail level such that the totals
don't agree.
Click to expand...

Well let me guess. Human error in entering data?
As Gary said, does Col E have to equal I in every row?
Click to expand...

I answered that. Yes, they must match also.

Thanks to all who replied. Appreciate the help. This is a great forum.
 
pinmaster's approach is very good. Not only does it locate the bad rows, it
will tell you if your fixup is good.
 
Yes his reply was right on the money. So thankful I don't have to keep
racking my mind. Just a few pennies entered backwards in one cell can
mess with all the figures. It certainly told me which row to look in.

"it will tell you if your fixup is good."

Gary, what do you mean? When I corrected my backwards amount in one
cell, the highlite color on that row disappeared. Is that what you
mean?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Subtracting multiple columns from another column 3
Getting Text data using the Data - Subtotal feature 1
Pivot Table - 3
Grand Total for Rows not Working in a Pivot Table 1
subtotal pages 2
pivot table excel 2
Column Heading on each row in pivot table 1
Inserting row takes 1 - 3 minutes. 1

Back
Top