can excel check one column against another

**lil'bit** · 10-08-2005, 11:52 AM

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.

Gary''s Student · 10-08-2005, 01:05 PM

Looking at a single row, does E2 have to equal I2?
--
Gary''s Student

"lil'bit" wrote:

>
> 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.
>
>
> --
> lil'bit
>
> married, muddled, and mistook
> ------------------------------------------------------------------------
> lil'bit's Profile: http://www.excelforum.com/member.php...fo&userid=4306
> View this thread: http://www.excelforum.com/showthread...hreadid=474485
>
>

**lil'bit** · 10-08-2005, 02:08 PM

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 .

pinmaster · 10-08-2005, 02:24 PM

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

Ken Wright · 10-08-2005, 03:05 PM

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 :-)
------------------------------*------------------------------*----------------

"lil'bit" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> lil'bit
>
> married, muddled, and mistook
> ------------------------------------------------------------------------
> lil'bit's Profile:
> http://www.excelforum.com/member.php...fo&userid=4306
> View this thread: http://www.excelforum.com/showthread...hreadid=474485
>

**lil'bit** · 10-08-2005, 03:39 PM

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.

Well let me guess. Human error in entering data?

As Gary said, does Col E have to equal I in every row?

I answered that. Yes, they must match also.

Thanks to all who replied. Appreciate the help. This is a great forum.

pinmaster · 10-08-2005, 03:45 PM

Hi,
Glad to hear it and thanks for the feedback!

Regards
Jean-Guy

Gary''s Student · 10-08-2005, 04:05 PM

pinmaster's approach is very good. Not only does it locate the bad rows, it
will tell you if your fixup is good.
--
Gary's Student

"Ken Wright" wrote:

> 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 :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "lil'bit" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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.
> >
> >
> > --
> > lil'bit
> >
> > married, muddled, and mistook
> > ------------------------------------------------------------------------
> > lil'bit's Profile:
> > http://www.excelforum.com/member.php...fo&userid=4306
> > View this thread: http://www.excelforum.com/showthread...hreadid=474485
> >
>
>
>

**lil'bit** · 10-08-2005, 04:23 PM

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?
--