Prev: Earliest time in a range
Next: Can a formula be "built" referencing text content from another cel
From: EllenM on 9 Mar 2010 15:09
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



From: Eva on 9 Mar 2010 15:17
=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
>
>
>
From: Fred Smith on 9 Mar 2010 15:16
Is this what you are looking for:
=a1&"<br />"&right(a2,7)&"<br />"&right(a3,7)&"<br />"&right(a4,7)
?

Regards,
Fred

"EllenM" <EllenM(a)discussions.microsoft.com> wrote in message
news:25F0F53A-5298-4B67-9CBC-0725C543E094(a)microsoft.com...
> 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
>
>
>

From: EllenM on 9 Mar 2010 16:16
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/scripts/fcn/fcnNavigation.cfm?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.
From: Fred Smith on 9 Mar 2010 17:20
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" <EllenM(a)discussions.microsoft.com> wrote in message
news:542F61F4-80D0-4043-ABAD-AD8823D76369(a)microsoft.com...
> 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/scripts/fcn/fcnNavigation.cfm?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.

 |  Next  |  Last
Pages: 1 2 3
Prev: Earliest time in a range
Next: Can a formula be "built" referencing text content from another cel