Ordering two sets of columns by duplicate values

noclue88 · Aug 8, 2013

Hello,<o

></o

>
I’m trying to do a comparison so that two different sets of columns are duplicates/paired with duplicates in the Zip Codes.<o

></o

>
So I want to change this:<o

></o

>

State<o></o>	Program Name<o></o>	Grantee<o></o>	City<o></o>	Zip <o></o>	ECP Organization Name<o></o>	Zip Code<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System<o></o>	New York<o></o>	10003<o></o>	AIDS Service Center Of Lower Manhattan Inc.<o></o>	10003<o></o>
NY<o></o>	JMHCP<o></o>	NYS Unified Court System<o></o>	New York<o></o>	10004<o></o>	Beth Israel Medical Center<o></o>	10003<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System - BTC<o></o>	New York<o></o>	10004<o></o>	Bowery Residents Committee, Inc.<o></o>	10003<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System: Statewide Evaluation<o></o>	New York<o></o>	10004<o></o>	Institute for Urban Family Health, Inc.<o></o>	10003<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System- Buffalo Veterans Program<o></o>	New York<o></o>	10004<o></o>	NY EYE AND EAR INFIRMARY<o></o>	10003<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System - Queens Voc Ed Program<o></o>	New York<o></o>	10004<o></o>	Project Renewal, Inc.<o></o>	10003<o></o>
NY<o></o>	JMHCP<o></o>	New York City Administration for Children's Services<o></o>	New York<o></o>	10038<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
NY<o></o>	SCA<o></o>	Westchester County<o></o>	White Plains<o></o>	10601<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	The Institute for Family Health<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	The Institute for Family Health<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	American Indian Community House, Inc. (New York, NY)<o></o>	10004<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	NYC HRA/Assessment and Referral Team for AIDS (ART<o></o>	10004<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	AHRC Health Care, Inc. D.B.A. Access Community Health Center<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	HHC Health and Home Care<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	NEW YORK DOWNTOWN HOSPITAL<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Prison Health Services, Inc. (Rikers Transitional)<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Mount Vernon Neighborhood Health Center<o></o>	10601<o></o>

<o

> </o

>
To this:<o

></o

>

State<o></o>	Program Name<o></o>	Grantee<o></o>	City<o></o>	Zip <o></o>	ECP Organization Name<o></o>	Zip Code<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System<o></o>	New York<o></o>	10003<o></o>	AIDS Service Center Of Lower Manhattan Inc.<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Beth Israel Medical Center<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Bowery Residents Committee, Inc.<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Institute for Urban Family Health, Inc.<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	NY EYE AND EAR INFIRMARY<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Project Renewal, Inc.<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	The Institute for Family Health<o></o>	10003<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	The Institute for Family Health<o></o>	10003<o></o>
NY<o></o>	JMHCP<o></o>	NYS Unified Court System<o></o>	New York<o></o>	10004<o></o>	American Indian Community House, Inc. (New York, NY)<o></o>	10004<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System - BTC<o></o>	New York<o></o>	10004<o></o>	NYC HRA/Assessment and Referral Team for AIDS (ART<o></o>	10004<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System: Statewide Evaluation<o></o>	New York<o></o>	10004<o></o>	<o></o>	<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System- Buffalo Veterans Program<o></o>	New York<o></o>	10004<o></o>	<o></o>	<o></o>
NY<o></o>	Drug Courts<o></o>	NYS Unified Court System - Queens Voc Ed Program<o></o>	New York<o></o>	10004<o></o>	<o></o>	<o></o>
NY<o></o>	JMHCP<o></o>	New York City Administration for Children's Services<o></o>	New York<o></o>	10038<o></o>	AHRC Health Care, Inc. D.B.A. Access Community Health Center<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	HHC Health and Home Care<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	NEW YORK DOWNTOWN HOSPITAL<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Prison Health Services, Inc. (Rikers Transitional)<o></o>	10038<o></o>
<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	Sunset Park Health Council, Inc<o></o>	10038<o></o>
NY<o></o>	SCA<o></o>	Westchester County<o></o>	White Plains<o></o>	10601<o></o>	Mount Vernon Neighborhood Health Center<o></o>	10601<o></o>

<o

> </o

>
So both sets of columns may have inserted blank cells for duplicates.<o

></o

>
I’m expecting each set of columns to have no more than 2000 entries.<o

></o

>
Let me know If you have any questions!

Thanks again!<o

></o

>
<o

> </o

>

MickG · Aug 9, 2013

Try this :- Results start "I1"

Code:

[COLOR="Navy"]Sub[/COLOR] MG09Aug14
[COLOR="Navy"]Dim[/COLOR] eRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] gRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Arng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] p           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] eRng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] gRng = Range(Range("G2"), Range("G" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Arng = Union(eRng, gRng)
Range("I1:O1").Value = Range("A1:G1").Value
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Arng
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn '[COLOR="Green"][B]Array(Dn, c, p)[/B][/COLOR]
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ReDim ray(1 To (eRng.Count + gRng.Count), 1 To 7)
[COLOR="Navy"]Dim[/COLOR] k       [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] A       [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] sA      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    p = temp
        c = temp
For Each A In .Item(k) '[COLOR="Green"][B].Areas[/B][/COLOR]
    [COLOR="Navy"]If[/COLOR] A.Column = 5 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        [COLOR="Navy"]For[/COLOR] n = 1 To 5
            ray(c, n) = A.Offset(, -5 + n)
        [COLOR="Navy"]Next[/COLOR] n
     [COLOR="Navy"]ElseIf[/COLOR] A.Column = 7 [COLOR="Navy"]Then[/COLOR]
        p = p + 1
        ray(p, 6) = A.Offset(, -1): ray(p, 7) = A
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] A
temp = Application.Max(c, p)
   [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] With
Range("I2").Resize(c, 7) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Ordering two sets of columns by duplicate values

noclue88

New Member

Excel Facts

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Ordering two sets of columns by duplicate values

noclue88

New Member

Excel Facts

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock