How to find a duplicate value from two cell values

Hi All,

I have column A with values as initial value and column B with final value, say 1000 in a cell of column A and Value and 1999 in a cell of corresponding row of column B that mean both columns make a range of numbers containing values 1000 till 1999 for that row. I need to find the duplicates for any any occurrences. For example if there is another row with range of 1900 till 2500, this means that there are duplicate numbers from 1900 till 1999. Business provide us list of accounts with ranges listed in Column A and B that we upload in Database. We cannot have duplicate account numbers and need to identify those before uploading in database. Looking to create a VBA script that could identify these duplicate. I would appreciate your help. Thank you.

Column A Column B
1000 1999
1900 2500

hi, try

Please Login or Register  to view this content.

Thank you, k1dr0ck,

Above solution only works for the values listed in Column A and B but ignores what falls in between those numbers. Let me explain one more time with example and data.

Here is the set of columns

Column A Column B
3300600000 3300600999
3300601000 3300601049
3300601050 3300601999
3300602000 3300602019
3300602020 3300604999
3300605000 3300605999
3300606000 3300612999
3300613000 3300613999
3300614000 3300614999
3300615000 3300615999
3300620000 3300620999
3400644000 3400644199
3400651000 3400654499
3400654000 3400666199
3400666000 3400666199

Now if we see last three rows have duplicate account numbers, third last row have accounts from 3400651000 up til 3400654499 so accounts in this row from 3400654000 to 3400654499 are also included in the accounts listed in second last row from 3400654000 till 3400666199. same is the case with second last row and the last, there are accounts that are included in both rows.
Hope I tried to make you understand.
Please feel free if you have any question.

if I understand correctly retrieve the duplicate values that intersect between column A and B?

try the attach file i used your data on post #4

the macro takes a while to generate the result
the duplicate values are shown in column D

Please Login or Register  to view this content.

Thank you, so much k1dr0ck and sktneer.
Both worked. But I changed k1dr0ck script so can highlight row that is getting duplicated. But I am now getting overflow as this file is pretty big.
Issue comes in the loop - cstartA = CLng(Sheet2.Range("D" & x).Value)

I am attaching the whole file. your help would truly be appreciated.
Thank you for support.

Here is the VBA code I am using, and underlined bold where I get overflow error after running for a while. It probably process almost 2/3 of file as I see Reds in the row uptil that before its error out.

Sub find_intersect()
Dim startA As Long, stopB As Long, cstartA As Long, cstopB As Long, lastrw As Long, x As Long, i As Long, y As Long, svalue As Long, cvalue As Long, rowD As Long


lastrw = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrw
startA = Sheet2.Range("A" & i).Value
stopB = Sheet2.Range("B" & i).Value
x = i + 1
For y = x To lastrw
cstartA = CLng(Sheet2.Range("A" & x).Value)
cstopB = CLng(Sheet2.Range("B" & x).Value)
If Sheet2.Range("A" & x).Row > lastrw Then
Exit Sub
Else
For svalue = startA To stopB
For cvalue = cstartA To cstopB
If cvalue = svalue Then
rowD = rowD + 1

Range("A" & i & ":B" & i).Interior.Color = vbRed

End If
Next cvalue
Next svalue
End If
Next y
Next i

End Sub

Thank you, so much k1dr0ck and sktneer.
I used sktneer script and it worked perfect, also very efficient.

Regards