CODEWITHSUNDEEP
excelcompare
user2324097
user2324097

Reputation: 41

How to compare two columns in Excel (from different sheets) and replace values in the corresponding column?

I have two excel sheets with same dimension. I need to compare column a in Sheet 1 with column a in Sheet 2, such that I can find any rows with a value "1" in sheet 1, then I need to find the corresponding row in sheet 2 and replace that with NAN.

Any help will be very appreciated.

Thanks

Upvotes: 0

Views: 4577

Answers (3)

Timh
Timh

Reputation: 512

Sub compareRange()
    Dim ran1, ran2 As Range
    Dim index As Integer
    Set ran1 = Sheet1.Range("a1:a50")
    Set ran2 = Sheet2.Range("a1:a50")

    index = 0
    For Each c In ran1.Cells
        index = index + 1
        If (c.Value = 1) Then
            ran2.Cells(index).Value = "NAN"
        End If
    Next
End Sub

Upvotes: 1

bcorso
bcorso

Reputation: 47058

I'm not sure if you can overwrite a cell from a different cell, but you could do this:

=IF(Sheet1!A1=1, "NAN", Sheet2!A1)

In Sheet3, copy the above to MxN cells (the dimensions of sheet1 and sheet2). This will give an identical copy of Sheet2 with NANs in cells where the corresponding sheet1 contains a 1.

Upvotes: 0

Cara Dong
Cara Dong

Reputation: 1

Function Vlookup could solve your problem. The help guide of Excel has an clear description of the function.

Upvotes: 0

Related Questions