Concatenate problem
 

Hello,

I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown below:

388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV

I want to concatenate the keywords for each unique ID into one row/cell (col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.

Thanks in advance,
Todd

Concatenate problem
 

Write a function that accepts the ID as an argument, then open a recordset
using it as the criteria, loop through the records, and concacetenate, store
in a string, and update another table with the concacentated string value.
Here's something I did awhile back:

Function sCostDesc(lcindex As Variant) As Variant

Dim sReturn As Variant
Dim Db As Database, Rs As Recordset

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("Select * From cDescript Where cindex=" &
lcindex & ";")

Do While Not Rs.EOF
sReturn = sReturn & " " & Trim(Rs![cddesc])
Rs.MoveNext
Loop

sCostDesc = Trim(sReturn)

End Function

"tmwilkin" wrote in message
...
Hello,

I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown below:

388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV

I want to concatenate the keywords for each unique ID into one row/cell
(col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.

Thanks in advance,
Todd

Concatenate problem
 

Hi Todd

the way i would do it is

use the advanced filter to generate a list of the unique ID numbers in
another column on your worksheet (Debra Dalgleish has notes on how to do
this at www.contextures.com/tiptech.html) and then use a UDF that Harald
Staff and myself created called CONCAT_IF to concatenate the keywords ...
this function and notes on using it can be found at my website
www.hcts.net.au/tipsandtricks.html

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"tmwilkin" wrote in message
...
Hello,

I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown below:

388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV

I want to concatenate the keywords for each unique ID into one row/cell
(col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.

Thanks in advance,
Todd

Concatenate problem
 

Lots of good suggestions but here is one that uses just standard worksheet
functions:

In column C, assuming your IDs are sorted (put this in C1 and copy down)

=IF(ROW()=1,B2,IF(OFFSET(A2,-1,0)=A2,OFFSET(C2,-1,0)&B2,B2))

This will "build" the concatenated string in each consecutive cell where the
ID is equal - if you can live with that and extract the last line it might do
for you. If you need JUST the final result then enter a new column D beside
the one created above and in D1 enter (then copy down) the formula:

=IF(A1=A2,"",C1)

This will leave the cells in D blank until you reach the last line having a
particular ID, where it will pick up the concatenated value from C.

No coding needed!


"tmwilkin" wrote:

Hello,

I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown below:

388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV

I want to concatenate the keywords for each unique ID into one row/cell (col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.

Thanks in advance,
Todd

Concatenate problem
 

Worked perfectly. Thanks!

"JulieD" wrote:

Hi Todd

the way i would do it is

use the advanced filter to generate a list of the unique ID numbers in
another column on your worksheet (Debra Dalgleish has notes on how to do
this at www.contextures.com/tiptech.html) and then use a UDF that Harald
Staff and myself created called CONCAT_IF to concatenate the keywords ...
this function and notes on using it can be found at my website
www.hcts.net.au/tipsandtricks.html

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"tmwilkin" wrote in message
...
Hello,

I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown below:

388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV

I want to concatenate the keywords for each unique ID into one row/cell
(col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.

Thanks in advance,
Todd

Concatenate problem
 

you're welcome and i'm glad to hear that it worked :)

--
Cheers
JulieD

"tmwilkin" wrote in message
...
Worked perfectly. Thanks!

"JulieD" wrote:

Hi Todd

the way i would do it is

use the advanced filter to generate a list of the unique ID numbers in
another column on your worksheet (Debra Dalgleish has notes on how to do
this at www.contextures.com/tiptech.html) and then use a UDF that Harald
Staff and myself created called CONCAT_IF to concatenate the keywords ...
this function and notes on using it can be found at my website
www.hcts.net.au/tipsandtricks.html

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"tmwilkin" wrote in message
...
Hello,

I'm working a file with two columns of data. The first column is an ID
number, the second is a description keyword. An example is shown
below:

388643 WSPHD170440 PE170IP4 417IPEB
388643 LIPE170IP4 IN004PE170IP4
388643 ZZFAB
388643 VVFL
388643 VVNW
766220 IN004!PE110IP5
766220 VVWV

I want to concatenate the keywords for each unique ID into one row/cell
(col
B). Can anyone assist? I can work with any function that generates
duplicate rows as I could strip them out later.

Thanks in advance,
Todd