URGENT find common string values in 2 colums

**M.Karim** · 12-12-2012, 09:26 AM

Dear All,

Please help in the following...

i have 2 columns each on have string values separated by commas
example:

columns 1

O9,L2,A2,7E,IT2
A2,C6,HY,IK,O9,CHE,MCP
IK,3D,HY,A8,P0,IY
7E,HY,O9,IT2,L2

columns 2
P0,L2,IT2,7E,3D
A2,HY,L2,IK,O9,CHE,MCP
IK,IT2,HY,A8,7E,O9
O9,C6,IY,A2,HY

I need to create a button to run a macro to create the 3rd column of Common string. The cells under the Common String column must contain the common codes from the first 2 columns and must be separated by comma in alphabetical order.

Please anyone can you help.

Regards

**JBeaucaire** · 12-12-2012, 11:58 AM

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your sample data AND your desired results.

**M.Karim** · 12-13-2012, 04:11 AM

sample.xlsx

please find attached sample,

**JBeaucaire** · 12-13-2012, 11:21 AM

Here are two functions to accomplish this. One I wrote called CompareLists() and it is a UDF. Here's the code, it goes in a standard code module, not a sheet module:

Please Login or Register  to view this content.

Once installed you use it in a cell like any other formula, in C2:

=COMPARELISTS(A2, B2, ",", TRUE)

RANGE1 - the first delimited list in a cell. Only the first cell will be used if this is a multicell range
RANGE2 - the second delimited list in another cell. Only the first cell will be used if this is a multicell range
DELIMITER - an optional string that indicates the delimiter in each comparison cell, the default is comma
TRUE/FALSE - optional, if TRUE is a list of common items, if FALSE or omitted is a list of non-common items

The second function is a BubbleSort function using a popular technique that is used to "sort an array in vba", it is putting the results into alphabetical order as you requested.