MS Excel, Concatenating query

davetrousers

Evening all, I have a query regarding concatenating data in Excel.

I have in column A, a list of names and in column B next to each name a (job code). However if a person (eg John Smith) has more than one job code then the name is repeated on the next line and the subsequent job codes next to that. eg:

A...............B
John Smith ABC
John Smith DEF
John Smith GHI
Joe Bloggs ABC
Joe Bloggs GHI

So I would like to concatenate the job codes so that it says (something like):

A.....................B
Joh Smith ABC, DEF, GHI
Joe Bloggs ABC, GHI

Can anyone give any advice on this please? Note that some people may only have 1 or 2 job codes while some may have 6.

Thanks

annasoper

http://spreadsheets.about.com/od/excelfunctions/qt/07-concatenate.htm

Try that

Richie(UK)

Hi Dave, A little macro should be able to do that for you. If nobody else helps in the meantime I'll knock one up in the next day or two.

davetrousers

annasoper wrote: »

http://spreadsheets.about.com/od/excelfunctions/qt/07-concatenate.htm

Try that

Thanks but isn't that kind of manually concatenating, thing is the list has about 2000 lines so I would like to be able to do it a bit quicker.

Richie(UK) wrote: »

Hi Dave, A little macro should be able to do that for you. If nobody else helps in the meantime I'll knock one up in the next day or two.

Thanks that would be a big help.

I was sort of thinking along the lines of an if,then logic so that if the name equalled the name above then concatenate, but couldn't think of a way of doing it when getting down to the nitty gritty.

annasoper

Here you go:
Just adapt it for your cells
http://support.microsoft.com/kb/213477

annasoper

Opps, just realised you have different rows with the same name too. Hmmm... ok, the above wont work for that, it will partially but I guess it is not the full solution

annasoper

Why don't you post your query on a site like stackoverflow. I have had excel problems before and I got my questions answered there. You're bound to get a quicker reply too. I'm no programmer, so I'm kind of struggling making sense of the code Good luck

BRSurvivor

To achieve the result you want, just use concatenate with if statements. So, if your first name is in Row 1, in cell C1, put the following:

=CONCATENATE(B29&", ",IF(A30=A29,B30&", ",""),IF(A31=A29,B31&", ",""),IF(A32=A29,B32&", ",""),IF(A33=A29,B33&", ",""),IF(A34=A29,B34&", ",""))

That's a concatenate with five IF statements that evaluate the 5 lines below the current one to see if the name matches the name on this line - if it does it adds the job code on followed by a comma and a space &", ". If there isn't a match, it just leaves nothing there - "".

No drag the formula in C1 all the way down your list, and you are almost at where you need to be.

Now I would make a copy of the worksheet (or save the file under a different filename).

Click on columns A, B and C and then select Copy, and then use Paste Special to paste all 3 columns back as Values. You can then delete column B.

Now select columns A and B, and use the remove duplicates facility (under Data in the Ribbon in 2007/10) to remove duplicates in column A (uncheck column .

Hope that helps.

davetrousers

Thanks for your help I'll let you know how I get on.

davetrousers

BRSurvivor wrote: »

To achieve the result you want, just use concatenate with if statements. So, if your first name is in Row 1, in cell C1, put the following:

=CONCATENATE(B29&", ",IF(A30=A29,B30&", ",""),IF(A31=A29,B31&", ",""),IF(A32=A29,B32&", ",""),IF(A33=A29,B33&", ",""),IF(A34=A29,B34&", ",""))

Thanks, I did a quick subtotal to discover that there were some people with 10 job codes. So what I did was copy the formula into Textpad, then copy each individual If statement (iyswim) and pasted it in several more times then amended the numbers accordingly and pasted that back into Excel.

Top stuff, thanks very much.

Richie(UK)

Hi Dave,

Looks as if you have it sussed. I've just thrown this together so may as well post it up now

Sub davetrousers()
    
    Dim lCalc As Long, rngData As Range, rngCell As Range
    Dim colNames As New Collection, lCount As Long, strJob As String
    
    With Application
        lCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    'prevent screen refresh and calculation while working
     
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngData = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        'the data range with the employee names (Assumes A1 is header)
        
        On Error Resume Next
        For Each rngCell In rngData
            If Not rngCell.Address = "$A$1" Then
                colNames.Add rngCell.Value, CStr(rngCell.Value)
            End If
        Next rngCell
        On Error GoTo 0
        'Use a Collection to identify all unique names
        
        For lCount = 1 To colNames.Count
            rngData.AutoFilter Field:=1, Criteria1:=colNames(lCount)
            For Each rngCell In rngData.SpecialCells(xlCellTypeVisible)
                If Not rngCell.Address = "$A$1" Then
                    strJob = strJob & " " & rngCell.Offset(0, 1).Value
                End If
            Next rngCell
            .Range("C" & lCount + 1).Value = colNames(lCount)
            .Range("D" & lCount + 1).Value = strJob
            strJob = ""
        Next lCount
        rngData.AutoFilter
        'Use unique names to apply series of autofilters to ensure all name rows captured
    
    End With
    
    With Application
        If Not lCalc = 0 Then .Calculation = lCalc
        .ScreenUpdating = True
    End With
    'put things back how they were
    
End Sub

It assumes the data is on Sheet1, row 1 is for headers, names in column A, jobs in column B, new details in columns C and D.