Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate problem | Excel Worksheet Functions | |||
Concatenate Problem | New Users to Excel | |||
Odd Concatenate problem. | Excel Worksheet Functions | |||
Concatenate Problem | Excel Discussion (Misc queries) | |||
Concatenate Problem | Excel Worksheet Functions |