match merge or vlookup and concatenate

[coaddison]

I have been tearing out what is left of my hair trying to sort data in a spreadsheet. The long and short of it is I have a sheet of data related to member information. Column's A-Z. Column A has case_num and can be listed several times within rows of data.


Many of the lines can have dup information information but I am looking to match column A and if there is any data in column L, concatenate or merge it into column L.


I am not sure what formula or syntax to use. I can provide sample data. Thank you for your help.

 

[Luke M]

Not sure if there's a native way to build this, but using a user defined function (UDF) it's not that hard. First, copy this to a module in the VBE:

Function ConcIf(Check_Range As Range, Criteria As String, _

ConcRange As Range, Delim As String)


If Check_Range.Count <> ConcRange.Count Then

ConcIf = "#Unequal_Range"

Exit Function

End If


xCount = Check_Range.Count


For i = 1 To xCount

If Check_Range.Cells(i, 1) = Criteria Then

ConcIf = ConcIf & ConcRange.Cells(i, 1) & Delim

End If

Next

ConcIf = Left(ConcIf, Len(ConcIf) - Len(Delim))

End Function

Next, in your workbook, the formula would be something like:

=ConcIf(A$2:A$100,A2,L$2:L$100,", ")


First arguement is the range to compare to criteria, 2nd arguement is the criteria (your case_num), 3rd argument is the range to concatenate, last arguement is the delimiter to use (put "" if you simply want to run everything together)

 

[coaddison]

Oh my! I was able to press alt+f8 and enter the function. I pasted the formula in a blank cell (AA) and it did exactly what was expected. I did receive a few #value! errors. Luke - Thank you so very much. I am in awe.


With this, is there a method to delete the duplicate lines after this runs? I am fine doing it with the manual process.

 

[Luke M]

Awesome, glad it worked.


What I usually do to remove duplicate values (make sure you've converted those new formulas to static values before doing this so they don't get messed up) is to create a helper column with this type of formula copied down as needed:


=COUNTIF(A$2:A2,A2)


I can then filter on that column for anything not equal to 1 and delete those rows.

 

[coaddison]

Excellent.... it works. Thank you