PDA

View Full Version : Solved: Compare Column data



vzachin
01-25-2008, 07:12 AM
hi,

i have 2 columns of data about 1000 entries.
Column B contains original data, Column E contains new data.
i want to compare the 2 columns and extract the differences and placing the data into the adjacent column but not in the same row.

i'm currently using the following formula and filling down
=IF(ISBLANK(B5),"",IF(COUNTIF($E$5:$E$20,B5),"", B5))



how can i code this in vba? without placing the formulas and placing the differences in the adjacent column but not adjacent row (just list the differences)?

thanks
zach

Krishna Kumar
01-29-2008, 03:08 AM
Hi,

Sub kTest()
Dim a, i As Long, j As Long, w(), b, x
Dim Rng1 As Range, Rng2 As Range, r As Long
Set Rng1 = Range("b2", Range("b" & Rows.Count).End(xlUp))
Set Rng2 = Range("e2", Range("e" & Rows.Count).End(xlUp))
a = Rng1.Value
b = Rng2.Value
r = Application.Max(UBound(a, 1), UBound(b, 1))
ReDim w(1 To r, 1 To 1)
For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
x = Application.Match(a(i, 1), b, 0)
If IsError(x) Then j = j + 1: w(j, 1) = a(i, 1)
End If
Next
If j > 0 Then Range("c2").Resize(j).Value = w
End Sub

HTH

david000
01-30-2008, 02:12 AM
Set Rng1 = Range("b2", Range("b" & Rows.Count).End(xlUp))
Set Rng2 = Range("e2", Range("e" & Rows.Count).End(xlUp))
If j > 0 Then Range("c2").Resize(j).Value = w


You'll need to just change the referances from b2, e2, c2 to b5, e5, and c5. Then remove any data underneth not being used. Like in your sample workbook.


Krishna Kumar,


x = Application.Match(a(i, 1), b, 0)
If IsError(x) Then j = j + 1: w(j, 1) = a(i, 1)


Very nice use of an error!

vzachin
01-31-2008, 05:06 AM
hi Krishna Kumar,

thanks so much for this code. wish i understand how it works


david000,
thanks for the headsup on correcting the references.


thanks again
zach