In Excel 2016, I have a worksheet with two columns and many rows of data where each entry consists of a name in the first column and the fruits they like separated by commas in the other column (for example). How can I sort this data based on fruit?

I know how to sort if there is only one piece of data per cell and I can do a find all which will select all cells in the column that contain strawberries for example but I would like to sort the rows and possibly highlight the ones that contain some word (like strawberry) when there are multiple pieces of data in one cell. The user of the spreadsheet should be able to quickly find all people who like strawberries.

I have attached an example screenshot that shows how my data is organized. Would it be better to organize the data in a different way? Will this require VBA? Thanks in advance for any help.

Example Image

up vote 1 down vote accepted

If the objective is that "The user of the spreadsheet should be able to quickly find all people who like strawberries," is it not sufficient to enable column filtering (CTRL-SHIFT-L for the shortcut)?

  • I thought that in filtering you would have to type in the specific contents of the cell to the letter but it turns out you can search within filtering and it will return all rows that contain that string even if there are other things. You're right this would work just fine.Thanks! I guess I assumed it had less functionality than it had. – Kyle Jones Jun 14 at 14:35

I ended up figuring out a solution to this anyway so I figured I would post it in case it helps anyone else. It might not be the best solution but it works.

I created a column that checks whether the cell Fruits in that row contains some user entered string and displays True or False accordingly.

=ISNUMBER(SEARCH($E$1,B2))

E1 is the cell where the user inputs a string to search and B2 will change as the formula is copied down the rows

You can then sort based on this cell, do conditional formatting or whatever else you'd like to do.

example image2

  • 1
    I suppose if I wanted this to be more robust I would have to make it so that pineapple does not get returned when apple is searched – Kyle Jones Jun 14 at 14:37
  • 1
    The easy way to do that is to test =ISNUMBER(SEARCH(","&$E$1&",", ","&B2&",")).  It looks for ,apple, in ,apple,orange,blueberry, and finds it; it looks for ,apple, in ,orange,kiwi,pineapple, and doesn’t find it. – G-Man Jun 15 at 21:50

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.