-1

Below is my requirement

1.Two sheets are available , say Sh1, Sh2

2.Row count differs between both the sheets(row count may/may not be the same between them)

3.Used Column of the sheets varies for data considered for comparison But at any given point of time, both sheets will have same number of Used columns ( ideally speaking range is Dynamic for both sheets)

4.Now I need to perform comparison between them and find out the excess records in Sh1, Sh2 and save them in diff worksheets Sh3, Sh4 or so.

5.The comparison performed needs to be a Row level Comparison between Sh1 & Sh2 6.I prefer using Arrays (Loading the worksheets to Arrays) and perform comparison on it and return value - PERFORMANCE PLAYS A MAJOR ROLE SINCE THE DATA MAY BE OFF MILLIONS OF RECORDS

  1. Is there a way to perform comparison of two Arrays whose size are non-identical ?

  2. Matched rows can be Ignored

  3. Is there a way to apply some Join() functions and read a complete row from Array1 and compare them with Array2 ?

Sorry If im violating the Forum rules by Any means !

I hope there is no Discussion on Comparing Two sheets with different Dynamic ranges to find the mismatches either here or anywhere

I mostly see ppl limiting the comparison to only one Column , or just for fixed ranges

My ULTIMATE AIM IS TO DO SOME VBA CODE WHICH OPERATES KIND OF "BEYOND COMPARE"(Well not exact behavior, read it a like)

user3586153
  • 1
  • 1
  • Is importing these files into MS Access an option for you? It lends itself very well to this kind of situation. – RubberDuck Apr 29 '14 at 16:50

1 Answers1

0

I have been working on a similar kind of vba macro. hope the below code helps !

All you need is to picture and include is 1)Row level comparison if possible 2)Looping through arrays and finding the Difference

** I am

Code shown below :

'Comparison Code

Sub CompareMacro()

Application.ScreenUpdating = True
Dim sheet1, sheet2, sheet4, sheet3 As Worksheet
Dim rComp, rcomp1 As Range, addy As String, addz As String
Dim iRow As Long, jCol As Long
Dim kRow As Long, lCol As Long
Dim strFileRange As String
Dim strDBRange As String



Set sheet1 = Sheets("File")
Set sheet2 = Sheets("DB")
Set sheet3 = Sheets("File(-)DB")
Set sheet4 = Sheets("DB(-)File")


sheet1.Select
Set rComp = sheet1.UsedRange

sheet2.Select
Set rcomp1 = sheet2.UsedRange


addy = rComp.Address
addz = rcomp1.Address
ary1 = rComp
ary2 = rcomp1
ary3 = sheet3.Range(addy)
ary4 = sheet4.Range(addz)

'*********File MINUS DB code goes here*********

'Step 1
'VALIDATE IF THE ROW1 OF File matches with Any of the Row in DB
'This step should include Iteration of Rows in DB range


'Step 2
'Proceed with Next row (ROW1+1 OF File) if Match Occurs
'This step contains incremental of File row & Starting comparison from First Row of DB

'Step 3
'If no Match occurs , Add the Specific Row to ary3
'This step captures the complete Mismatch record of File Row


'*********DB MINUS File code goes here*********


'Similar to the Flow of File MINUS DB



'adding the Array3 & 4 resultant Sheets

sheet3.Range(addy) = ary3
sheet4.Range(addz) = ary4

End Sub

Matt
  • 1