Conditional Concatenation

  • Thread starter Thread starter IamN2Deep
  • Start date Start date
I

IamN2Deep

Hi all,

I'm hoping that someone can help me with this problem.

I have two cloumns of data. In column A I have a 7 digit number. The
first 5 digits are a code number and the last two digits are a counter
that corresponds to the data in column B. Column B contains
descriptions of the codes in column A. When there is more than one
line of description then the counter digits are incremented in column
A.

I need to concatenate all the discription lines for a given 5 digit
code.

Column A Column B
1111101 Description Line 1
1111102 Description Line 2
1111103 Description Line 3
1111104 Description Line 4
2222201 Description Line 1
3333301 Description Line 1
3333302 Description Line 2

There may be as many as 10 description lines for each 5 digit code.

Any ideas? Anyone? Help!
 
IamN2Deep said:
Hi all,

I'm hoping that someone can help me with this problem.

I have two cloumns of data. In column A I have a 7 digit number. The
first 5 digits are a code number and the last two digits are a counter
that corresponds to the data in column B. Column B contains
descriptions of the codes in column A. When there is more than one
line of description then the counter digits are incremented in column
A.

I need to concatenate all the discription lines for a given 5 digit
code.
Click to expand...

Sounds like more of an MS Access job to me.

JW
 
Try =INDIRECT("$M"&(RIGHT(A1,2)))

The RIGHT command grabs the last digit(s) of the number
and the INDIRECT creates a cell reference accordingly.

To make the above work, I put your codes in A1:A6, the
descriptions in M1:M6 and referenced the descriptions with
the above code in B1.
 
That's close! Perhaps I should have been more clear in my explanation
of the descriptions. Take a look at my modified data.

Column A Column B
1111101 Description Line 1 For Code 11111
1111102 Description Line 2 For Code 11111
1111103 Description Line 3 For Code 11111
1111104 Description Line 4 For Code 11111
2222201 Description Line 1 For Code 22222
3333301 Description Line 1 For Code 33333
3333302 Description Line 2 For Code 33333

In other words, each 5 digit code has a completely different
description.

There is no short list of descriptions. Does that make sense?
 
Ok, let's say your data is in A1:B6

put the following in cell C1:

=B1

Put the following in cell C2 and copy down:

=IF(LEFT(A2,5)=LEFT(A1,5),C1&B2,B2)


Howzat?

P
 
Back
Top