| Home |
| Search |
| Today's Posts |
|
#1
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.105<br /175.300<br /175.380<br /175.390<br /176.170 Thanks in advance, Ellen |
|
#2
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.105<br /175.300<br /175.380<br /175.390<br /176.170 Thanks in advance, Ellen |
|
#3
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.105<br /175.300<br /175.380<br /175.390<br /176.170 Thanks in advance, Ellen |
|
#4
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
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
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
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
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
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
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. . . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Normalize/concatenate
On Mar 10, 4:09*am, 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.105<br /175.300<br /175.380<br /175.390<br /176.170 Thanks in advance, Ellen why don't u just use a pivot table? it would be easier and it hide duplicate value. another way would be to show all the value and hide the duplicates using a formula like AA2=IF(A2=A1,"",A2) for all the columns u want to hide duplicate value (input in A2:E100, output in AA2:AE100) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| normalize score | Excel Worksheet Functions | |||
| how to normalize in excel? | Excel Discussion (Misc queries) | |||
| Normalize score | Excel Worksheet Functions | |||
| Normalize | Excel Worksheet Functions | |||
| Normalize the second pie in a pie of pie chart | Charts and Charting in Excel | |||
Linear Mode
