Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
And there's a picture with instructions he
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change sort defaults for combined text number & hyphen. | Excel Discussion (Misc queries) | |||
sort text as numbers excell2003 to 2000 | Excel Discussion (Misc queries) | |||
Can you sort text in a random order in Excel | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Can you sort text in a column, but leave cell color alone? | New Users to Excel |