How to find duplicates in two separate cells

MFish

MFish

Board Regular
Joined
May 9, 2019
Messages
76
I have a code to check for a duplicate in column B7 and downward. I need to somehow configure it so it finds a duplicate in row B7-downward AND C7-downward. Here's the kicker... I need it to be like an "AND" function. It will be OK if the unique ID's are duplicated in column B as long as the unique ID is not duplicated in column C.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]OAK
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]OAK
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]OAK
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]THP
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]THP
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]ONT
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]VIS
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]OAK
[/TD]
[TD]A
[/TD]
[/TR]
</tbody>[/TABLE]
Just imagine a userform that has a drop down list... One for the 3 letter acronyms and the other for A, B, C. Before it can make it onto the sheet... I want the userform, when trying to hit submit, to prompt you that you already have an OAK A on the list, please choose something else. Here's my code already...

Code: 
Private Sub cmdSubmit_Click()


If cmbLH = "" Then
    MsgBox "The Line Haul name cannot be blank. Please choose a valid Line Haul name for the run that you would like to add.", vbOKOnly, "Invalid Line Haul Name"
    Exit Sub
Else: End If

If cmbOrig = "" Then
    MsgBox "The Origination Depot cannot be blank. Please choose a valid Origination Depot selection for the run that you would like to add.", vbOKOnly, "Invalid Origination Depot Name"
    Exit Sub
Else: End If

If cmbDest = "" Then
    MsgBox "The Destination Depot cannot be blank. Please choose a valid Destination Depot selection for the run that you would like to add.", vbOKOnly, "Invalid Destination Depot Name"
    Exit Sub
Else: End If

If txtDrivStart = "" Then
    MsgBox "The Driver's start time cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Driver Start Time"
    Exit Sub
Else: End If

If txtLoad = "" Then
    MsgBox "The Load Ready to Depart Time cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Load Ready Time"
    Exit Sub
Else: End If

If txtDep = "" Then
    MsgBox "The Actual Departure Time cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Actual Departure Time"
    Exit Sub
Else: End If

If txtSeal = "" Then
    MsgBox "The Seal No. cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Seal No."
    Exit Sub
Else: End If

If txtDate = "" Then
    MsgBox "The Date cannot be blank.  Please format the date as mm/dd/yyyy.", vbOKOnly, "Invalid Date"
    Exit Sub
Else: End If

If txtName = "" Then
    MsgBox "The Driver's name cannot be blank.", vbOKOnly, "Invalid Driver Name"
    Exit Sub
Else: End If

If txtTruck = "" Then
    MsgBox "The Truck Number cannot be blank.  If TPC then type TPC", vbOKOnly, "Invalid Truck No."
    Exit Sub
Else: End If

If txtTrailer = "" Then
    MsgBox "The Trailer Number cannot be blank.  If driving a bobtail then type N/A.", vbOKOnly, "Invalid Trailer No."
    Exit Sub
Else: End If

If cmbTPCName = "" Then
    MsgBox "The TPC selector cannot be blank.  If it is not a TPC then choose N/A.", vbOKOnly, "Invalid TPC Selection"
    Exit Sub
Else: End If

'''''' end of validation of blanks
            ''''''''''''''''''''''''
            ''''''''''''''''''''''''
    '''''notify the user if the selection is not from the selection in the list provided
Range("B7").Select
'Check for duplicate bill name
Do Until UCase(ActiveCell) = UCase(cmbLH) Or ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop

If ActiveCell <> "" Then
MsgBox "" & cmbLH & " already exists in the Table. Please choose a different Line Haul Number", _
vbOKOnly, "Duplicate Line Haul Number detected"
frmAdd.Show
Exit Sub

Else: End If





' Cannot override the duplicate line haul number
'forces the user to choose a different line haul number





''stop
''add in the sheet provided below



ActiveCell.Value = cmbLH
ActiveCell.Offset(0, 1).Value = cmbLeg
ActiveCell.Offset(0, 2).Value = txtDate
ActiveCell.Offset(0, 3).Value = cmbOrig
ActiveCell.Offset(0, 4).Value = cmbDest
ActiveCell.Offset(0, 8).Value = txtSeal
ActiveCell.Offset(0, 9).Value = txtName
ActiveCell.Offset(0, 11).Value = txtTruck
ActiveCell.Offset(0, 12).Value = txtTrailer
ActiveCell.Offset(0, 13).Value = cmbTPCName
ActiveCell.Offset(0, 14).Value = txtDrivStart
ActiveCell.Offset(0, 15).Value = txtLoad
ActiveCell.Offset(0, 17).Value = txtDep
If ob53.Value = True Then
ActiveCell.Offset(0, 10).Value = "53"
Else: End If
If ob28.Value = True Then
ActiveCell.Offset(0, 10).Value = "28"
Else: End If
If ob26.Value = True Then
ActiveCell.Offset(0, 10).Value = "26"
Else: End If

Unload Me
'''now sort table
sorttable



'''' move forward onto the selection of option buttons, 53, 28, 26

'''opens up the appropriate userform to fill out line haul load sheet
If ob53 = True Then
frmAdd.Hide
    frm53.Show
End If


If ob28 = True Then
frmAdd.Hide
    frm28.Show
End If

If ob26 = True Then
frmAdd.Hide
    frm26.Show
End If

End Sub

Thanks.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
MFish said:
I have a code to check for a duplicate in column B7 and downward. I need to somehow configure it so it finds a duplicate in row B7-downward AND C7-downward. Here's the kicker... I need it to be like an "AND" function. It will be OK if the unique ID's are duplicated in column B as long as the unique ID is not duplicated in column C.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]OAK[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]OAK[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]OAK[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]THP[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]THP[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]ONT[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]VIS[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]OAK[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]
Just imagine a userform that has a drop down list... One for the 3 letter acronyms and the other for A, B, C. Before it can make it onto the sheet... I want the userform, when trying to hit submit, to prompt you that you already have an OAK A on the list, please choose something else. Here's my code already...

Code: 
Private Sub cmdSubmit_Click()


If cmbLH = "" Then
    MsgBox "The Line Haul name cannot be blank. Please choose a valid Line Haul name for the run that you would like to add.", vbOKOnly, "Invalid Line Haul Name"
    Exit Sub
Else: End If

If cmbOrig = "" Then
    MsgBox "The Origination Depot cannot be blank. Please choose a valid Origination Depot selection for the run that you would like to add.", vbOKOnly, "Invalid Origination Depot Name"
    Exit Sub
Else: End If

If cmbDest = "" Then
    MsgBox "The Destination Depot cannot be blank. Please choose a valid Destination Depot selection for the run that you would like to add.", vbOKOnly, "Invalid Destination Depot Name"
    Exit Sub
Else: End If

If txtDrivStart = "" Then
    MsgBox "The Driver's start time cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Driver Start Time"
    Exit Sub
Else: End If

If txtLoad = "" Then
    MsgBox "The Load Ready to Depart Time cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Load Ready Time"
    Exit Sub
Else: End If

If txtDep = "" Then
    MsgBox "The Actual Departure Time cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Actual Departure Time"
    Exit Sub
Else: End If

If txtSeal = "" Then
    MsgBox "The Seal No. cannot be blank.  Please fill out before moving on.", vbOKOnly, "Invalid Seal No."
    Exit Sub
Else: End If

If txtDate = "" Then
    MsgBox "The Date cannot be blank.  Please format the date as mm/dd/yyyy.", vbOKOnly, "Invalid Date"
    Exit Sub
Else: End If

If txtName = "" Then
    MsgBox "The Driver's name cannot be blank.", vbOKOnly, "Invalid Driver Name"
    Exit Sub
Else: End If

If txtTruck = "" Then
    MsgBox "The Truck Number cannot be blank.  If TPC then type TPC", vbOKOnly, "Invalid Truck No."
    Exit Sub
Else: End If

If txtTrailer = "" Then
    MsgBox "The Trailer Number cannot be blank.  If driving a bobtail then type N/A.", vbOKOnly, "Invalid Trailer No."
    Exit Sub
Else: End If

If cmbTPCName = "" Then
    MsgBox "The TPC selector cannot be blank.  If it is not a TPC then choose N/A.", vbOKOnly, "Invalid TPC Selection"
    Exit Sub
Else: End If

'''''' end of validation of blanks
            ''''''''''''''''''''''''
            ''''''''''''''''''''''''
    '''''notify the user if the selection is not from the selection in the list provided
Range("B7").Select
'Check for duplicate bill name
Do Until UCase(ActiveCell) = UCase(cmbLH) Or ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop

If ActiveCell <> "" Then
MsgBox "" & cmbLH & " already exists in the Table. Please choose a different Line Haul Number", _
vbOKOnly, "Duplicate Line Haul Number detected"
frmAdd.Show
Exit Sub

Else: End If





' Cannot override the duplicate line haul number
'forces the user to choose a different line haul number





''stop
''add in the sheet provided below



ActiveCell.Value = cmbLH
ActiveCell.Offset(0, 1).Value = cmbLeg
ActiveCell.Offset(0, 2).Value = txtDate
ActiveCell.Offset(0, 3).Value = cmbOrig
ActiveCell.Offset(0, 4).Value = cmbDest
ActiveCell.Offset(0, 8).Value = txtSeal
ActiveCell.Offset(0, 9).Value = txtName
ActiveCell.Offset(0, 11).Value = txtTruck
ActiveCell.Offset(0, 12).Value = txtTrailer
ActiveCell.Offset(0, 13).Value = cmbTPCName
ActiveCell.Offset(0, 14).Value = txtDrivStart
ActiveCell.Offset(0, 15).Value = txtLoad
ActiveCell.Offset(0, 17).Value = txtDep
If ob53.Value = True Then
ActiveCell.Offset(0, 10).Value = "53"
Else: End If
If ob28.Value = True Then
ActiveCell.Offset(0, 10).Value = "28"
Else: End If
If ob26.Value = True Then
ActiveCell.Offset(0, 10).Value = "26"
Else: End If

Unload Me
'''now sort table
sorttable



'''' move forward onto the selection of option buttons, 53, 28, 26

'''opens up the appropriate userform to fill out line haul load sheet
If ob53 = True Then
frmAdd.Hide
    frm53.Show
End If


If ob28 = True Then
frmAdd.Hide
    frm28.Show
End If

If ob26 = True Then
frmAdd.Hide
    frm26.Show
End If

End Sub

Thanks.
Click to expand...

Sorry I don't think I understood the question fully, but was just thinking:

Would it help if you concatenated those 2 columns somewhere else to create a single unique ID and just copy that formula all the way down the page? eg Column G =B7&C7 or OAKA, and then just hide that column so you don't have to look at it but can still reference it in vba
 
Last edited:
Upvote 0
Basically I want a code that references the cell next to eachother... A1 and B1. If it sees it's OAK in A1 and A in B1 then the userform won't allow me to submit the two comboboxes of OAK and A, it must move onto OAK and B. I don't know if it makes sense. I don't know how to write a concatenated VBA Code. I know it's with the ampersand key and can use it in a worksheet function on sheets, just don't know it in VBA.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
192
ipbr21054
I
I
  • Question Question
File is allready saved but code should be stopping duplicates being saved
Replies
5
Views
263
ipbr21054
I
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
244
MBarrett
M
R
  • Solved
Message box to stop Macro if two cells are filled
Replies
5
Views
226
Joe4
Joe4
gravanoc
  • Question Question
Certain actions not completed by code
Replies
4
Views
172
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top