Data comparison and extraction (VB Macro)

hi reachspk123, what should happen if Data1 is empty and Data2 is not?

that's how it looks for now, press Start button

thanks a lot watersev for the macro. It works pretty fine for the given data. I'd test it for more iterations. I will get back to you, if I have any further queries.

many thanks for your time and help,

kind regards,
reachspk123

Hello watersev,

thank you for the excel VB macro, for the previous conditions. Could you please help me out in adding another conditional check , before populating the unmatched data in the specified column ranges (also, there is one additional column in data sets 1 and 2)..
Please find the updated algorithm details below:- (Also, I've mentioned the same in the Worksheet'Algorithm')
1. Comparison of each data ID of column F (F7) with all the data ID in column A (A7:An). Comparison check should be made for all data till 'Fn'; n can be 65536 (row limit of Excel)

2. If the data compared does not match, then populate the unmatched data (of data 2) in column F (F7) and the corresponding columns G,H & I in the new column range (K7:Nn) - And, the code now populates only three columns, as per the previous algorithm. Please modify the code accordingly to display all 4 columns .
2a. Please add another conditional check with the above algorithm; i.e., even if the compared data (data1 and data2 IDs) matches (as in step 1), the data versions in columns B and G (of data sets 1 and 2 respectively) should be compared & if the version is different, it should also populate the unmatched data ID and the corresponding columns (G,H & I) in the range(K7:Nn)

3. Please repeat the above algorithm vice-versa (with 1, 2 & 2a conditions) by comparing the data 1 with each of data 2 sets and populate the unmatched data (of data 1) in column A (A7) and the corresponding columns in the new coulmn range (P7:Sn)
It will be helpful, if you could please add few comment lines to the code, for better understanding of the code and for further updates.

Thank you,
kind regards,
reachspk123

as there is some problem with attaching files, here is the code with comments

Option Explicit
Sub test()

Dim data, result, rcount As Long, temp, Data1str As String, Data2str As String, i As Long, j As Long, n As Integer, m As Long

With Sheets("Data-Comparison-sheet")
    
    'check if there is any data to process
    If .Cells(Rows.Count, 1).End(xlUp).Row = 6 And .Cells(Rows.Count, 6).End(xlUp).Row = 6 Then Exit Sub
    
    'take data table to array plus two more columns
    data = .Range("a7", .Cells(.UsedRange.Rows.Count, 1)).Resize(, 11)
    
    'array rows count to variable
    rcount = UBound(data)
    
    'dimensions set for result array
    ReDim result(1 To rcount, 1 To 9)
    
    'join Data ID and Data version for Data1 and Data2
    For i = 1 To rcount
        
        temp = "," & data(i, 1) & "|" & data(i, 2) & ","
        
        If temp <> "" Then
            
            If InStr(Data1str, temp) = 0 Then
                
                Data1str = Data1str & temp
                
                'saving each joined string to array column 10
                data(i, 10) = temp
                
            End If
            
        End If
        
        temp = "," & data(i, 6) & "|" & data(i, 7) & ","
        
        If temp <> "" Then
            
            If InStr(Data2str, temp) = 0 Then
                
                Data2str = Data2str & temp
                
                'saving each joined string to array column 10
                data(i, 11) = temp
                
            End If
            
        End If
        
        
    Next
    
    'main loop checking if the joined string Data2str contains Data1 value and vice versa
    For i = 1 To rcount
        
        If InStr(Data2str, data(i, 10)) = 0 Then
            
            'row counter for result Data1
            j = j + 1
            
            'if data is absent place it to result array
            For n = 6 To 9
        
                result(j, n) = data(i, n - 5)
            
            Next
        
        End If
        
        If InStr(Data1str, data(i, 11)) = 0 Then
            
            'row counter for result Data2
            m = m + 1
            
            'if data is absent place it to result array
            For n = 1 To 4
        
                result(m, n) = data(i, n + 5)
            
            Next
        
        End If
    
    Next
    
    Application.ScreenUpdating = 0
    
    'checking if clearing range required to output result array
    If Application.CountA(Range("k7"), Range("p7")) > 0 Then .UsedRange.Offset(6, 10).Resize(, 9).ClearContents
    
    'if no differencies found exit sub
    If j = 0 And m = 0 Then Exit Sub
    
    'decide which result data is longer measuring number of rows filled
    If j >= m Then .Range("k7").Resize(j, 9) = result Else .Range("k7").Resize(m, 9) = result
    
    Application.ScreenUpdating = 1
        
End With

End Sub

thanks a lot watersev for the macro. I'll test it for more iterations and will get back to you soon.

many thanks for your time and help,
kind regards,
reachspk123

many thanks watersev for the macro.. It works pretty good for all test conditions and the code looks very robust..

thank you so much for your time and support,

have a good weekend!
kind regards,
reachspk123

if the issue is resolved, please mark the thread as solved:

Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.