Filter

ScottishLass

New Member
Joined
Oct 17, 2017
Messages
1
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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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:
Code:
=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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top