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

 

[coreyjames]

Mick,

Thank you for the code but I am getting an error that says " Run-time error 13: Type mismatch."

Do you have any suggestions as to why this might be? Thanks!

 

[MickG]

The data below shows your basic layout with Procedures in columns "A & "E" and Volumes in "B & F"
I am wonering if your volumes ar not Numeric , this would give you the error.
NB:- I have not seen your posted File!!
If you still have the problem could you Show some basic data with Procedures and Volumes for the 4 columns.
NB:- Rows shown below as (1 to 5) are actually Rows (32 to 36)

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A)          [/B][/COLOR] [COLOR=royalblue][B]Col(B) [/B][/COLOR][COLOR=royalblue][B]Col(C) [/B][/COLOR][COLOR=royalblue][B]Col(D) [/B][/COLOR][COLOR=royalblue][B]Col(E)          [/B][/COLOR] [COLOR=royalblue][B]Col(F) [/B][/COLOR]
1.      cardiovascular   5                       other            6      
2.      orthopedics      12                      ear nose throat  5      
3.      ear nose throat  3                       cardiovascular   4      
4.      cardiology       2                       Vascular         3      
5.      other            6

Returned Data after running code:-

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(G)          [/B][/COLOR] [COLOR=royalblue][B]Col(H) [/B][/COLOR][COLOR=royalblue][B]Col(I)          [/B][/COLOR] [COLOR=royalblue][B]Col(J) [/B][/COLOR]
1.      cardiovascular   5       cardiovascular   4      
2.      other            6       other            6      
3.      ear nose throat  3       ear nose throat  5      
4.      orthopedics      12                              
5.      cardiology       2                               
6.                               Vascular         3

Regards Mick

 

[coreyjames]

I posted a link to my file a few posts up. Are you having problems viewing it? I will give your suggestions a try

 

[coreyjames]

Mick,

I have gotten the code to work but I am trying to clean it up a little to suit my needs. Is there a way where I can have the results just replace my original text? Meaning I only want the macro to look at cells A/B 32:45 and E/F 32:45 and then place the results in the same cells. This way everything is lined up the way I want it for the graphs. Thanks!!

 

[MickG]

Try this:-
NB:- This code will delete information in Range("A32: F45") then replace with Results.
If you have anything in "C32:to D45" that you want I will alter the code. !!!

[COLOR="Navy"]Sub[/COLOR] MG26Apr33
'[COLOR="Green"][B]Align Data in columns "A/B & E/F" rows 32 to 45[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oval [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[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] Rng = Range("A32:A45")
ReDim Ray(1 To Rng.Count * 2, 1 To 6)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [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, 5) = K: Ray(rw, 6) = .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, 5) = "": Ray(rw, 6) = ""
        [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, 5) = K: Ray(rw, 6) = .Item(K)(4)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] K
Rng.Resize(, 6).ClearContents
Range("A32").Resize(rw, 6) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[coreyjames]

Mick,

Thank you very much for your help. The last bit of code worked perfectly. I am creating a template for my team to use and present data with. This simple macro will save us a lot of time. Thanks again!