Excel 2016 - combine data from multiple cells into 1 cell 2

[ITALIAORIANA]

Hi, I received an excel report with codes and descriptions. The codes and descriptions are in multiple rows. I need to combine all the data descriptions for each code into 1 cell for the corresponding code. The number of rows per code is variable. There are no duplicate codes. I tried to use a simple concatenate to combine the cells, but my knowledge of a simple concatenate isn't working since the number of lines depends on how long the description is. Any help would be greatly appreciated. This report currently has over 600 rows.

CURRENT

Code	Description
01	Diagnostic reason not given
01	claim denied
	
02	Services were rendered by an 
02	unauthorized provider therefore
02	services are not covered. This is 
02	a member liability

RESULT I AM LOOKING FOR

Code	Description
01	Diagnostic reason not given claim denied
	
02	Services were rendered by an unauthorized provider therefore services are not covered. This is a member liability

Thanks
Deana

 

[SkipVought]

Hi,

I hate getting reports to process in Excel. Sometimes "the boss" wants all the data in one row in one column or one row in multiple columns (like a column for 01 and another column for 02).

However in this case, he wants everything in one column with one row for 01 and one row for 02.

I'd like to see a COPY n PASTE of two sets of data. I'm assuming that your report source has one empty row between the 01s and 02s.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ITALIAORIANA]

Hi,
The report didn't come with the empty rows between each codes. I added the separate rows between codes so I could see the descriptions for each code easier. I can't copy and paste because I can't get on tek tips from my work computer and I can't send anything to my personal email. I've added additional codes to show what the current report looks like below.

The request is to get each code in 1 column and the description in another column. So code would be in column A and the description would be in column B. I started using concatenate but it is taking forever and I have to keep changing the formula to pick up additional rows or drop them if the description is shorter.

Code	Description
06	This procedure is denied. The members
06	age is invalid for this procedure. This is not the members liability
	
21	This claim is being denied due to the 
21	lack of requested
21	information from the 
21	provider of service
21	For reconsideration, the requested documentation
21	should be submitted to the complaint and
21	grievance department
	
E2	An assessment fee and appropriate ancillaries have
E2	been paid.
E2	The balance is not a member
E2	liability
E2	To appeal the denial of this claim, please submit
E2	the ER medical record, a copy of this remit, and
E2	a cover letter outlining your request to the
E2	appeals unit (redacted address)
E2	within 90 working days of this notice

 

[SkipVought]

In columns C & D...
[tt]
C2: =IF(A1=A2,"",A2)
D2: =IF(A3=A2,B2&" "&B3&" "&D3,"")
[/tt]

Copy/Paste both formulas down thru all rows of data

Then put a Filter in row 1 and select NON BLANKS in column C

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ITALIAORIANA]

Thank you so much, this worked perfectly. You just saved me a gazillion hours of tedious work!

Deana

 

[SkipVought]

Just know that this kind of solution is the basis for a number of similar problems. Just takes thinking carefully about what you got and what you need to end up with.

BTW, you don't need an empty row between groups.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ITALIAORIANA]

Hi, Yep, I just got this back because it didn't work exactly the way expected. It is duplicating verbiage if the number of rows being combined is more than 3.

 

[Andrzejek]

Would VBA solution work for you?
If so, eliminate empty rows and run this code:

Option Explicit

Sub ITALIAORIANA()
Dim r As Integer
Dim n As Integer
Dim strCode As String
Dim strDesc As String

Cells(1, 3).Value = Cells(1, 1).Value
Cells(1, 4).Value = Cells(1, 2).Value
r = 2
n = r
Do While Cells(r, 1).Value <> ""
    If strCode = "" Then strCode = Cells(r, 1).Value
    If strDesc = "" Then
        strDesc = Cells(r, 2).Value
    Else
        strDesc = strDesc & " " & Cells(r, 2).Value
    End If
    
    If Cells(r + 1, 1).Value <> strCode Then
        Cells(n, 3).Value = strCode
        Cells(n, 4).Value = strDesc
        strCode = ""
        strDesc = ""
        n = n + 1
    End If
    r = r + 1
Loop

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

In reference to Skip's expectation in oter thread, you can find a workbook data processed by Power Query aka Get&Transform excel's functionality. All you have to do is to fill input table (first worksheet) and refresh data in the second sheet. There is also a link to wider description of the M code.

combo

 

[SkipVought]

Beware the limits, Skip!
[tt]
D2: =IF(A3=A2,B2&" "&D3,B2)
[/tt]
Sorry!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ITALIAORIANA]

I keep getting a compile error: User-defined type not defined.

 

[ITALIAORIANA]

Thanks Combo, I appreciate that link!

 

[Andrzejek]

I keep getting a compile error: User-defined type not defined.

On a piece of code I gave you...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[RobertPhillips]

I tend to agree with combo that Power Query is the way to go, it should be quite simple using grouping, but should you want a VBA solution, here is my offering

Public Sub DescriptionMerge()
Dim calcmode As Long
Dim lastrow As Long
Dim i As Long

    Application.ScreenUpdating = False
    calcmode = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    On Error GoTo dm_exit
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 2 Step -1
        
            If .Cells(i, "A").Value = vbNullString Then
            
                .Rows(i).Delete
            ElseIf .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
            
                .Cells(i - 1, "B").Value = .Cells(i - 1, "B").Value & " " & .Cells(i, "B").Value
                .Rows(i).Delete
            End If
        Next i
    End With

dm_exit:
    Application.ScreenUpdating = True
    Application.Calculation = calcmode
End Sub