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.

 

[stylencia18]

 

[sandy666]

you mean

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

and so on....

columns order can be changed of course

 

[sandy666]

if post#12 is not what you want I give up

maybe someone else will give you what you want

btw. what is your Excel version?

 

[stylencia18]

That looks good. I am running version 2013

 

[sandy666]

do you have Power Query add-in installed?
if not, you can download it from the MS website for free

here is an example file but PowerQuery add-in is required

 

[MickG]

Another option:-
Data in "A & B" starting row2, Results start "C2".

Option Explicit
[COLOR="Navy"]Dim[/COLOR] sRay() [COLOR="Navy"]As[/COLOR] Variant

[COLOR="Navy"]Sub[/COLOR] Dups()
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nray(), Q [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.Range("A1").CurrentRegion.Resize(, 2)
[COLOR="Navy"]With[/COLOR] Rng
   .Sort Key1:=.Range("A1"), Key2:=.Range("B1"), Header:=xlYes
[COLOR="Navy"]End[/COLOR] With
p = 2
Ray = Rng.Value
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
        ReDim nray(1 To 1)
            nray(1) = Ray(n, 2)
            .Add Ray(n, 1), nray
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Ray(n, 1))
            ReDim Preserve Q(1 To UBound(Q) + 1)
           Q(UBound(Q)) = Ray(n, 2)
        .Item(Ray(n, 1)) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]Dim[/COLOR] rRng [COLOR="Navy"]As[/COLOR] Range, K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] vElements, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
        vElements = .Item(K) 
        ReDim vresult(1 To p)
        Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
Call nCount(sRay)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 [COLOR="Navy"]Sub[/COLOR] CombinationsNP(vElements [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] iElement [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] iIndex [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 [COLOR="Navy"]For[/COLOR] i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    [COLOR="Navy"]If[/COLOR] iIndex = p [COLOR="Navy"]Then[/COLOR]
        lRow = lRow + 1
        ReDim Preserve sRay(1 To 2, 1 To lRow)
        sRay(1, lRow) = vresult(1)
        sRay(2, lRow) = vresult(2)
    [COLOR="Navy"]Else[/COLOR]
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] nCount(R)
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(R, 2)
txt = R(1, n) & "," & R(2, n)
    [COLOR="Navy"]If[/COLOR] Not .Exists(txt) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        .Add txt, c
        Cells(c, "C") = txt: Cells(c, "D") = 1
    [COLOR="Navy"]Else[/COLOR]
        Cells(.Item(txt), "D") = Cells(.Item(txt), "D") + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick