Reverse Comma Delimited

[dianamruelas]

I receive a healthcare financial report. Each account has several services called "CPT." Each CPT has an assigned payment code called "EOB Code." The report I get lists all EOB codes for the account next to each CPT. I would like to break this down to only list the EOB code applicable to each CPT. This can be identified by the "EOB Code Number." The EOB Code Number corresponds with the actual EOB code. For the example below, CPT 99214 has EOB Code Number 2. "2-3" identifies the specific EOB Code assigned to the CPT, so the CPT Code I want is "3." For CPT 27392, I need "59."

Account	CPT	EOB Code Number	Account EOB Codes
54321	99214	2	2-3,3-59,4-288,5-N630,6-288,7-N630
54321	27392	3	2-3,3-59,4-288,5-N630,6-288,7-N630
54321	86872	4,5	2-3,3-59,4-288,5-N630,6-288,7-N630
54321	86831	6,7	2-3,3-59,4-288,5-N630,6-288,7-N630

<tbody>
</tbody>

Here is the needed format. It can be comma delimited or listed out separately, but I prefer comma delimited.

Account	CPTs	EOB Codes
54321	99214	3
54321	27392	59
54321	86872	288,N630
54321	86831	288,N630

<tbody>
</tbody>

OR

Account	CPT	EOB Codes
54321	99214	3
54321	27392	59
54321	86872	288
54321	86872	N630
54321	86831	288
54321	86831	N630

<tbody>
</tbody>

 

[Fluff]

How about

Sub SortData()
   Dim Cl As Range
   Dim Sp1 As Variant, Sp2 As Variant, x As Variant
   Dim i As Long
   
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      Sp1 = Split(Cl.Value, ",")
      Sp2 = Split(Cl.Offset(, 1).Value, ",")
      For i = 0 To UBound(Sp1)
         If x = "" Then x = Split(Sp2(Sp1(i) - 2), "-")(1) Else x = x & "," & Split(Sp2(Sp1(i) - 2), "-")(1)
      Next i
      Cl.Value = x
      Cl.Offset(, 1).ClearContents
      x = ""
   Next Cl
End Sub

 

[dianamruelas]

Thank you!!! This is great. But the data will vary. I added to the first line. Please see the error I get when running it on the below:

Invoice	CPT	EOB Code Number	EOB Codes
P54321	99214	1,2	1-45,2-3,3-59,4-288,5-N630,6-288,7-N630
P54321	27392	3	2-3,3-59,4-288,5-N630,6-288,7-N630
P54321	86872	4,5	2-3,3-59,4-288,5-N630,6-288,7-N630
P54321	86831	6,7	2-3,3-59,4-288,5-N630,6-288,7-N630

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[dianamruelas]

And there can actually be up to several dozen codes. Can that be accounted for?

 

[Fluff]

How about

Sub SortData()
   Dim Cl As Range
   Dim Sp1 As Variant, Sp2 As Variant, x As Variant, a As Variant
   Dim i As Long
   
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      Sp1 = Split(Cl.Value, ",")
      Sp2 = Split(Cl.Offset(, 1).Value, ",")
      For i = 0 To UBound(Sp1)
         a = Application.Match(Sp1(i) & "*", Sp2, 0)
         If Not IsError(a) Then
            If x = "" Then x = Split(Sp2(a - 1), "-")(1) Else x = x & "," & Split(Sp2(a - 1), "-")(1)
         End If
      Next i
      Cl.Value = x
      Cl.Offset(, 1).ClearContents
      x = ""
   Next Cl
End Sub

 

[mopp1]

And there can actually be up to several dozen codes. Can that be accounted for?

A UDF solution - First parameter, Codes, is the cellcontaining EOB Code Number. Secondparameter , CodeList, is the cell containing Account EOB Codes.

Function EOBCode(Codes, CodeList)
    
    C = Split(Codes, ",")
    CL = Split(CodeList, ",")
    For i = 0 To UBound(C)
        found = False
        For j = 0 To UBound(CL)
            CLparts = Split(CL(j), "-")
            If CLparts(0) = C(i) Then
                EOBCode = EOBCode & CLparts(1) & ","
                found = True
                Exit For
            End If
        Next
        If Not found Then EOBCode = EOBCode & "#NA" & ","
    Next
    EOBCode = Left(EOBCode, Len(EOBCode) - 1)

End Function

 

[dianamruelas]

Last thing. I'm pretty amateur. The Sub works perfectly. But I cut the columns out of my actual report as not to be overwhelming in my original post. Now I'm afraid I'll mess it up by trying to adapt it to the report, which has 21 columns. Here's a mock of the report, could you please adjust to the correct columns? I've highlighted the original 4 columns with red font. Column C from above is now column U

Account #	Patient	Subscriber #	DOB	Billing Provider	Billing Provider Type	Billing Prov NPI	Service Date	CPT	Mod	EOB Allowed Amount	Expected Allowed Amount	Allowed Amount Discrepancy	% of Expected Allowed	Discrepancy %	Patient Liability	Paid Amount	EOB Code Number	EOB Codes
P54321	Mr. Smith	W099233166	10/25/58	Dr. Smith	Physician	1396705919	10/01/18	99214		113.41	122.48	9.07	92.59%	7.41%	0.00	113.41	1,2	1-45,2-3,3-59,4-288,5-N630,6-288,7-N630
P54321	Mr. Smith	W099233166	10/25/58	Dr. Smith	Physician	1396705919	10/01/18	27392		20.03	21.63	1.60	92.60%	7.40%	0.00	20.03	3	1-45,2-3,3-59,4-288,5-N630,6-288,7-N631
P54321	Mr. Smith	W221226798	03/26/03	Dr. Smith	Physician	1427035484	10/01/18	86872		18.77	20.27	1.50	92.60%	7.40%	0.00	18.77	4,5	1-45,2-3,3-59,4-288,5-N630,6-288,7-N632
P54321	Mr. Smith	W211213062	03/13/63	Dr. Smith	Physician	1588806749	10/01/18	86831		130.76	150.14	19.38	87.09%	12.91%	130.76	0.00	6,7	1-45,2-3,3-59,4-288,5-N630,6-288,7-N633

<tbody>
</tbody>

 

[Fluff]

Just change the range from C to U on this line

   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))

 

[dianamruelas]

I'm getting an error. Do I replace all "C" with "U"? Or just in that one line? And correction, it is actually column "S," not "U."

 

[Fluff]

Just replace both "C"s in that line