Detecting Duplicate Pairs

[stylencia18]

Please help!! I have a large list ofcompanies (approx. 180,000 rows) that placed bids on different contracts. I amtrying to determine how to figure out how many TOTAL times each company bidagainst one another. Any suggestions?? Sample dataset is below:


Contract Number (Column A) Company Bidder Name (Column S)
15461 Fun Company Inc.
15461 Joe’s Pools
15461 Molly’s Restaurantand Co.
15461 Alpha’s Dreamhouse
15461 Wonder Inc.
15461 Go N Wash LLC
22222 Go N Wash LLC
22222 Fun Company Inc.
22222 Sandra’s Steak NMore
22222 Crab Co.
22222 Designer Outlet
22222 Shoes For All
22222 Joe’s Pools
22222 Hard Life LLC
36363 Go N Wash LLC
36363 All the Time Inc.
36363 Peruvian ChickenCo.
36363 Crab Co.
36363 Maryland Crab Co.
36363 Blue Crab Co.
36363 Wonder Inc.
36363 Alpha Streams LLC
36363 Day N Night Inc.
36363 Fun Company Inc.
36363 Allison’sPlayground Inc.

 

[mse330]

Hey stylencia18,

This is one way to do it via a VBA code if you like. The way it works that it will show you an input box where you have to enter the first company name & then another input box for the second company name then it will show you the number of contacts they bid against. Let me know if this works for you or in case you are having any issues

PS. Couple of remarks:

1. I assumed that your data starts in cell A1 with a header in row 1
2. When you write the company name in the input box, it will also search partial text so ensure to have the exact company name to avoid mistakes

Sub CompaniesList()
Dim Dic As Object, k As Variant, Ar As Variant, CompA As String, CompB As String, Cnt As Long
Set Dic = CreateObject("scripting.dictionary")
CompA = InputBox("Please enter first company name below ...")
CompB = InputBox("Please enter second company name below ...")
Ar = ActiveSheet.Range("A1").CurrentRegion.Value
For i = 2 To UBound(Ar)
    If Not Dic.exists(Ar(i, 1)) Then
        Dic.Add Ar(i, 1), Ar(i, 19)
    Else
        Dic(Ar(i, 1)) = Dic(Ar(i, 1)) & "," & Ar(i, 19)
    End If
Next i
ReDim Ar(1 To Dic.Count, 1 To 1)
For Each k In Dic.keys
    If InStr(1, Dic(k), CompA, 1) > 0 And InStr(1, Dic(k), CompB, 1) > 0 Then
        Cnt = Cnt + 1
        Ar(Cnt, 1) = k
    End If
Next
If MsgBox("There is " & Cnt & " contract(s) that both" & vbNewLine & _
    "    a. " & CompA & vbNewLine & "    b. " & CompB & vbNewLine & _
    "placed bids against. Would you like to see the contract numbers in a separate new sheet ?" _
    , vbYesNo + vbInformation) = vbYes Then
    With Sheets.Add(after:=Sheets(Sheets.Count))
        .Range("A1") = "Contract Number"
        .Range("A2").Resize(UBound(Ar)) = Ar
    End With
End If
End Sub

 

[stylencia18]

Thank you for replying! If I have a list of 8,000 companies do I need to run a macro for each pair of companies?

Hey stylencia18,

This is one way to do it via a VBA code if you like. The way it works that it will show you an input box where you have to enter the first company name & then another input box for the second company name then it will show you the number of contacts they bid against. Let me know if this works for you or in case you are having any issues

PS. Couple of remarks:

1. I assumed that your data starts in cell A1 with a header in row 1
2. When you write the company name in the input box, it will also search partial text so ensure to have the exact company name to avoid mistakes

Sub CompaniesList()
Dim Dic As Object, k As Variant, Ar As Variant, CompA As String, CompB As String, Cnt As Long
Set Dic = CreateObject("scripting.dictionary")
CompA = InputBox("Please enter first company name below ...")
CompB = InputBox("Please enter second company name below ...")
Ar = ActiveSheet.Range("A1").CurrentRegion.Value
For i = 2 To UBound(Ar)
    If Not Dic.exists(Ar(i, 1)) Then
        Dic.Add Ar(i, 1), Ar(i, 19)
    Else
        Dic(Ar(i, 1)) = Dic(Ar(i, 1)) & "," & Ar(i, 19)
    End If
Next i
ReDim Ar(1 To Dic.Count, 1 To 1)
For Each k In Dic.keys
    If InStr(1, Dic(k), CompA, 1) > 0 And InStr(1, Dic(k), CompB, 1) > 0 Then
        Cnt = Cnt + 1
        Ar(Cnt, 1) = k
    End If
Next
If MsgBox("There is " & Cnt & " contract(s) that both" & vbNewLine & _
    "    a. " & CompA & vbNewLine & "    b. " & CompB & vbNewLine & _
    "placed bids against. Would you like to see the contract numbers in a separate new sheet ?" _
    , vbYesNo + vbInformation) = vbYes Then
    With Sheets.Add(after:=Sheets(Sheets.Count))
        .Range("A1") = "Contract Number"
        .Range("A2").Resize(UBound(Ar)) = Ar
    End With
End If
End Sub

 

[mse330]

For the code provided above yes, you would have to run each pair. Can you post a sample of the expected result & I’ll adjust the code accordingly

 

[sandy666]

maybe

Company Bidder Name	Count	Contract Number
Fun Company Inc.	3​	15461, 22222, 36363
Joe’s Pools	2​	15461, 22222
Molly’s Restaurantand Co.	1​	15461
Alpha’s Dreamhouse	1​	15461
Wonder Inc.	2​	15461, 36363
Go N Wash LLC	3​	15461, 22222, 36363
Sandra’s Steak NMore	1​	22222
Crab Co.	2​	22222, 36363
Designer Outlet	1​	22222
Shoes For All	1​	22222
Hard Life LLC	1​	22222
All the Time Inc.	1​	36363
Peruvian ChickenCo.	1​	36363
Maryland Crab Co.	1​	36363
Blue Crab Co.	1​	36363
Alpha Streams LLC	1​	36363
Day N Night Inc.	1​	36363
Allison’sPlayground Inc.	1​	36363

or post expected result for your example

 

[stylencia18]

Hey mse330:

I was hoping to obtain aresult similar to the following:


Company Names Numberof Contracts Bid Against Each Other
Fun Company Inc. & Joe’sPools 1
Go N Wash LLC & FunCompany Inc. 4


Is it possible to generate alist of all potential company pairs and then run a count?


Apologies if this doesn’tmake sense!

 

[sandy666]

here is a part of the list

VS

Fun Company Inc. & Joe’s Pools

Fun Company Inc. & Molly’s Restaurantand Co.

Fun Company Inc. & Alpha’s Dreamhouse

Fun Company Inc. & Wonder Inc.

Fun Company Inc. & Go N Wash LLC

Fun Company Inc. & Sandra’s Steak NMore

Fun Company Inc. & Crab Co.

Fun Company Inc. & Designer Outlet

Fun Company Inc. & Shoes For All

Fun Company Inc. & Hard Life LLC

Fun Company Inc. & All the Time Inc.

Fun Company Inc. & Peruvian ChickenCo.

Fun Company Inc. & Maryland Crab Co.

Fun Company Inc. & Blue Crab Co.

Fun Company Inc. & Alpha Streams LLC

Fun Company Inc. & Day N Night Inc.

Fun Company Inc. & Allison’sPlayground Inc.

Joe’s Pools & Fun Company Inc.

Joe’s Pools & Molly’s Restaurantand Co.

Joe’s Pools & Alpha’s Dreamhouse

Joe’s Pools & Wonder Inc.

Joe’s Pools & Go N Wash LLC

Joe’s Pools & Sandra’s Steak NMore

Joe’s Pools & Crab Co.

Joe’s Pools & Designer Outlet

Joe’s Pools & Shoes For All

Joe’s Pools & Hard Life LLC

Joe’s Pools & All the Time Inc.

Joe’s Pools & Peruvian ChickenCo.

Joe’s Pools & Maryland Crab Co.

Joe’s Pools & Blue Crab Co.

Joe’s Pools & Alpha Streams LLC

Joe’s Pools & Day N Night Inc.

Joe’s Pools & Allison’sPlayground Inc.

can you say what you want to count???

 

[stylencia18]

How did you do that?! I am trying to count the number of contracts they both bid on. In other words, when both company names appear under the same contract number.

 

[sandy666]

like this?

Contract Number	Count	VS
15461​	102​	Fun Company Inc. & Joe’s Pools
15461​	102​	Fun Company Inc. & Molly’s Restaurantand Co.
15461​	102​	Fun Company Inc. & Alpha’s Dreamhouse
15461​	102​	Fun Company Inc. & Wonder Inc.
15461​	102​	Fun Company Inc. & Go N Wash LLC
15461​	102​	Fun Company Inc. & Sandra’s Steak NMore
15461​	102​	Fun Company Inc. & Crab Co.
15461​	102​	Fun Company Inc. & Designer Outlet
15461​	102​	Fun Company Inc. & Shoes For All
15461​	102​	Fun Company Inc. & Hard Life LLC
15461​	102​	Fun Company Inc. & All the Time Inc.
15461​	102​	Fun Company Inc. & Peruvian ChickenCo.
15461​	102​	Fun Company Inc. & Maryland Crab Co.
15461​	102​	Fun Company Inc. & Blue Crab Co.
15461​	102​	Fun Company Inc. & Alpha Streams LLC
15461​	102​	Fun Company Inc. & Day N Night Inc.
15461​	102​	Fun Company Inc. & Allison’sPlayground Inc.
15461​	102​	Joe’s Pools & Fun Company Inc.
15461​	102​	Joe’s Pools & Molly’s Restaurantand Co.
15461​	102​	Joe’s Pools & Alpha’s Dreamhouse
15461​	102​	Joe’s Pools & Wonder Inc.
15461​	102​	Joe’s Pools & Go N Wash LLC
15461​	102​	Joe’s Pools & Sandra’s Steak NMore
15461​	102​	Joe’s Pools & Crab Co.
15461​	102​	Joe’s Pools & Designer Outlet
15461​	102​	Joe’s Pools & Shoes For All
15461​	102​	Joe’s Pools & Hard Life LLC
15461​	102​	Joe’s Pools & All the Time Inc.
15461​	102​	Joe’s Pools & Peruvian ChickenCo.
15461​	102​	Joe’s Pools & Maryland Crab Co.
15461​	102​	Joe’s Pools & Blue Crab Co.
15461​	102​	Joe’s Pools & Alpha Streams LLC
15461​	102​	Joe’s Pools & Day N Night Inc.
15461​	102​	Joe’s Pools & Allison’sPlayground Inc.
15461​	102​	Molly’s Restaurantand Co. & Fun Company Inc.
15461​	102​	Molly’s Restaurantand Co. & Joe’s Pools
15461​	102​	Molly’s Restaurantand Co. & Alpha’s Dreamhouse

but this is 425 rows...

or like this with PivotTable

Contract Number	Count	VS
15461​	102​	Alpha’s Dreamhouse & All the Time Inc.
		Alpha’s Dreamhouse & Allison’sPlayground Inc.
		Alpha’s Dreamhouse & Alpha Streams LLC
		Alpha’s Dreamhouse & Blue Crab Co.
		Alpha’s Dreamhouse & Crab Co.
		Alpha’s Dreamhouse & Day N Night Inc.
		Alpha’s Dreamhouse & Designer Outlet
		Alpha’s Dreamhouse & Fun Company Inc.
		Alpha’s Dreamhouse & Go N Wash LLC
		Alpha’s Dreamhouse & Hard Life LLC
		Alpha’s Dreamhouse & Joe’s Pools
		Alpha’s Dreamhouse & Maryland Crab Co.
		Alpha’s Dreamhouse & Molly’s Restaurantand Co.
		Alpha’s Dreamhouse & Peruvian ChickenCo.
		Alpha’s Dreamhouse & Sandra’s Steak NMore
		Alpha’s Dreamhouse & Shoes For All
		Alpha’s Dreamhouse & Wonder Inc.
		Fun Company Inc. & All the Time Inc.
		Fun Company Inc. & Allison’sPlayground Inc.
		Fun Company Inc. & Alpha Streams LLC
		Fun Company Inc. & Alpha’s Dreamhouse
		Fun Company Inc. & Blue Crab Co.
		Fun Company Inc. & Crab Co.
		Fun Company Inc. & Day N Night Inc.
		Fun Company Inc. & Designer Outlet

 

[stylencia18]

I was hoping to obtain aresult similar to the following:

Company Names DistinctCount of Contracts Bid Against Each Other Contract Numbers
Fun Company Inc. & Joe’sPools 1 15461

Go N Wash LLC & FunCompany Inc. 2 22222, 36363