Reverse Comma Delimited

D

dianamruelas

New Member
Joined
Sep 28, 2015
Messages
44
Office Version
  1. 365
Platform
  1. Windows
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."

[TABLE="width: 550"]
<tbody>[TR]
[TD]Account[/TD]
[TD]CPT[/TD]
[TD]EOB Code Number[/TD]
[TD]Account EOB Codes[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]99214[/TD]
[TD]2[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]27392[/TD]
[TD]3[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86872[/TD]
[TD]4,5[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86831[/TD]
[TD]6,7[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 329"]
<tbody>[TR]
[TD]Account[/TD]
[TD]CPTs[/TD]
[TD]EOB Codes[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]99214[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]27392[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86872[/TD]
[TD]288,N630[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86831[/TD]
[TD]288,N630[/TD]
[/TR]
</tbody>[/TABLE]

OR

[TABLE="width: 329"]
<tbody>[TR]
[TD]Account[/TD]
[TD]CPT[/TD]
[TD]EOB Codes[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]99214[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]27392[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86872[/TD]
[TD]288[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86872[/TD]
[TD]N630[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86831[/TD]
[TD]288[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]86831[/TD]
[TD]N630[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
How about
Code: 
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
 
Upvote 0
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:

[TABLE="width: 457"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Invoice[/TD]
[TD]CPT[/TD]
[TD]EOB Code Number[/TD]
[TD]EOB Codes[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]99214[/TD]
[TD]1,2[/TD]
[TD]1-45,2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]27392[/TD]
[TD]3[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]86872[/TD]
[TD]4,5[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]86831[/TD]
[TD]6,7[/TD]
[TD]2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
How about
Code: 
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
 
Upvote 0
dianamruelas said:
And there can actually be up to several dozen codes. Can that be accounted for?
Click to expand...

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


Rich (BB code): 
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)
Rich (BB code):
Rich (BB code): 
End Function


 
Upvote 0
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

[TABLE="width: 1531"]
<tbody>[TR]
[TD]Account #[/TD]
[TD]Patient[/TD]
[TD]Subscriber #[/TD]
[TD]DOB[/TD]
[TD]Billing Provider[/TD]
[TD]Billing Provider Type[/TD]
[TD]Billing Prov NPI[/TD]
[TD]Service Date[/TD]
[TD]CPT[/TD]
[TD]Mod[/TD]
[TD]EOB Allowed Amount[/TD]
[TD]Expected Allowed Amount[/TD]
[TD]Allowed Amount Discrepancy[/TD]
[TD]% of Expected Allowed[/TD]
[TD]Discrepancy %[/TD]
[TD]Patient Liability[/TD]
[TD]Paid Amount[/TD]
[TD]EOB Code Number[/TD]
[TD]EOB Codes[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]Mr. Smith[/TD]
[TD]W099233166[/TD]
[TD="align: right"] 10/25/58[/TD]
[TD]Dr. Smith[/TD]
[TD]Physician[/TD]
[TD]1396705919[/TD]
[TD="align: right"] 10/01/18[/TD]
[TD]99214[/TD]
[TD][/TD]
[TD="align: right"]113.41[/TD]
[TD="align: right"]122.48[/TD]
[TD="align: right"]9.07[/TD]
[TD="align: right"]92.59%[/TD]
[TD="align: right"]7.41%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]113.41[/TD]
[TD]1,2[/TD]
[TD]1-45,2-3,3-59,4-288,5-N630,6-288,7-N630[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]Mr. Smith[/TD]
[TD]W099233166[/TD]
[TD="align: right"] 10/25/58[/TD]
[TD]Dr. Smith[/TD]
[TD]Physician[/TD]
[TD]1396705919[/TD]
[TD="align: right"] 10/01/18[/TD]
[TD]27392[/TD]
[TD][/TD]
[TD="align: right"]20.03[/TD]
[TD="align: right"]21.63[/TD]
[TD="align: right"]1.60[/TD]
[TD="align: right"]92.60%[/TD]
[TD="align: right"]7.40%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]20.03[/TD]
[TD]3[/TD]
[TD]1-45,2-3,3-59,4-288,5-N630,6-288,7-N631[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]Mr. Smith[/TD]
[TD]W221226798[/TD]
[TD="align: right"] 03/26/03[/TD]
[TD]Dr. Smith[/TD]
[TD]Physician[/TD]
[TD]1427035484[/TD]
[TD="align: right"] 10/01/18[/TD]
[TD]86872[/TD]
[TD][/TD]
[TD="align: right"]18.77[/TD]
[TD="align: right"]20.27[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]92.60%[/TD]
[TD="align: right"]7.40%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]18.77[/TD]
[TD]4,5[/TD]
[TD]1-45,2-3,3-59,4-288,5-N630,6-288,7-N632[/TD]
[/TR]
[TR]
[TD]P54321[/TD]
[TD]Mr. Smith[/TD]
[TD]W211213062[/TD]
[TD="align: right"] 03/13/63[/TD]
[TD]Dr. Smith[/TD]
[TD]Physician[/TD]
[TD]1588806749[/TD]
[TD="align: right"] 10/01/18[/TD]
[TD]86831[/TD]
[TD][/TD]
[TD="align: right"]130.76[/TD]
[TD="align: right"]150.14[/TD]
[TD="align: right"]19.38[/TD]
[TD="align: right"]87.09%[/TD]
[TD="align: right"]12.91%[/TD]
[TD="align: right"]130.76[/TD]
[TD="align: right"]0.00[/TD]
[TD]6,7[/TD]
[TD]1-45,2-3,3-59,4-288,5-N630,6-288,7-N633[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Just change the range from C to U on this line
Code: 
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
 
Upvote 0
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."
 
Upvote 0
Just replace both "C"s in that line
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Comparing two comma-delimited lists and getting unique values
Replies
7
Views
1K
Fluff
Fluff
SkywardPalm
  • Question Question
Best Match (Fuzzy Match Alternative) for Product Description and Pulling Respective Product ID Code
Replies
5
Views
682
Akuini
Akuini
L
  • Question Question
VBA Issue / Exporting Excel File to CSV Causing Double Quote Issues
Replies
0
Views
3K
Laurence Harper
L
L
  • Question Question
VBA in Excel 2010 for Pivot Tables with comma delimited items in column
Replies
0
Views
612
Legacy 366536
L
J
  • Question Question
How to Search Comma-Separated Content in a Cell against a List
Replies
0
Views
1K
JMKnapp
J

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top