How do I sort using text located in different columns?

I know this is a bad design, but in my worksheet I want to sort using text
entries (for example," dist" or "bp") that are (or may be) located in five
separate columns. The entries have the same meaning regardless of column,
and I want to capture all of the entries in a single sort. Is this possible,
or do I need to go to a relational database? I don't want to construct a
worksheet with separate columns for each entry type because there are simply
too many types. Thanks.

Does this mean that there is only one " dist" or "bp" in those 5 columns?

if yes, you could use a formula like:

=if(countif(a1:e1," dist")>0," dist","")&if(countif(a1:e1,"bp")>0,"bp","")
drag it down
Then sort by that helper column.



Neil wrote:
>
> I know this is a bad design, but in my worksheet I want to sort using text
> entries (for example," dist" or "bp") that are (or may be) located in five
> separate columns. The entries have the same meaning regardless of column,
> and I want to capture all of the entries in a single sort. Is this possible,
> or do I need to go to a relational database? I don't want to construct a
> worksheet with separate columns for each entry type because there are simply
> too many types. Thanks.

--

Dave Peterson

Dave, thanks for responding. I think what you're suggesting will work,
although I'm not sure what "drag it down" means (I'm new to Excel). But if I
put the formula in its own column, I think that would do it. p.s. It would
be an astounding coincidence, but by any chance are you the Dave Peterson who
forty years ago lived on Edgecliff Road and was one of my best friends when I
was a kid?

"Dave Peterson" wrote:

> Does this mean that there is only one " dist" or "bp" in those 5 columns?
>
> if yes, you could use a formula like:
>
> =if(countif(a1:e1," dist")>0," dist","")&if(countif(a1:e1,"bp")>0,"bp","")
> drag it down
> Then sort by that helper column.
>
>
>
> Neil wrote:
> >
> > I know this is a bad design, but in my worksheet I want to sort using text
> > entries (for example," dist" or "bp") that are (or may be) located in five
> > separate columns. The entries have the same meaning regardless of column,
> > and I want to capture all of the entries in a single sort. Is this possible,
> > or do I need to go to a relational database? I don't want to construct a
> > worksheet with separate columns for each entry type because there are simply
> > too many types. Thanks.
>
> --
>
> Dave Peterson
>

The easy answer first. Nope, that ain't me. But with a name like that, I bet
he was a generous, giving, good looking kid! (I think that this is mandatory if
you have this name. <vbg>)

Drag down just means to put the formula in a cell. Then use that little box in
the lower right hand corner of the cell selector and drag it down through all
the rows you need. (That little box is sometimes called the autofill handle.)

You could also just put the formula in one cell, edit|copy that cell, and select
all the cells that this formula should go into. Then Edit|Paste.

Debra Dalgleish has a picture of a selected cell on:
http://www.contextures.com/xlfaqApp.html#HeaderNumber
(the instructions are for something else, but the picture shows the autofill
handle in the selected cell)

Neil wrote:
>
> Dave, thanks for responding. I think what you're suggesting will work,
> although I'm not sure what "drag it down" means (I'm new to Excel). But if I
> put the formula in its own column, I think that would do it. p.s. It would
> be an astounding coincidence, but by any chance are you the Dave Peterson who
> forty years ago lived on Edgecliff Road and was one of my best friends when I
> was a kid?
>
> "Dave Peterson" wrote:
>
> > Does this mean that there is only one " dist" or "bp" in those 5 columns?
> >
> > if yes, you could use a formula like:
> >
> > =if(countif(a1:e1," dist")>0," dist","")&if(countif(a1:e1,"bp")>0,"bp","")
> > drag it down
> > Then sort by that helper column.
> >
> >
> >
> > Neil wrote:
> > >
> > > I know this is a bad design, but in my worksheet I want to sort using text
> > > entries (for example," dist" or "bp") that are (or may be) located in five
> > > separate columns. The entries have the same meaning regardless of column,
> > > and I want to capture all of the entries in a single sort. Is this possible,
> > > or do I need to go to a relational database? I don't want to construct a
> > > worksheet with separate columns for each entry type because there are simply
> > > too many types. Thanks.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

And there's a picture with instructions here:

http://www.contextures.com/xlDataEntry01.html#Mouse

Dave Peterson wrote:
> The easy answer first. Nope, that ain't me. But with a name like that, I bet
> he was a generous, giving, good looking kid! (I think that this is mandatory if
> you have this name. <vbg>)
>
> Drag down just means to put the formula in a cell. Then use that little box in
> the lower right hand corner of the cell selector and drag it down through all
> the rows you need. (That little box is sometimes called the autofill handle.)
>
> You could also just put the formula in one cell, edit|copy that cell, and select
> all the cells that this formula should go into. Then Edit|Paste.
>
> Debra Dalgleish has a picture of a selected cell on:
> http://www.contextures.com/xlfaqApp.html#HeaderNumber
> (the instructions are for something else, but the picture shows the autofill
> handle in the selected cell)
>
> Neil wrote:
>
>>Dave, thanks for responding. I think what you're suggesting will work,
>>although I'm not sure what "drag it down" means (I'm new to Excel). But if I
>>put the formula in its own column, I think that would do it. p.s. It would
>>be an astounding coincidence, but by any chance are you the Dave Peterson who
>>forty years ago lived on Edgecliff Road and was one of my best friends when I
>>was a kid?
>>
>>"Dave Peterson" wrote:
>>
>>
>>>Does this mean that there is only one " dist" or "bp" in those 5 columns?
>>>
>>>if yes, you could use a formula like:
>>>
>>>=if(countif(a1:e1," dist")>0," dist","")&if(countif(a1:e1,"bp")>0,"bp","")
>>>drag it down
>>>Then sort by that helper column.
>>>
>>>
>>>
>>>Neil wrote:
>>>
>>>>I know this is a bad design, but in my worksheet I want to sort using text
>>>>entries (for example," dist" or "bp") that are (or may be) located in five
>>>>separate columns. The entries have the same meaning regardless of column,
>>>>and I want to capture all of the entries in a single sort. Is this possible,
>>>>or do I need to go to a relational database? I don't want to construct a
>>>>worksheet with separate columns for each entry type because there are simply
>>>>too many types. Thanks.
>>>
>>>--
>>>
>>>Dave Peterson
>>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html