Excel 2007 : combing columns in a cell and separating with a coma

[nafri]

hi

i am trying to find a solution to this problem.I have looked at various post but can seem to put anything together to get the outcome i need

A1 model   B1  poduct 
A2 pp2      B2  lop2 
A3 pp2      B3  lop4 
A4 pp3      B4  lop5 
A5 pp3      B5  lop6 
A6 pp3      B6  lop7  

A1 contains multiple similar model and b1 contains products what assosiate to that model, i want to print out in Coloumn C products which have same model number in Row A .separated by a coma.For example like this

A1 model   B1 product           C1 
A2 pp2      B2  lop2               C2   lop2,lop4 
A3 pp2      B3  lop4               C3 (stays empty as result is copied into C2) 
A4 pp3      B4  lop5               C4 lop5,lop6.lop7 
A5 pp3      B5  lop6               C5 (stays empty as result is copied into C4) 
A6 pp3      B6  lop7               C6 (stays empty as result is copied into C4)  

The excel file has over 12000 rows and doing it by hand is taking way too long and the list changes daily.

Any help would be appreciated.

kind regards

Nafri

[JBeaucaire]

Hi Nafri, welcome to the forum.

What you're describing is called String Concatenation. It's not a normal built-in function in Excel, but enough people have written User Defined Functions to add to their worksheets that it is easy to do nowadays. Have you ever worked with a UDF? If not, this will be new but not difficult.

First, to add code, you're going to have to save your sheet as .xlsm format, remember that. Here's the code we're going to add to your worksheet:

Please Login or Register  to view this content.

========
How to install the User Defined Function:

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 above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

=============
Now, let's say the data looks exactly like your sample, with Models in A2 and down and Product codes in B2 and down.

In C2, put this formula that uses your new UDF function:
=CONCATIF($A:$A, A2, $B:$B, ",", TRUE))

Copy that down. See how it reads a lot like a SUMIF() formula? The added 4th parameter is the delimiter you want to use, and the 5th TRUE/FALSE parameter is where you instruct the function to remove duplicates or not. If TRUE, the lop2 would only appear once if it were found multiple times as a match.

Now that formula caused a concatenated string to appear in every row, and you said you only wanted a string in the FIRST row of each new model #, so now that you've seen the base UDF in action, here's a longer formula to get the real result you wanted:

=IF(COUNTIF($A$1:A2, A2) > 1, "",CONCATIF($A:$A, A2, $B:$B, ",", TRUE))

Attached is a sample book so you can see it.