|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
March 9th, 2010, 08:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Normalize/concatenate
Hello,
I have a data set that looks like this: Sortterm Doc # Main Term CAS # CFR REG # DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.300 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.380 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.390 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 176.170 I'd like to concatentate the last column with the unique values to look like this: Sorterm DocNum Mainterm CAS Regnum DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105br /175.300br /175.380br /175.390br /176.170 Thanks in advance, Ellen 
|
|
#2
March 9th, 2010, 08:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
Is this what you are looking for:
=a1&"br /"&right(a2,7)&"br /"&right(a3,7)&"br /"&right(a4,7) ? Regards, Fred "EllenM" wrote in message ... Hello, I have a data set that looks like this: Sortterm Doc # Main Term CAS # CFR REG # DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.300 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.380 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.390 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 176.170 I'd like to concatentate the last column with the unique values to look like this: Sorterm DocNum Mainterm CAS Regnum DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105br /175.300br /175.380br /175.390br /176.170 Thanks in advance, Ellen
|
|
#3
March 9th, 2010, 08:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
=E2&"br/"&E3&"br/"&E4&"br/"&E5&"br/"&E6
-- Please click "yes" if this post helped you! Greatly appreciated Eva "EllenM" wrote: Hello, I have a data set that looks like this: Sortterm Doc # Main Term CAS # CFR REG # DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.300 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.380 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.390 DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 176.170 I'd like to concatentate the last column with the unique values to look like this: Sorterm DocNum Mainterm CAS Regnum DIPHTHALATE 7065 DI(2-ETHYLHEXYL) PHTHALATE 000117817 175.105br /175.300br /175.380br /175.390br /176.170 Thanks in advance, Ellen
|
|
#4
March 9th, 2010, 09:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
Thanks, but actually there's lots more data than that. For example:
5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help.
|
|
#5
March 9th, 2010, 10:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
As always, it's useful to define your full requirements up front to save
everyone's time. Is your data in separate columns, or all in one column? If separate, how many columns, and what do you want in between them when they're concatenated (a space?)? If all in one column, what defines what you want to concatenate? Everything after the first space and before the last space is the same? Regards, Fred "EllenM" wrote in message ... Thanks, but actually there's lots more data than that. For example: 5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help.
|
|
#6
March 10th, 2010, 12:50 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
Hi Fred,
There's 6 columns, which I'd like to retain. The only difference will be that there will be no duplicates among the first 5 columns. The second column will a concatenation of the unique values in the last column. Thanks so much for asking and for all attention that you've given to my question.  "Fred Smith" wrote: As always, it's useful to define your full requirements up front to save everyone's time. Is your data in separate columns, or all in one column? If separate, how many columns, and what do you want in between them when they're concatenated (a space?)? If all in one column, what defines what you want to concatenate? Everything after the first space and before the last space is the same? Regards, Fred "EllenM" wrote in message ... Thanks, but actually there's lots more data than that. For example: 5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help. .
|
|
#7
March 10th, 2010, 06:03 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
Here's how I would handle it. This assumes your columns are A-F, with data
starting in row 2. G1: "Reg Nums" G2: =IF(AND(A2=A1,B2=B1,C2=C1,D2=D1,E2=E1),F2&"br /"&G1,F2) H1: "Final?" H2: =IF(AND(A2=A3,B2=B3,C2=C3,D2=D3,E2=E3),"","Y") Copy down Each row with "Y" in column H will be the last row of a group, and column G will have all the Reg #'s in it. You can then filter for only the Y's, and do what you want with the visible cells. Regards, Fred "EllenM" wrote in message ... Hi Fred, There's 6 columns, which I'd like to retain. The only difference will be that there will be no duplicates among the first 5 columns. The second column will a concatenation of the unique values in the last column. Thanks so much for asking and for all attention that you've given to my question. "Fred Smith" wrote: As always, it's useful to define your full requirements up front to save everyone's time. Is your data in separate columns, or all in one column? If separate, how many columns, and what do you want in between them when they're concatenated (a space?)? If all in one column, what defines what you want to concatenate? Everything after the first space and before the last space is the same? Regards, Fred "EllenM" wrote in message ... Thanks, but actually there's lots more data than that. For example: 5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help. .
|
|
#8
March 10th, 2010, 01:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
Thanks, Fred. I'll give it a try.
"Fred Smith" wrote: Here's how I would handle it. This assumes your columns are A-F, with data starting in row 2. G1: "Reg Nums" G2: =IF(AND(A2=A1,B2=B1,C2=C1,D2=D1,E2=E1),F2&"br /"&G1,F2) H1: "Final?" H2: =IF(AND(A2=A3,B2=B3,C2=C3,D2=D3,E2=E3),"","Y") Copy down Each row with "Y" in column H will be the last row of a group, and column G will have all the Reg #'s in it. You can then filter for only the Y's, and do what you want with the visible cells. Regards, Fred "EllenM" wrote in message ... Hi Fred, There's 6 columns, which I'd like to retain. The only difference will be that there will be no duplicates among the first 5 columns. The second column will a concatenation of the unique values in the last column. Thanks so much for asking and for all attention that you've given to my question. "Fred Smith" wrote: As always, it's useful to define your full requirements up front to save everyone's time. Is your data in separate columns, or all in one column? If separate, how many columns, and what do you want in between them when they're concatenated (a space?)? If all in one column, what defines what you want to concatenate? Everything after the first space and before the last space is the same? Regards, Fred "EllenM" wrote in message ... Thanks, but actually there's lots more data than that. For example: 5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help. . .
|
|
#9
March 10th, 2010, 05:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
Hi Fred,
I like your logic. All the data, however, is in a single row with column E containing the data that need to be concatenated. "Fred Smith" wrote: Here's how I would handle it. This assumes your columns are A-F, with data starting in row 2. G1: "Reg Nums" G2: =IF(AND(A2=A1,B2=B1,C2=C1,D2=D1,E2=E1),F2&"br /"&G1,F2) H1: "Final?" H2: =IF(AND(A2=A3,B2=B3,C2=C3,D2=D3,E2=E3),"","Y") Copy down Each row with "Y" in column H will be the last row of a group, and column G will have all the Reg #'s in it. You can then filter for only the Y's, and do what you want with the visible cells. Regards, Fred "EllenM" wrote in message ... Hi Fred, There's 6 columns, which I'd like to retain. The only difference will be that there will be no duplicates among the first 5 columns. The second column will a concatenation of the unique values in the last column. Thanks so much for asking and for all attention that you've given to my question. "Fred Smith" wrote: As always, it's useful to define your full requirements up front to save everyone's time. Is your data in separate columns, or all in one column? If separate, how many columns, and what do you want in between them when they're concatenated (a space?)? If all in one column, what defines what you want to concatenate? Everything after the first space and before the last space is the same? Regards, Fred "EllenM" wrote in message ... Thanks, but actually there's lots more data than that. For example: 5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help. . .
|
|
#10
March 10th, 2010, 05:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
You'll need to be more specific (and more consistent) in telling us how your
data is laid out. What do you mean by "all the data is in a single row"? If so, what separates the lines? Regards, Fred "EllenM" wrote in message ... Hi Fred, I like your logic. All the data, however, is in a single row with column E containing the data that need to be concatenated. "Fred Smith" wrote: Here's how I would handle it. This assumes your columns are A-F, with data starting in row 2. G1: "Reg Nums" G2: =IF(AND(A2=A1,B2=B1,C2=C1,D2=D1,E2=E1),F2&"br /"&G1,F2) H1: "Final?" H2: =IF(AND(A2=A3,B2=B3,C2=C3,D2=D3,E2=E3),"","Y") Copy down Each row with "Y" in column H will be the last row of a group, and column G will have all the Reg #'s in it. You can then filter for only the Y's, and do what you want with the visible cells. Regards, Fred "EllenM" wrote in message ... Hi Fred, There's 6 columns, which I'd like to retain. The only difference will be that there will be no duplicates among the first 5 columns. The second column will a concatenation of the unique values in the last column. Thanks so much for asking and for all attention that you've given to my question. "Fred Smith" wrote: As always, it's useful to define your full requirements up front to save everyone's time. Is your data in separate columns, or all in one column? If separate, how many columns, and what do you want in between them when they're concatenated (a space?)? If all in one column, what defines what you want to concatenate? Everything after the first space and before the last space is the same? Regards, Fred "EllenM" wrote in message ... Thanks, but actually there's lots more data than that. For example: 5964 N-ACETYL-N-(2-HYDROXYETHYL)-N'-OLEOYLETHYLENEDIAMINE 101229032 177.28 5006 ACETYL TRIETHYL CITRATE 77894 175.105 5007 ACETYL TRIETHYL CITRATE 77894 175.3 5008 ACETYL TRIETHYL CITRATE 77894 175.32 5009 ACETYL TRIETHYL CITRATE 77894 178.391 5010 ACETYL TRIETHYL CITRATE 77894 181.27 You can see the final result at http://www.accessdata.fda.gov/script...?rpt=iaListing. The br / causes the regnums to line up in a column. Just looking for a simpler way to prepare this data. Thanks for your help. . .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
