Combine two records into one based on a different tab

[SpunkyElderBerry]

Hello,

I'm in the process of expanding on a macro to look at customer ID and the product name they ordered. If they ordered a set of a particular product then it will combine both product name into one and delete the oldest record.




I have created another tab that combines the product name condensed based on the following

Test 1	Test 2	Test To use
ABC123	ABC456	ABC123456
PEN541	PENV2	PEN541V2

<tbody>
</tbody>

For example here is a small list of customers that has two products. Based on the table above if a customer has test 1 and test 2 then it should be combine into "Test to use".

Record #	Customer Name	Product Name	Product Name Condensed
101	James Smith	ABC-123	ABC123
101	James Smith	ABC-456	ABC456
202	Jill Jones	PEN-541	PEN541
202	Jill Jones	PEN-V2	PENV2

<tbody>
</tbody>

The output of the macro should be the following:

Record #	Customer Name	Product Name	Product Name Condensed
101	James Smith	ABC123456	ABC123456
202	Jill Jones	PEN541V2	PEN541V2

<tbody>
</tbody>

Here if the code i been using thus far

Sub DupTestDelete()
'
' MaskDuplicateTests Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Sheets("Tests").Activate
For i = 2 To Sheets.Count
    pickTest.ComboBox1.AddItem Sheets(i).Name
Next i
pickTest.Show
End Sub


Sub MaskDuplicateTests(pickedTest As String)


screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim acc As Long
acc = Cells.Find(What:="Record #", LookIn:=xlFormulas, LookAt:= _
       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
       , SearchFormat:=False).Column
Dim patID As Long
patID = Cells.Find(What:="Customer ID", LookIn:=xlFormulas, LookAt:= _
       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
       , SearchFormat:=False).Column
Dim TestName As Long
TestName = Cells.Find(What:="Product Name", LookIn:=xlFormulas, LookAt:= _
       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
       , SearchFormat:=False).Column
Dim TestNameCon As Long
TestNameCon = Cells.Find(What:="Product Name Condensed", LookIn:=xlFormulas, LookAt:= _
       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
       , SearchFormat:=False).Column
If TestName = TestNameCon Then
    TestName = Cells.Find(What:="Test Name", LookIn:=xlFormulas, LookAt:= _
       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
       , SearchFormat:=False).Column
End If
Dim x As Long
Dim y As Long
Dim firstTest As String
Dim secondTest As String
x = 2
Do While Sheets("Tests").Cells(x, 1).Value <> ""
    If Sheets("Tests").Cells(x, patID).Value = Sheets("Tests").Cells(x + 1, patID).Value Then
        firstTest = LCase(Trim(Sheets("Tests").Cells(x, TestNameCon).Text))
        secondTest = LCase(Trim(Sheets("Tests").Cells(x + 1, TestNameCon).Text))
        If firstTest = secondTest Then
            If Sheets("Tests").Cells(x, acc).Value > Sheets("Tests").Cells(x + 1, acc).Value Then
                Sheets("Tests").Rows(x + 1).Delete
            ElseIf Sheets("Tests").Cells(x, acc).Value < Sheets("Tests").Cells(x + 1, acc).Value Then
                Sheets("Tests").Rows(x).Delete
            Else
                If InStr(LCase(Sheets("Tests").Cells(x, TestName).Text), "step2") > 0 Then
                    Sheets("Tests").Rows(x + 1).Delete
                ElseIf InStr(LCase(Sheets("Tests").Cells(x + 1, TestName).Text), "step2") > 0 Then
                    Sheets("Tests").Rows(x).Delete
                Else
                    x = x + 1
                End If
            End If
            GoTo vbaNeedsContinueStatements
        End If
        y = 2
        Do While Sheets(pickedTest).Cells(y, 1).Value <> ""
            If LCase(Trim(Sheets(pickedTest).Cells(y, 1).Text)) = firstTest Then
                If LCase(Trim(Sheets(pickedTest).Cells(y, 2).Text)) = secondTest Or LCase(Trim(Sheets(pickedTest).Cells(y, 2).Text)) = "anything" Then
                    If LCase(Trim(Sheets(pickedTest).Cells(y, 3).Text)) = "both" Then
                    ElseIf LCase(Trim(Sheets(pickedTest).Cells(y, 3).Text)) = firstTest Then
                        Sheets("Tests").Rows(x + 1).Delete
                        x = x - 1
                    Else
                        Sheets("Tests").Rows(x).Delete
                        x = x - 1
                    End If
                    Exit Do
                End If
            ElseIf LCase(Trim(Sheets(pickedTest).Cells(y, 1).Text)) = secondTest Then
                If LCase(Trim(Sheets(pickedTest).Cells(y, 2).Text)) = firstTest Or LCase(Trim(Sheets(pickedTest).Cells(y, 2).Text)) = "anything" Then
                    If LCase(Trim(Sheets(pickedTest).Cells(y, 3).Text)) = "both" Then
                    ElseIf LCase(Trim(Sheets(pickedTest).Cells(y, 3).Text)) = firstTest Then
                        Sheets("Tests").Rows(x + 1).Delete
                        x = x - 1
                    Else
                        Sheets("Tests").Rows(x).Delete
                        x = x - 1
                    End If
                    Exit Do
                End If
            End If
            y = y + 1
        Loop
    End If
    x = x + 1
vbaNeedsContinueStatements:
Loop
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
End Sub

Any help would be much appreciated. Thanks in advanced!