alfabetic wording
Posted by john vermeulen on July 06, 2001 8:52 AM
Hi I am from the netherlands so my english is not that good however i try to explain the problem in here
this is what i want:
i have an excel sheet with gemstones and disseases
for example:
mountain chrhystal headache blood pain
diamond pain headache swears
what i want is that in all different fields you have sometimes the same thing in this example pain
now i want to create a list wich said:
pain mountain chrystal diamond
so all disseases will be mentioned but the gemstone
is now behind all disseases
I hope my explenation is clear and hope someone can help me on this
Thx in advance
Posted by faster on July 06, 2001 9:30 AM
You might try using autofilter. From the menu select
Data>Filter>Autofilter. You can then filter by
*pain* in the appropriate column.
Posted by Roger on July 07, 2001 7:32 PM
I'm sure that there is a more direct way to do this, but here's what I came up with. I hope that it helps.
I assumed that each disease/condition was in a separate cell and that the conditions could appear in any order (for example, in your example, pain was first on the list for one gemstone and last for another).
I also assume that you have or can easily compile a list of all of the diseases/conditions. Let's say that you have 1000 rows of data and a total of 26 conditions. In the first column, you have the names of the gemstones.
On row 1001, list each of the possible diseases in a separate cell (e.g., in cells a1001 thru z1001).
In cell a1002, enter the following formula:
=if(isna(match(a$1001,$b1:$aa1,0)),1,$a1)
then copy that formula down through row 2001 and column z. What you should end up with is a table with the diseases/conditions at the top and then the corresponding gemstone names underneath that disease/condition. Where there is no match, the number 1 will appear. The number 1 is there to make it easier to compress the entries in the next step.
To compress the table (to get rid of the unmatched conditions), highlight everything from a1002 through z2001. Then press the F5 key, click on SPECIAL, click on FORMULAS, clear all check marks except for NUMBERS, click on OK, then RIGHT click, choose DELETE, choose SHIFT CELLS UP, and finally click OK. All of those "1" entries will be deleted and if there are any entries below them, they'll shift upwards.
It seems like there should be an easier way to do it, but at least this seems to work.
