Comparing data in Excel

0 votes

I have exported CSV files from a Development SQL Server and another from Production.

The table (in the database) has two columns

  1. UserID
  2. DocumentID

both of these should be unique values.

When those two combinations are combined, I want to be able to confirm that they match the other environment.

I've so far imported both CSV files into separate Excel worksheets. I'm not sure what to do next in order to compare these columns.

I looked up a few things on Google, but I'm not sure how to proceed because there are so many various types of replies.

I can only use conditional formatting if I choose a single column. I need both columns combined, please.

Jan 20, 2023 in Others by Kithuzzz
• 38,000 points
497 views

1 answer to this question.

0 votes

A quick and mildly dirty VBA approach. I assumed your workbook consists of two worksheets, each containing two columns with headers.

Option Explicit
Sub SoftwareIsFun()
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim dicObj As Object
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim i As Long

    Set dicObj = CreateObject("Scripting.Dictionary")
    Set wks1 = ThisWorkbook.Worksheets(1)
    Set wks2 = ThisWorkbook.Worksheets(2)
    
    With wks1
        lastRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow1
            If Not dicObj.Exists(.Range("A" & i).Value) Then
                dicObj.Add .Range("A" & i).Value, .Range("B" & i).Value
            Else
                .Range("C" & i).Value = "UserID already exists"
            End If
        Next i
    End With
    
    With wks2
        lastRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow2
            If dicObj.Exists(.Range("A" & i).Value) Then
                If .Range("B" & i).Value = dicObj.Item(.Range("A" & i).Value) Then
                    .Rows(i).Interior.Color = vbGreen
                Else
                    .Rows(i).Interior.Color = vbRed
                End If
            Else
                .Rows(i).Interior.Color = vbRed
            End If
        Next i
    End With
End Sub
answered Jan 20, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
908 views
0 votes
1 answer
0 votes
1 answer

How search data in excel with openpyxl?

The method iter_rows in the library has ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
7,161 views
0 votes
1 answer

lookup Data in Excel

Without VBA, it can be done in ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
715 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,246 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,692 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
954 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,083 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,600 points
804 views
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
2,597 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP