Need help reversing table

[Legacy 327817]

Hello,

This one might be a little complicated but I was curious if anyone knew an easy way to accomplish this.

I have a table that looks like the following:

Column A_____________Column B

1) Ambulatory_________99201, 99202, 99203, 99204, 99205, 99211, 99212, 99213
2) Testing_____________87070, 87071, 87081, 87430, 87650, 87651, 87652, 87880
3) Cancer Screening____88141, 88142, 88143, 88147, 88148, 88150, 88152, 88153
4) Prenatal Care_______99201, 99202, 99203, 99204, 99205, 99211, 99212, 99213

Column A is descriptions, and Column B is 5 digit codes separated by a comma. What I want to do is to create a table by Code. For example:

Column A_____________Column B
99201________________Ambulatory,Prenatal Care
99202________________Ambulatory,Prenatal Care
22903________________Ambulatory,Prenatal Care
88141________________Cancer Screening
88412________________Cancer Screening
etc...

Another issue is that one code may appear multiple times, as you can see above. I would like for the table to look at all the codes and return the descriptions that corresponds with it. (Either have the descriptions in the same column like above or separate, doesn't matter)

Is this too complicated? I know there are probably multiple steps in this issue but I am not even sure how to start. Any help would be appreciated.

Thanks!!

 

[Rijnsent]

Hi Mhalle,

I would approach it as follows:
1) I'd need a list of unique 5 number codes
2) then I could find them with the options in column A

If it's something you need to do numerous times, a macro would be your friend, otherwise you could take the following approach:
a) Make a copy of your original data to a new sheet
b) Select column B and go for "Text To Columns" with a comma as the delimiter
c) Now you should have a block with 5 digit codes. If your data is no bigger then in your example, you could manually cut-paste them all in one column (otherwise a macro might be practical). The key is to get all your numbers (not unique yet) in one column. In your example I end up with one column with 32 values. Select all 32 and go for "Remove Duplicates", 8 duplicates should be removed.
d) Next, you could use a formula like =IF(ISERROR(FIND(YOUR_5_DIGIT_NUMBER;LIST_OF_NUMBERS));"";"Ambulatory, ") and do that for the following 3 items to "glue your string together.

Hope that gets you moving,

Koen

 

[MickG]

Try this:-
NB:- Your data assumed to start "A2" , Results sheet2 , Starting "A2".

[COLOR="Navy"]Sub[/COLOR] MG01Jun12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 [COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
 [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, ", ")
        [COLOR="Navy"]For[/COLOR] Ac = 0 To UBound(Sp)
            [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Sp(Ac)) [COLOR="Navy"]Then[/COLOR]
                Dic.Add Sp(Ac), Dn.Offset(, -1).Value
            [COLOR="Navy"]Else[/COLOR]
                Dic.Item(Sp(Ac)) = Dic.Item(Sp(Ac)) & ", " & Dn.Offset(, -1).Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A2").Resize(Dic.Count, 2)
    .Value = Application.Transpose(Array(Dic.Keys, Dic.items))
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick