Simple Ranking - please help

[john]

hi,

I have a Excel sheet that has five columns of data. i want
to prioritise the data as a user ammends it. in other
words for example:

Index name col1 col2 col3 col4 col5
----------------------------------------------------
cat cat dog mouse rat bear
cat dog cat sheep bear squiral
cat dog mouse cat rat bear
cat dog squiral sheep bear cat
dog dog cat sheep bear squiral
dog dog mouse cat rat bear
dog dog squiral sheep bear cat
sheep dog cat sheep bear squiral
sheep dog squiral sheep bear cat
sheep dog mouse cat rat bear

So the index name is the priority and you can see the
order of the word cat in the first four rows.

Any ideas?

Cheers

John

 

[Frank Kabel]

Hi John
you posted this several times. And I assume that the problem with your
question is that most don't understand what you're trying to do. What
do you mean with 'prioritise'. It just looks like a simple sorting
(maybe in combination with a custom list). Also I'm not sure if this is
your real data (or if this is just some sample data).
So why does a sort not work for you?

 

[john]

Frank,

i dont think i can put it any simpler. Basically if the
name i want in a row of data is listed in column one then
it is ranked higher in the list that if the same name
appears in say column 2 on a differnt row.

Make sense?

Thanks
John

 

[Frank Kabel]

Hi
I think now I understand. The index name is stored in column A? and
your data in column B:F. If yes you may use the following formula in a
helper column. Lets say in cell G1 enter the following formula
=IF(ISNA(MATCH(A1,B1:F1,0)),100,MATCH(A1,B1:F1,0))
copy this down for all rows.
After this select the entire range (e.g. A1:G30) and goto 'Data - Sort'
- use column A as the first sort criteria and column G as the second
one (sorted ascending)

 

[john]

Frank,

Thanks for this. I'll try this first thing tomorrow.

I presume that the word MATCH is substituted with the word
i'm searching for (cat)?

Thanks

John

 

[Frank Kabel]

Hi
no: MATCH looks for the value A1 in your range. I assumed that you have
the value to search in column A. If you want to enter this manually use
the following formula:
=IF(ISNA(MATCH("cat",A1:F1,0)),100,MATCH("cat",A1:F1,0))

 

[john]

Frank,

I just seam to get a list of numbers and not the text.
Also i need to link the list to another sheet in my
workbook.

Any ideas?

John

 

[Frank Kabel]

Hi
not sure what you mean with this. Of course this formula generates only
numbers by which you then can sort your data?