Comparing figures across 2 columns.

[Guest]

Hello

In this spreadsheet we have pairs of columns for each week-ending date.
Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns
C and D both represent week-ending 6th October 2007.

I need a formula which will look along the row and check for a blank. If
Column C is blank and there is a figure in Column D, or vice-versa, I want
the formula to flag this occurence. In other words, I want the spreadsheet to
show me where a store has enetered a figure in one column but no figure in
the paired column across the sheet.

Can anyone suggest a formula please?

Many thanks.

Keith

 

[Guest]

Hi,

You could use just use Autofilter and select blanks from the
list..(Data>Filter>Autofilter)

Or..
Lets say your data starts in in Cell C1...

Maybe in cell E1 enter =COUNTBLANK(C11) and copy down has far as you need
this will count the number of blank cells in the range.

Hope this helps,

Gav.

 

[Guest]

Hi Gav

Many thanks for your reply.

I have been using the Autofilter, but it's very time consuming that way, not
least because there are actually 2 spreadhseets and some 24 columns on each..

Also, I don't want to count the number of occurences, I want to find them
and notify the store that they have made an error.

 

[Guest]

The only other way I can think off without using a macro is using conditional
formatting....

select the first cell that you want to flag on your sheet...

Then select Format>Conditional Formatting..

Select Formula is from the first drop down and in the condition section type
=ISBLANK(C1) and choose your format.

Copy this along your 24 columns and down as far as you need.

This will then make it easier to see what stores haven't entered the data.

Still a bit time consuming I'm afraid...

Gav.

 

[MartinW]

Hi Keith,

You will have to adjust this to suit, but I think it will do what you want.

Select cell C2 and set Conditional Formatting
with Formula is =COUNTBLANK(C22)=1
(pick a color)
Then
Select cell D2 and set Conditional Formatting
with Formula is =COUNTBLANK(C22)=1
(pick same color)
(note same formula in each cell but applied seperately)

Now highlight both cells and use the formula painter
to drag them across the sheet.
Then reselect the entire row grab the formula painter
and once again select the entire row and drag down as
far as is needed.

It works in my trials here but you may need to adjust for your
data

HTH
Martin

 

[MartinW]

Correction!!
Where I said formula painter, read format painter.

 

[Guest]

Hi Martin

Many thanks for that. It works a treat!

 

[MartinW]

Glad to hear that Keith, thanks for the feedback.

Martin