Normalize/concatenate [Functions]

Prev: Earliest time in a range
Next: Can a formula be "built" referencing text content from another cel

From: EllenM on 9 Mar 2010 19:50

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" <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 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 10 Mar 2010 01:03

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&" "&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" <EllenM(a)discussions.microsoft.com> wrote in message
news:66FD2D44-30C8-43AC-B4BE-4C33DE9BE5B1(a)microsoft.com...
> 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" <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 causes the regnums to line up in a column.
>> > Just looking for a simpler way to prepare this data.
>> >
>> > Thanks for your help.
>>
>> .
>>

From: EllenM on 10 Mar 2010 08:44

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&" "&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" <EllenM(a)discussions.microsoft.com> wrote in message
> news:66FD2D44-30C8-43AC-B4BE-4C33DE9BE5B1(a)microsoft.com...
> > 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" <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 causes the regnums to line up in a column.
> >> > Just looking for a simpler way to prepare this data.
> >> >
> >> > Thanks for your help.
> >>
> >> .
> >>
>
> .
>

From: EllenM on 10 Mar 2010 12:15

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&" "&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" <EllenM(a)discussions.microsoft.com> wrote in message
> news:66FD2D44-30C8-43AC-B4BE-4C33DE9BE5B1(a)microsoft.com...
> > 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" <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 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 10 Mar 2010 12:48

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" <EllenM(a)discussions.microsoft.com> wrote in message
news:02A509F6-11B5-43F4-A1DD-7B7C06E263DD(a)microsoft.com...
> 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&" "&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" <EllenM(a)discussions.microsoft.com> wrote in message
>> news:66FD2D44-30C8-43AC-B4BE-4C33DE9BE5B1(a)microsoft.com...
>> > 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" <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 causes the regnums to line up in a column.
>> >> > Just looking for a simpler way to prepare this data.
>> >> >
>> >> > Thanks for your help.
>> >>
>> >> .
>> >>
>>
>> .
>>

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