Monday, December 1, 2008

Computer: EXCEL - Comparing Columns of Date

So once again I'm chasing some numbers in Excel and I needed to compare a column of 275 #'s against a column of 1000 #'s. Here's how I did it:

So if your 1000 row column of data in column A. Place your 2nd column of 275 #'s Data in Column C. (You can actually put it whereever you want, it will still work.)

The formula you want to put in column B between A1 and C1 is:
=IF(ISERROR(MATCH(A1,$C$1:$C$250,0)),"",A1)

So what we are doing is comparing cell A1 to the entire column C to see if it matches.
Now grab that anchor and drag it down the sheet. The $C$1:$C$250 is not going to change because we locked it in with the $ signs, but A1 will change to A2, A3 as you drag the anchor.

Poof, you instantly have a comparison of the 2 mismatched rows of data.

I'm not done yet. I've been doing a lot of programming lately, so here's an option if you have so many rows of data in one column that it would be easier to write a Visual Basic macro than drag that anchor 60,000 lines. Check out this trick:

Press ALT+F11 to start the Visual Basic editor.
On the Insert menu, click Module.
Enter the following code in a module sheet:

Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
Set CompareRange = Range("C1:C250")
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x
End Sub

Press ALT+F11 to return to Excel.

In Excel 2003 and earlier versions,
choose Macro on the Tools menu,
then click Macros.

In Excel 2007, click the Developer tab, and then click Macro in the Code group.

No comments:

Post a Comment

Hi - sorry for the confirmation but I need to weed out the noise from the well intended comments. Thanks for leaving a note... - Bruce