question on filter function

[avengine]

I have a sheet like this

a b c d e
1 group name tom
2 group total 2
3 attend 1
4 absent 1
5 % attend 50
6 id first last group attend
7 1 allan leung tom 1
8 2 amy lo tom 0
9 3 carol tam andy 1
10 4 karen kwok andy 1
11 5 eric li andy 0

Question 1:
I use filter command on the first, last and group on line 6
then I filter tom group, I will get 1 attend and 1 absent, but I want to make tom name be on E1, when I filter tom, and I will get andy name on E1 if I filter andy. How to make this work?

Question 2,
you know all I want is to filter the group name only, but I cannot just filter D6 only, I need to have b6:d6, why is that, and I will not filter first and last name anyway.
please help
thanks.

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[newdoverman]

I took a guess and I extended the data range a bit so that you have a couple of rows for experimentation. For your real data, make the ranges large enough to cover your data...D7:D15 could be D7:D200 or whatever your data requires.
Enter this in B2

Please Login or Register  to view this content.

Enter this in B3

Please Login or Register  to view this content.

Enter this in B4

Please Login or Register  to view this content.

Enter this in B5 and format as %

Please Login or Register  to view this content.

[newdoverman]

Here's the same solution but using a table that will expand as data is added so that you don't have to adjust the formulae in order to use it.
I moved the table down 1 row
Enter this in B2

Please Login or Register  to view this content.

Enter this in B3

Please Login or Register  to view this content.

The differences in the formulae from the first message is the use of Table nomenclature to define the ranges.