I have a large spreadsheet that has job numbers in column A and broken up job
descriptions in column C. I'm looking for a way to concatenate the text in
the cells in column C, but it has to concatenate based on what job number is
in column A. For example:

A B C
01 Picks fruit.
01 Trims trees.
02 Does data entry.
02 Reviews records.
02 Maintains files.
03 Gives shots.
03 Monitors blood pressure.

Based on this type of information, somewhere in the spreadsheet (after each
change in column A or at the very bottom), I need lines that state the
following:

01 Picks fruit. Trims trees.
02 Does data entry. Reviews records. Maintains
files.
03 Gives shots. Monitors blood pressure.

You could perhaps do this with the MCONCAT function available with the
MOREFUNC addin, download MOREFUNC from here

http://www.rhdatasolutions.com/morefunc/

then you could use this formula in D1 copied down column

=IF(A1=A2,"",A1&" "&MCONCAT(INDEX(C$1:C1,MATCH(A1,A$1:A1,0)):C1, " "))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=512925

On Wed, 15 Feb 2006 13:35:28 -0800, "BCBC"
wrote:

I have a large spreadsheet that has job numbers in column A and broken up job
descriptions in column C. I'm looking for a way to concatenate the text in
the cells in column C, but it has to concatenate based on what job number is
in column A. For example:

A B C
01 Picks fruit.
01 Trims trees.
02 Does data entry.
02 Reviews records.
02 Maintains files.
03 Gives shots.
03 Monitors blood pressure.

Based on this type of information, somewhere in the spreadsheet (after each
change in column A or at the very bottom), I need lines that state the
following:

01 Picks fruit. Trims trees.
02 Does data entry. Reviews records. Maintains
files.
03 Gives shots. Monitors blood pressure.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Ensure your table of JobNum | JobDesc is sorted ascending by JobNum as you
show in your example.

3. F2: 1
F3: 2
F4: 3

G2:
=MCONCAT(OFFSET(JobDesc,MATCH(F2,JobNum,0)-1,0,COUNTIF(JobNum,F2)))

Copy/drag down


--ron

Hello

I suggest to take my UDF vlookupall() from
http://www.sulprobil.com/html/vlookupall.html

HTH,
Bernd