match & merge or vlookup and concatenate « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

match & merge or vlookup and concatenate

(5 posts) (2 voices)

Started 2 years ago by coaddison
Latest reply from coaddison

Tags:

coaddison

Member
Joined: Jan '11
Posts: 3

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.

Posted 2 years ago #
Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,274

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)

Posted 2 years ago #
coaddison

Member
Joined: Jan '11
Posts: 3

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.

Posted 2 years ago #
Luke M

Excel Ninja
Joined: Nov '10
Posts: 4,274

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.

Posted 2 years ago #
coaddison

Member
Joined: Jan '11
Posts: 3

Excellent.... it works. Thank you

Posted 2 years ago #