Locating duplicate entries and concatenating adjacent values

**justinwright** · 03-30-2011, 01:02 PM

Hello, I had a bit of an odd question.

I have a worksheet that has two columns, one (column A) is a number. The second one (column B), is a name.

What I was wondering is if there was a formula that would go through, see where the name was the same, then concatenate the values where the name also matched (column C), such as:

A | B | C
1 Bob Smith 1,2
2 Bob Smith 1,2
3 Jane Smith 3,4,5
4 Jane Smith 3,4,5
5 Jane Smith 3,4,5

Any help is much-appreciated. Thanks!

**NBVC** · 03-30-2011, 01:08 PM

If you add this UDF to your VB editor (ALT+F11, INSERT|MODULE)

Please Login or Register  to view this content.

Then you can use this formula:

Please Login or Register  to view this content.

confirm it with CTRL+SHIFT+ENTER keys not just ENTER and copy down.

assumes data is in A1:B5

**justinwright** · 03-30-2011, 01:09 PM

So if the list was say, A1 to A3500, the module wouldn't change? Just the formula used?

Also, if I may trouble you to ask, what is the Ctrl+Shift+Enter for?

**NBVC** · 03-30-2011, 01:13 PM

Yes, only the formula would change... but the more cells in the range and the more times the formula is used, the less efficient the worksheet gets, because this in an array formula.

The CTRL+SHIFT+ENTER identifies the formula as an ARRAY formula and makes EXCEL process the formula as such. Array Formulas