Sorting

[Veronica]

How can I sort a list of names that contain multiple names within the same
cell, by one specific name? Example:

Name
1 Paul; Mark; Amy
2 John
3 Chris; Amy

How can I sort the name collumn by the name "Amy"?

Thank you in adavance!

 

[Frank Kabel]

Hi
you have to split the contents in separate cells

 

[Myrna Larson]

You can't. If you put the name of interest in, say, F3, you can put a formula
in a "helper" column that looks like this

=IF(COUNTIF(A1,"*"&$F$3&"*")<>0,"A","B")

Copy the formula down. If the name was found, you get A, otherwise B. If you
sort ascending on this helper column, the rows that contain the name will be
at the top.

 

[Myrna Larson]

Hi, Frank:

I see a problem with that approach.

Using her example, Amy would be in the 3rd column in row 1, in the 2nd column
in the 3rd row. There's no easy way to sort it unless Amy always ends up in
the same column. Or am I missing the obvious (again)?

 

[Frank Kabel]

Hi Myrna
no you're not missing anything. It was just me who missed the different
type of values. Now the question is HOW the OP wants to sort?
- always by the last name in the cell
- or all cells with 'Amy' at the beginning

 

[Veronica]

How can I split the cells?

Hi
you have to split the contents in separate cells

 

[Veronica]

Dear Myrna,

Thank you so much for your help! The formula worked perfectly!

Just so I can understand it, what does the "*" mean and the <>0?

Once again, thank you very much! I really appreciate it!

Best regards,
Veronica

 

[Myrna Larson]

I don't know, either, but I assumed the 2nd -- all rows with AMY at thetop.

 

[Myrna Larson]

You use Data/Text to columns, but as I said in my reply, you won't be able to
do the sort on the results unless the name you're looking for is always in the
same position in the cell, so it always ends up in the same column.

Did you try my suggestion?

 

[Myrna Larson]

The asterisk (*) is a wild card. Sticking an asterisk on either side of the
text you're looking for allows the countif formula to return a number greater
than 0 if the text is located anywhere in the cell. <>0 means that the count
must be "not equal to" 0. I could also have written it as >0 (greater than 0)
or =1.

 

[Veronica]

Thank you so much once again!

The asterisk (*) is a wild card. Sticking an asterisk on either side of the
text you're looking for allows the countif formula to return a number greater
than 0 if the text is located anywhere in the cell. <>0 means that the count
must be "not equal to" 0. I could also have written it as >0 (greater than 0)
or =1.