I need to concatenate a number of lines of text that is
broken up by comment lines. E.g:
Workorder Comment Line Comment
12334 1 Engineer has replaced the
12334 2 motherboard and requested
12334 3 new power supply
12523 1 Call needs to be cancelled
12523 2 due to customer locating
12523 3 brain, hence problem with
12523 4 screen was in head
Is there a way that I can setup a formula to work out
which comment lines relate to the same workorder number,
then concatenate them onto one line? I would greatly
appreciate any advise!
Yes, but . . . this is a poor layout for free-form text entry. Why don't you
just use a single cell for comment entry and enable Word Wrap in the comment
cells? Your users would likely find that easier to use than manually breaking up
comments into multiple lines, and you'd find it easier because it would rended
the current problem moot.
But to the question at hand, unless there's a maximum possible number of comment
lines per work order code, there's no general formula that can do this without
add-in or user-defined functions (udfs).
Assuming there's a maximim possible number of comment lines, e.g., 10, and if
the source table were named TBL and it was sorted by work order code then
comment line number, and if the current work order code were in a cell named X,
you could try
=VLOOKUP(X,TBL,3,0)
&IF(COUNTIF(INDEX(TBL,0,1),X)>1,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+1,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>2,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+2,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>3,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+3,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>4,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+4,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>5,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+5,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>6,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+6,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>7,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+7,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>8,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+8,3),"")
&IF(COUNTIF(INDEX(TBL,0,1),X)>9,INDEX(TBL,MATCH(X,INDEX(TBL,0,1),0)+9,3),"")
It'd be considerably easier if you used two ancillary cells for each work order
code, one to hold the number of comment lines matching the work order code and
the other giving the row index of the topmost comment line matching the work
order code. If the former were a cell named CNT and the latter named TOP, then
CNT:
=COUNTIF(INDEX(TBL,0,1),X)
TOP:
MATCH(X,INDEX(TBL,0,1),0)
and the formula could be shortened to
=INDEX(TBL,X,3)&IF(CNT>1,INDEX(TBL,TOP+1,3),"")
&IF(CNT>2,INDEX(TBL,TOP+2,3),"")&IF(CNT>3,INDEX(TBL,TOP+3,3),"")
&IF(CNT>4,INDEX(TBL,TOP+4,3),"")&IF(CNT>5,INDEX(TBL,TOP+5,3),"")
&IF(CNT>6,INDEX(TBL,TOP+6,3),"")&IF(CNT>7,INDEX(TBL,TOP+7,3),"")
&IF(CNT>8,INDEX(TBL,TOP+8,3),"")&IF(CNT>9,INDEX(TBL,TOP+9,3),"")
There have been several generalized concatenation udfs given over the years in
this newsgroup. Check the Google Groups archive for them (search for Function
and concatenate as keywords). If you use any of them along with the 4 defined
names already given, you could use an array formula like
=TRIM(mcat(" "&OFFSET(TBL,TOP-1,2,CNT,1)))
where mcat is such a udf.
