Matching Values and Copying to A new sheet.

**adienspade** · 09-29-2008, 12:04 PM

Correct on both, conformation Number = Order and Line Number = Line Note.

Hopefully I will anwser the 3rd question well enough to help out.
You can take the details from sheet 2 but the reason for sheet one is to match the part number with the detail column on sheet 2. I assumed you would have to match the two together to get the part and detail to match on sheet three, unless I am way overthinking it here.

**MatrixMan** · 09-29-2008, 12:46 PM

OK - let's make our terms consistent; you have:
Sheet1 - Order, Line Note & Part Number
Sheet2 - Order, Line Note & Detail.

You want to find all records where Order & Line Note are the same across Sheets 1 & 2 and you want Order, Line Note & Part Number (which can all be taken from Sheet1) copied to Sheet3 ... Is this right? Or do you want the Detail from Sheet2 as well?

**adienspade** · 09-29-2008, 12:55 PM

Correct on terms for sheets 1 and 2, and I appoligize for making this confusing.

The actual line not doesn't need to be copied over the the 3rd sheet. The only things needed for sheet three is Order, Part Number, and Detail.

**MatrixMan** · 09-29-2008, 02:30 PM

OK - here you go ...

Option Explicit
Option Base 1

Sub MatchAndPaste()
Dim strPartsSheet As String, strDetailSheet As String, strSummarySheet As String
Dim intFirstDataRow As Integer, intLastDataRow1 As Integer, intLastDataRow2 As Integer
Dim sht As Worksheet
Dim strPart As String, strDetail As String, strOrder As String
Dim intCount As Integer

Dim i As Integer, j As Integer

    'setup/intialise:
    strPartsSheet = "Sheet1"
    strDetailSheet = "Sheet2"
    strSummarySheet = "Sheet3"
    intFirstDataRow = 2
    
    intLastDataRow1 = Worksheets(strPartsSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count
    intLastDataRow2 = Worksheets(strDetailSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count
    
    For Each sht In Worksheets
        If sht.Name = strPartsSheet Or sht.Name = strDetailSheet Then
            Worksheets(sht.Name).Select
            Worksheets(sht.Name).Range("A1").Sort _
                key1:=Worksheets(sht.Name).Columns("A"), order1:=xlAscending, _
                key2:=Worksheets(sht.Name).Columns("B"), order2:=xlAscending, _
                header:=xlYes
        End If
    Next sht
    
    intCount = 0
    Worksheets(strSummarySheet).Cells.ClearContents
    
    For i = intFirstDataRow To intLastDataRow1
        For j = i To intLastDataRow2
            If Worksheets(1).Range("A" & i) = Worksheets(2).Range("A" & j) Then
                If Worksheets(1).Range("B" & i) = Worksheets(2).Range("B" & j) Then
                    strOrder = Worksheets(1).Cells(i, 1).Text
                    strPart = Worksheets(1).Cells(i, 3).Text
                    strDetail = Worksheets(2).Cells(j, 3).Text
                    intCount = intCount + 1
                    Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder
                    Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart
                    Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail
                    Exit For   'assuming only 1 match is possible.
                End If
            End If
        Next j
    Next i
    With Worksheets(strSummarySheet)
        .Columns("A:C").NumberFormat = "@"
        .Columns("A:C").HorizontalAlignment = xlRight
        .Cells(1, 1).Value = "Order Number"
        .Cells(1, 2).Value = "Part Number"
        .Cells(1, 3).Value = "Detail"
        .Range("A1").Sort _
            key1:=Worksheets(3).Columns("A"), order1:=xlAscending, _
            key2:=Worksheets(3).Columns("B"), order2:=xlAscending, _
            header:=xlYes
    End With

End Sub

**adienspade** · 09-29-2008, 03:38 PM

Thank you so very much that is exactly what I was needing! You are a hero in my books. I have been struggling with this for a week now on this issue and you made it look so easy, my hat is off to you.

**adienspade** · 10-03-2008, 10:54 AM

I ran into a small problem, lol there is a possiblility that there might not be a match. Basicly I have an order which says it has a line note(page 1), if you go to the Ordernotes(page2) tab there is no order. On the attachment I threw a order with note on page one and left it out on the second tab for an example if anyone could help.

Second I know it is possible I just can't think of it, if there is no match after going through all the orders, then I need it to copy the order and part over and then go to the next order.

I haven't change the code to much as of yet. Only thing I have done is change the rows, sheets locations. I have attached the original code, and my minor modification as of yet. I commented out all the code that I have worked on.

**MatrixMan** · 10-03-2008, 12:24 PM

Hi Aidenspade ... sure - no problem: here's an amended code that will trap this for you; I've annotated what's going on in the comments so you can follow it ... I've also added a boolean variable called booMatchFound.

Option Explicit
Option Base 1

Sub MatchAndPaste()
Dim strPartsSheet As String, strDetailSheet As String, strSummarySheet As String
Dim intFirstDataRow As Integer, intLastDataRow1 As Integer, intLastDataRow2 As Integer
Dim sht As Worksheet
Dim strPart As String, strDetail As String, strOrder As String
Dim intCount As Integer
Dim booMatchFound As Boolean
Dim i As Integer, j As Integer

    'setup/intialise:
    strPartsSheet = "Sheet1"
    strDetailSheet = "Sheet2"
    strSummarySheet = "Sheet3"
    intFirstDataRow = 2
    
    intLastDataRow1 = Worksheets(strPartsSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count
    intLastDataRow2 = Worksheets(strDetailSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count
    
    For Each sht In Worksheets
        If sht.Name = strPartsSheet Or sht.Name = strDetailSheet Then
            Worksheets(sht.Name).Select 'selects sheet1
            Worksheets(sht.Name).Range("A1").Sort _
                key1:=Worksheets(sht.Name).Columns("A"), order1:=xlAscending, _
                key2:=Worksheets(sht.Name).Columns("B"), order2:=xlAscending, _
                header:=xlYes
        End If 'sorts pages to assending based on order.
    Next sht
    
    intCount = 0
    Worksheets(strSummarySheet).Cells.ClearContents 'Clears Page three
        
    For i = intFirstDataRow To intLastDataRow1 'Loop till end of sheet
        strOrder = Worksheets(1).Cells(i, 1).Text
        strPart = Worksheets(1).Cells(i, 3).Text
        strDetail = "NO MATCH"
        For j = i To intLastDataRow2
            If Worksheets(1).Range("A" & i) = Worksheets(2).Range("A" & j) Then
                If Worksheets(1).Range("B" & i) = Worksheets(2).Range("B" & j) Then
                    strDetail = Worksheets(2).Cells(j, 3).Text
                    intCount = intCount + 1
                    Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder
                    Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart
                    Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail
                    booMatchFound = True
                    Exit For   'assuming only 1 match is possible.
                End If
            End If
        Next j
        'if you get to here, you've cycled through the current record in sheet1 and attempted to
        'match it against all the records in sheet2 and found no match; in this case, need to
        'copy just the Order and Part Number to the Summary sheet (Sheet3):
        If booMatchFound = False Then
            intCount = intCount + 1
            Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder
            Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart
            Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail
            Exit For
        Else
            booMatchFound = False
        End If
    Next i
    
    With Worksheets(strSummarySheet)
        .Columns("A:C").NumberFormat = "@"
        .Columns("A:C").HorizontalAlignment = xlRight
        .Cells(1, 1).Value = "Order Number"
        .Cells(1, 2).Value = "Part Number"
        .Cells(1, 3).Value = "Detail"
        .Range("A1").Sort _
            key1:=Worksheets(3).Columns("A"), order1:=xlAscending, _
            key2:=Worksheets(3).Columns("B"), order2:=xlAscending, _
            header:=xlYes
    End With

End Sub