Matching two columns of data

[coreyjames]

If I have a list of surgical specialties in column A and a list in column B is there a way I could match them up so that if cardiovascular is in both columns it will put them in the same row and then add the specialties that do not match at the end? Please see below.

Column A Column B

cardiovascular other
orthopedics ear nose throat
ear nose throat cardiovascular
cardiology Vascular
other

 

[shg]

There's a workbook with code that does that at http://www.box.net/shared/elrnbidnr7.

Read the comments in the top section of code and post back if you have problems.

 

[coreyjames]

Thank you with your reply. I dont think I explained very well what I am trying to accomplish. Is there anyway you could email me so I could send you an example of what I am looking to do? Thanks again, my email is corey.trautman@gmail.com. Thanks

 

[shg]

The code rearranges your data like this:

      ------A------- ---B---- --C--- ------D-------
  1       col 1       col 2   col 3      col 4     
  2   cardiology                                   
  3   cardiovascular                 cardiovascular
  4   ear            ear                           
  5                  nose     nose                 
  6   orthopedics                                  
  7   other          other                         
  8                           throat throat        
  9                  Vascular

If that's not what you want, could you describe it?

 

[coreyjames]

Well I am comparing data from 2010 and 2011. I have a pie cart showing procedures from 2010 and a pie chart beside it showing procedures from 2011. If a procedure shows up in both charts I want the pie slice to be the same color in both graphs. I figured the only way to accomplish this would be to come up with a way to have the procedures that match be listed in the same order so that when the pie graph is created it picks the same colors. I may be going about this the wrong way. If I have a colonoscopy in my 2010 chart and its pie slice is blue then i want to make sure colonoscopy is blue in my 2011 chart. This way when I present my data and someone sees blue they know exactly what it is. The problem is there are over 2,000 different procedures so I cant assign a color to a name as I have seen in some macros on this site. I may have 6 procedures in 2011 and 4 in 2010 but I want to make sure the ones that match have the same color. The ones that do not match I want to make sure have completely different colors. Does this make sense?

 

[MickG]

Try this:-
Data starts "A2",Results start "C1".

[COLOR="Navy"]Sub[/COLOR] MG22Apr33
[COLOR="Navy"]Dim[/COLOR] RngA [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oval [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngB [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] CA [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] CB [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] RngA = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
  [COLOR="Navy"]Set[/COLOR] RngB = Range(Range("B2"), Range("B" & rows.Count).End(xlUp))
    oMax = Application.Max(RngA.Count, RngB.Count)
        [COLOR="Navy"]Set[/COLOR] RngA = Range("A2").Resize(oMax)
            ReDim Ray(1 To RngA.Count * 2, 1 To 2)
                ReDim nRay(1 To RngA.Count * 10)
                    ReDim RwRay(1 To RngA.Count * 10, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngA
    For col = 1 To 2 '[COLOR="Green"][B] Loop across RngA[/B][/COLOR]
        oval = IIf(col = 1, Dn, Dn.Offset(, 1))
            [COLOR="Navy"]If[/COLOR] Not .Exists(oval) [COLOR="Navy"]Then[/COLOR]
                n = n + 1
                ReDim RayAc(1 To RngA.Count, 1 To 2)
                nRay(n) = RayAc
                nRay(n)(1, col) = oval
                [COLOR="Navy"]If[/COLOR] col = 1 [COLOR="Navy"]Then[/COLOR] CA = 1: CB = 0
                [COLOR="Navy"]If[/COLOR] col = 2 [COLOR="Navy"]Then[/COLOR] CB = 1: CA = 0
                    .Add oval, Array(nRay(n), CA, CB, 1)
            [COLOR="Navy"]Else[/COLOR]
                Q = .Item(oval)
                    [COLOR="Navy"]If[/COLOR] col = 1 [COLOR="Navy"]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        Q(0)(Q(1), col) = oval
                    [COLOR="Navy"]ElseIf[/COLOR] col = 2 [COLOR="Navy"]Then[/COLOR]
                        Q(2) = Q(2) + 1
                        Q(0)(Q(2), col) = oval
                    [COLOR="Navy"]End[/COLOR] If
                Q(3) = Application.Max(Q(1), Q(2))
                .Item(oval) = Q
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] n = 1 To .Item(k)(3)
        rw = rw + 1
        RwRay(rw, 1) = .Item(k)(0)(n, 1)
        RwRay(rw, 2) = .Item(k)(0)(n, 2)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] k
Range("C1").Resize(rw, 2) = RwRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[coreyjames]

Mick,

That works but its a little more simplistic than what I need it for. If there is away I could send you my excel file I think it would be easier. I have my data setup the following way.

A32:A45 = My procedures such as Cardiovascular, Incision and Drainage, etc.
B32:B45 = The volume for the corresponding procedures in column A

Then in E32:E45 I have the procedures from 2010 and in F32:45 I have the volume for those procedures.

I am looking for a way to automatically sort the procedures from 2010 in the F column to match the procedures from 2011 in the A column so that the pie slices for each graph are the same color when I graph them. I also need the volumes to stay with the procedures as they move. I hope I am explaining this well. Thanks !

 

[shg]

Can you post the file on a sharing site like box.net and post a link?

 

[coreyjames]

Please let me know if this link works. Thanks!

Finally got it right. Google was acting strange

https://docs.google.com/leaf?id=0B2wwdw1zA88gNGE1ZTU3Y2EtZTMyMi00ODBlLWE3ZWQtNzQ5YjM3ZTVlMmNl&hl=en&authkey=CMfZ688G

 

[MickG]

Try this :-
Based on Columns "A/B & E/F" starting row 32.
Results Start "G1" Alter Results range in last line to suit.

[COLOR="Navy"]Sub[/COLOR] MG23Apr37
'[COLOR="Green"][B]Align Data in columns "A/B & E/F"[/B][/COLOR]
'[COLOR="Green"][B]Results Columns G to J[/B][/COLOR]
'[COLOR="Green"][B]Count Method[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngA [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oval [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngE [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ColA [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ColE [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] aVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] eVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] RngA = Range(Range("A32"), Range("A" & rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] RngE = Range(Range("E32"), Range("E" & rows.Count).End(xlUp))
        oMax = Application.Max(RngA.Count, RngE.Count)
            [COLOR="Navy"]Set[/COLOR] RngA = Range("A32").Resize(oMax)
                ReDim Ray(1 To oMax * 2, 1 To 4)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngA
            [COLOR="Navy"]For[/COLOR] col = 1 To 4 [COLOR="Navy"]Step[/COLOR] 3
                oval = IIf(col = 1, Dn, Dn.Offset(, 4))
               [COLOR="Navy"]If[/COLOR] oval <> "" [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Not .Exists(oval) [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] col = 1 [COLOR="Navy"]Then[/COLOR]
                        ColA = 1: ColE = 0
                        aVal = Dn.Offset(, 1)
                        eVal = 0
                    [COLOR="Navy"]Else[/COLOR]
                        ColE = 1: ColA = 0
                        eVal = Dn.Offset(, 5)
                        aVal = 0
                     [COLOR="Navy"]End[/COLOR] If
                    .Add oval, Array(ColA, ColE, 1, aVal, eVal)
                [COLOR="Navy"]Else[/COLOR]
                    Q = .Item(oval)
                    [COLOR="Navy"]If[/COLOR] col = 1 [COLOR="Navy"]Then[/COLOR]
                        Q(0) = Q(0) + 1
                        Q(3) = Dn.Offset(, 1)
                    [COLOR="Navy"]ElseIf[/COLOR] col = 4 [COLOR="Navy"]Then[/COLOR]
                        Q(1) = Q(1) + 1
                       Q(4) = Dn.Offset(, 5)
                    [COLOR="Navy"]End[/COLOR] If
                    Q(2) = Application.sum(Q(0), Q(1))
                    .Item(oval) = Q
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] col
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(k)(2) = 2 [COLOR="Navy"]Then[/COLOR]
            rw = rw + 1
            Ray(rw, 1) = k: Ray(rw, 2) = .Item(k)(3)
            Ray(rw, 3) = k: Ray(rw, 4) = .Item(k)(4)
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] k
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
        [COLOR="Navy"]If[/COLOR] .Item(k)(0) = 1 And .Item(k)(1) = 0 [COLOR="Navy"]Then[/COLOR]
            rw = rw + 1
            Ray(rw, 1) = k: Ray(rw, 2) = .Item(k)(3)
             Ray(rw, 3) = "": Ray(rw, 4) = ""
        [COLOR="Navy"]ElseIf[/COLOR] .Item(k)(0) = 0 And .Item(k)(1) = 1 [COLOR="Navy"]Then[/COLOR]
            rw = rw + 1
            Ray(rw, 1) = "": Ray(rw, 2) = ""
            Ray(rw, 3) = k: Ray(rw, 4) = .Item(k)(4)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] k
'[COLOR="Green"][B]Change Results range to suit[/B][/COLOR]
Range("G1").Resize(rw, 4) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick