Family members grouping by eldest

Hello Everyone,
I'm looking for some help here.
I have list of family members on my spreadsheet that have been identified by their family and individual id's. Also i have their first name, surname and age's. I need help in getting the below result:
- In column F i'd like to get the result family name in there for ex: Man family. This needs be done by first finding the oldest age from each family groups and then using their surname of the eldest member and adding "family" after the surname.

I've attached the spreadsheet to have a look at.

I look forward to your solutions

Thank you

MAK

Hi Jeff,
Thank you so much for this, but i was looking to avoid doing via pivot. Any chance this can be done by formula's.

Regards
Mak

Hi Jeff,
The results to be shown the same way you did using Pivot, enclosed is the sheet with results. I was hoping to find a way through formula's where after finding the largest age within a family group it would use that family members surname and add "family" in the results for each member of that family.

Regards
MAK

Hi Jeff,
so sorry, my mistake i didn't clarify my query properly and neither did my spreadsheet. There will be family members with different surnames (some having their father's name and some mother's name as a surname) within a family. I've been asked to bring the eldest family members surname as the family name for the whole family.
for example:
A crazy 10yrs
B cram 12yrs
F crazy 34yrs
K cram 32yrs
Since the eldest member is F crazy 34yrs the result should be
A crazy 10yrs Crazy family
B cram 12yrs Crazy family
F crazy 34yrs Crazy family
K cram 32yrs Crazy family

hope this is more clear now.
My apologies again

Regards
MAK

Hi Jeff,
as requested

regards

MAK

How about
=INDEX($D$2:$D$13,AGGREGATE(15,6,(ROW($D$2:$D$13)-ROW($D$2)+1)/($E$2:$E$13=MAX(IF($A$2:$A$13=A2,$E$2:$E$13))),1))&" Family"

expected results

Great Thanks for this, but when i copied it to the remaining cells its giving me same surname+family for every single person on the sheet. am i doing something wrong here

Originally Posted by Fluff13

How about
=INDEX($D$2:$D$13,AGGREGATE(15,6,(ROW($D$2:$D$13)-ROW($D$2)+1)/($E$2:$E$13=MAX(IF($A$2:$A$13=A2,$E$2:$E$13))),1))&" Family"

ok, ok, my bad...it works...me dumbo needed to copy it as an array ....DUH!!!

You're welcome & thanks for the feedback.

Originally Posted by Fluff13

You're welcome & thanks for the feedback.

Just before you go, a small duplicate error has happened, i've enclosed the results where this has happened highlighted in yellow.

Oops, forgot about the possibility of matching ages, but Jeff has sorted that.

Originally Posted by jeffreybrown

try this one adjustment

=index($d$2:$d$11,aggregate(15,6,(row($d$2:$d$11)-row($d$2)+1)/(($a$2:$a$11=a2)*($e$2:$e$11=max(if($a$2:$a$11=a2,$e$2:$e$11)))),1))&" family"

"great it works all fine now, appreciate the help!"

Glad we could help & thanks for the feedback