i have an excel sheet with gemstones and disseases
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
You might try using autofilter. From the menu select
Data>Filter>Autofilter. You can then filter by
*pain* in the appropriate column.
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:
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.