Excel 2007 : AutoFilter

[Paddon]

Hey all,

So I have searched high and low and have been unable to find a work around for this problem.

I have a table of data with filters on the columns so I can easily and quickly sort and print. The issue at hand is that some columns have multiple values. For example there is an ownership heading and some entries would have multiple owners. I want to be able to sort the entries by owners, so that essentially if, say, I filtered the data by owner "Bob Smith" ALL entries owned by "Bob Smith" would appear, even if he was merely part owner.

Initially I had all the owners in the same cell for each entry but that lead to problems filtering. So instead I put the owners in different cells and merged all those other headings that didn't require multiple entires. This works well except for the fact that when I filter by an owner it shirks the Row... such that the other data isn't displayed well. Let me clarify with an example.

Please Login or Register  to view this content.

Now I merged rows [2] & [3] (just in columns [1] &[2]) and when I sort column [3] via "Bob Smith" I want all the information for Asset 1 & Asset 7 to appear, including the "John Doe" row.

Any thoughts?

Thanks in advance,
Paddon

[Marcol]

Don't merge the cells!

Move John Doe to row 2 and maybe call the column "Owner 2"

[Paddon]

That works in this case but there could be any number of owners. Furthermore, I merged the cells so that it the information is well laid out as to which information corresponds to which asset.

[shg]

Any information that includes merged cells is not well laid out.

[Paddon]

True, but I am hard pressed as how to do a better job laying it out given what I am trying to accomplish.

I would prefer to have all the owner information in one cell... but it doesn't work well when filtering and the quick easy filtering is what I am looking for our of the spreadsheet.

[banana-pumpkin]

I can't promise this will work because I'm not sure how Excel formats merged cells, but try running a text filter with the text

"*"&"Bob Smith"&"*"

This will find any cell containing the phrase "Bob Smith" and any other text as well, so should hopefully work for you.