Filter

[ScottishLass]

Hello I'm having trouble with a spreadsheet I have created. When I turn on the filter I can't get the information to display as I want. Please see example layout below.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Committee 1[/TD]
[TD]Joe Smith[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane Brown[/TD]
[/TR]
[TR]
[TD]Committee 2[/TD]
[TD]Alex Davies[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Heather Nimmo[/TD]
[/TR]
[TR]
[TD]Committee 3[/TD]
[TD]Tom Munro[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jake McDonald[/TD]
[/TR]
[TR]
[TD]Committee 4[/TD]
[TD]David Clark[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane Brown[/TD]
[/TR]
</tbody>[/TABLE]


If I filter column B on Jane Brown to see how many Committee's she is on the result is this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane Brown[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane Brown[/TD]
[/TR]
</tbody>[/TABLE]

I want column A to show that she is on Committee 1 and 4. I don't want to duplicate the Committee's all the way down column A. Any help would be much appreciated!

 

[Blochand]

Re: Filter Help!

Unfortunately, that is not how filtering works

If you don't want to change the setup in column A, then the only solution I know of is to have a 'helper formula' in column C:

=IF(A2="",C1,A2)


But not sure if that's the fix you want :/


//Blochand

 

[inactiveuserps07]

Re: Filter Help!

@ScottishLass, I'd recommend setting up your data differently and then using conditional formatting. Assuming four committees in this example:

1. List your committee names as Column headers in A1, B1, C1, D1.
2. List your committee member names under each of those headings.
3. In F1, type the label "Search:" or whatever else makes sense to you, and then use G1 as a search field for the name you want to search within all committees.
4. Select all headers in A1:D1 so that they are highlighted.
5. From the Home tab, click Conditional Formatting. Choose "New Rule" from the dropdown menu, and then choose "Use a formula to determine which cells to format."
6. In the field under "Format cells where this formula is true," enter the following formula:

=AND($G$1<>"",ISNUMBER(MATCH("*"&TRIM($G$1)&"*",A1:A50,0)))

7. Click the "Format..." button. Click to open the "Fill" tab. Choose a highlight color. Click "OK." The Format window will close.
8. Click "OK" again in the Conditional Formatting window to apply the rule.

Now, when you enter a name or partial name in G1 and hit Enter, all column headers that have that name listed under them will highlight.

To clear the highlighting, just delete the contents of G1, or change the name to immediately see the new highlighting for that name.

If you enter a name that is not yet listed under any committee header, nothing will highlight.

 

[RoryA]

Welcome to the forum.

You can fill in the blanks in column A by selecting the column, pressing f5, then click the 'Special...' button and choose Blanks and press OK. Then type = and press the up arrow key and then Ctrl+Enter. Your filter should now work as you want it.