Grouping like items together

I am working on a set of data and need to group items in the same style together. Is there any function can help me?
Example:
CODE// CROSS-SELL
131-A // 131-B, 131-C, 131-D, 131-E
131-B // 131-A, 131-C, 131-D, 131-E
131-C // 131-A, 131-B, 131-D, 131-E
131-D // 131-A, 131-B, 131-C, 131-E
131-E // 131-A, 131-B, 131-C, 131-D
133-A //
133-B //
133-C //
133-D //
133-E //
134-A //
134-B //
134-C //
134-D //
134-A //
135-B //
135-C //
135-D //
135-E //
135-F //

Welcome to Excel Forum!, you might want to amend your title as per rule 1, that said, why not use autofilter or do you physicaly want them grouped elsewhere?

String concatenation is a bit tricky and is not a builtin function to excel, you'll have to add this capability in. I couldn't figure out how to remove the original entry easily, but I was able to use a string concatenation User Defined Function to list all the versions of a single code in one cell.

Based on your example, all the original codes come BEFORE the hyphen, so I used the hyphen as the break-point to determining which items are "matches" on any given row.

First, you have to install the StringConcat() function into your spreadsheet:

1. Open up your workbook.
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given below)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet.

Here's the code to add:

Please Login or Register  to view this content.

Now, there are a LOT of string concatenation UDFs out there, many simpler than this. The benefit of THIS one is that it takes complicated array formulas as a filtering criteria. That's good. On your sheet, in cell B2, this would be the first array formula:

=stringconcat(",",IF(ISNUMBER(SEARCH(LEFT(A2,FIND("-",A2)-1),$A$2:$A$50)),$A$2:$A$50,""))
...confirmed by pressing CTRL-SHIFT-ENTER.

Curly braces { } will appear around your formula and the first set of comma-delimited string values will appear...all the ones with 131 as the "code".

The first parameter in red is the "delimiter"..you can make that anything you want. I used a comma.

The second parameter in blue is the array-filtering criteria, this time in a complicated IF() array.

Take a look, sorry I couldn't figure out how to NOT list the entry in the CODE column, too, but this at least is usable.

Originally Posted by lunlum57

Hi Simon,

How can I edit the title?
I can only edit the content.

After clicking Edit on your original post, click Go Advanced... and then Edit your title.

Originally Posted by NBVC

After clicking Edit on your original post, click Go Advanced... and then Edit your title.

NBVC, it's out of the edit time window i think as we only have 2 days to edit our titles/content

Originally Posted by Simon Lloyd

NBVC, it's out of the edit time window i think as we only have 2 days to edit our titles/content

Thanks Simon.. thought it was a fresh post... anyways, hopefully OP gets the message about thread titles