CODEWITHSUNDEEP
vbaexcelexcel-formula
JRU
JRU

Reputation: 327

Compare two columns from multiple sheets ans extract values

It may be a repeated question, but I couldn't find an effective solution anywhere.

One of my clients needs a weekly update on projects. They download an excel from their ERP consisting of multiple columns and I have to comment status on the last column. Every week I will get a fresh copy and all my previous entries will be cleared, then its a repeated job for me. I just want to see what I commented last week and copy paste the same in the new sheet.

Problems:

Sheet 1

enter image description here

Sheet 2

Sheet 2

For the new rows in Sheet 2, I will update the comments manually. But please help me on copying the repeated rows, which I entered on sheet1

Looking for some expert solutions

Thanks

Upvotes: 0

Views: 385

Answers (3)

DisplayName
DisplayName

Reputation: 13386

using Dictionary object

Option Explicit

Sub main()
    Dim dict As Object
    Dim cell As Range

    Set dict = CreateObject("Scripting.Dictionary")
    With Worksheets("Sheet1")
        For Each cell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
            dict.Item(cell.Value2 & "|" & cell.Offset(, 1).Value2) = cell.Offset(, 2).Value2
        Next
    End With

    With Worksheets("Sheet2")
        For Each cell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
            If dict.exists(cell.Value2 & "|" & cell.Offset(, 1).Value2) Then cell.Offset(, 2).Value = dict.Item(cell.Value2 & "|" & cell.Offset(, 1).Value2)
        Next
    End With
End Sub

Upvotes: 0

Nandan A
Nandan A

Reputation: 2932

Try the below code. It worked for me.

Input sheet (Sheet1):

enter image description here

Below is the code:

Sub Comapre()

Dim TotalNames As Integer

Dim NameInSheet2 As String, PO As String

TotalNames = Worksheets("Sheet2").Range("A1").End(xlDown).Row

For i = 2 To TotalNames

NameInSheet2 = Worksheets("Sheet2").Range("A" & i).Value

PO = Worksheets("Sheet2").Range("B" & i).Value

Worksheets("Sheet1").Activate

'Finds the cell value in Sheet1

Set cell = Cells.Find(What:=NameInSheet2, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)


    If cell Is Nothing Then

        Else

            'If it found the name then it will compare the PO value

            If cell.Offset(, 1).Value = PO Then

                'If Name and Po value matched then comment will be copied to sheet2.

                Worksheets("Sheet2").Range("C" & i) = cell.Offset(, 2).Value

            End If

        End If

    Next


End Sub

Output Sheet(Sheet2):

enter image description here

Please let me know if my answer fits your question.

Upvotes: 2

t6nnp6nn
t6nnp6nn

Reputation: 317

If I understand you right a simple VLOOKUP() should do the job.

I am assuming the PO numbers in a table are unique.

You take new sheet and look for the last comment you had for this PO.

in the Sheet2 eg in cell C2 you would type like:

=VLOOKUP(B2,Sheet1!B:C,2,FALSE)

This will look up your PO nr 4500253 in the Sheet1 Column B (with an exact match) and return the matched row value from Column C. An error is returned if no match is found.

Upvotes: 1

Related Questions