Normalize/concatenate

  • Thread starter Thread starter EllenM
  • Start date Start date
E

EllenM

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
 
Is this what you are looking for:
=a1&"<br />"&right(a2,7)&"<br />"&right(a3,7)&"<br />"&right(a4,7)
?

Regards,
Fred
 
=E2&"<br/>"&E3&"<br/>"&E4&"<br/>"&E5&"<br/>"&E6
--
Please click "yes" if this post helped you!

Greatly appreciated

Eva
 
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.
 
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
 
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.

:)
 
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
 
Thanks, Fred. I'll give it a try.

Fred Smith said:
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



.
Click to expand...
 
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.
 
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
 
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
Click to expand...

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)
 
Back
Top