Using VBA to find discrepencies between columns in Excel

[Ant234]

Hi,

If this has been posted before I apologise.

I have been handed a spreadsheet with some 20000 rows in it.

If a Zero occurs in a particular column (say V), how can I check that a zero also appears in the same row in column W.

If, for Example V28 = 0 but W28 doesnt, then just some sort of notification is needed, rather than changing it.

It is just zero's that need to be checked for.

I hope this makes sense.

Many many thanks in advance.

 

[dhulbert]

Quickest way would be to put a formula in a spare column such as

=IF(V1=0,IF(W1=0,"OK", "Error"),"OK")
Then filter on the errors.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Ant234]

Thanks for that. )

Is it possible to automate this with VBA? (because of the enormity of it)

Ideally, I'd like to either have something pop up when there is a mismatch, or to copy the entire column into a new worksheet, then move on to the next one.

Cheers

 

[KenWright]

What does the size have to do with it. You create one formula to the right of the data and then double click the bottom right of the cell. Formula is automatically run down to the bottom of your data.

To get it on another sheet, you can either just use autofilter on your error column to do it in place, or copy the sheet so you don't lose the original, then filter on OK, select all and do Edit / Go To / Special / Visible cells only, and then hit Edit / Delete / Entire row, and then take off the filter. Now you have just the errors.

Literally about 60 seconds work.

Absolutely no use for VBA in this scenario as currently stated whatosever.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

formulae would be quicker than VBA

enter formula into top cell
double click on the bottom right of the formula cell - formula will self populate all the way down to the bottom of your dataset...

 

[AnotherHiggins]

Ideally, I'd like to either have something pop up when there is a mismatch

I think what Ant wants is something that will appear as the data is being entered, not something (s)he can do later.

If that assumption is correct, I'd suggest looking at Conditional Formatting. For example, if you select columns A:W (or however many columns you are using), then you can
[ul][li]go to Format > Conditional Formatting[/li]
[li]Change the first box to Formula Is[/li]
[li]Type [COLOR=blue white]=if($V1=0,$W1<>0)[/color] into the second box[/li]
[li]Click the Format button[/li]
[li]Go to the Patterns tab[/li]
[li]Select the color of you choice (nothing dark)[/li]
[li]Click OK[/li]
[li]Click OK[/li][/ul]
Now, whenever you enter a zero in column V, if the value entered into column W is also a zero the entire row will be highlighted!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.