Formula Required to Group Family Members, where ID matches

[WednesdayC]

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Georgia, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-family: Calibri, sans-serif; }.xl64 { color: black; font-family: Verdana; text-align: right; border: 0.5pt solid rgb(170, 170, 170); }.xl65 { color: black; font-family: Verdana; text-align: left; border: 0.5pt solid rgb(170, 170, 170); }.xl66 { font-weight: 700; font-family: Calibri, sans-serif; }.xl67 { font-weight: 700; font-family: Calibri, sans-serif; text-align: center; }.xl68 { color: black; font-weight: 700; font-family: Verdana; text-align: left; border: 0.5pt solid rgb(170, 170, 170); }</style>Hi All

Can anyone help me please? I am trying to group family members, as follows:-

[TABLE="width: 477"]
<colgroup><col style="width:75pt" width="75" span="4"> <col style="mso-width-source:userset;mso-width-alt:6473;width:177pt" width="177"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 75"]ID[/TD]
[TD="class: xl65, width: 75"]LAST NAME[/TD]
[TD="class: xl65, width: 75"]FIRST NAME[/TD]
[TD="class: xl66, width: 75"]Status[/TD]
[TD="class: xl67, width: 177"]RESULTS REQUIRED[/TD]
[/TR]
[TR]
[TD="class: xl68"]84[/TD]
[TD="class: xl69"]Doe[/TD]
[TD="class: xl69"]Joe[/TD]
[TD="class: xl69"]Employee[/TD]
[TD="class: xl70"]EMPLOYEE[/TD]
[/TR]
[TR]
[TD="class: xl68"]88[/TD]
[TD="class: xl69"]Smith[/TD]
[TD="class: xl69"]Alan[/TD]
[TD="class: xl69"]Employee[/TD]
[TD="class: xl70"]EMPLOYEE[/TD]
[/TR]
[TR]
[TD="class: xl68"]192[/TD]
[TD="class: xl69"]Jones[/TD]
[TD="class: xl69"]Jack[/TD]
[TD="class: xl69"]Employee[/TD]
[TD="class: xl70"]EMPLOYEE, SPOUSE + 3 CHILDREN[/TD]
[/TR]
[TR]
[TD="class: xl68"]192[/TD]
[TD="class: xl69"]Jones[/TD]
[TD="class: xl69"]Emma[/TD]
[TD="class: xl69"]spouse[/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]192[/TD]
[TD="class: xl69"]Jones[/TD]
[TD="class: xl69"]John[/TD]
[TD="class: xl69"]child[/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]192[/TD]
[TD="class: xl69"]Jones[/TD]
[TD="class: xl69"]Jane[/TD]
[TD="class: xl69"]child[/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]192[/TD]
[TD="class: xl69"]Jones[/TD]
[TD="class: xl69"]Jessica[/TD]
[TD="class: xl69"]child[/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]195[/TD]
[TD="class: xl69"]Davies[/TD]
[TD="class: xl69"]Mark[/TD]
[TD="class: xl69"]Employee[/TD]
[TD="class: xl70"]EMPLOYEE[/TD]
[/TR]
[TR]
[TD="class: xl68"]197[/TD]
[TD="class: xl69"]Lewis[/TD]
[TD="class: xl69"]Evan[/TD]
[TD="class: xl69"]Employee[/TD]
[TD="class: xl70"]EMPLOYEE, SPOUSE + 1 CHILD[/TD]
[/TR]
[TR]
[TD="class: xl68"]197[/TD]
[TD="class: xl69"]Lewis[/TD]
[TD="class: xl69"]Maria[/TD]
[TD="class: xl69"]spouse[/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]197[/TD]
[TD="class: xl69"]Lewis[/TD]
[TD="class: xl69"]Victor[/TD]
[TD="class: xl69"]child[/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl68"]199[/TD]
[TD="class: xl69"]Clark[/TD]
[TD="class: xl69"]Kevin[/TD]
[TD="class: xl69"]Employee[/TD]
[TD="class: xl70"]EMPLOYEE[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Georgia, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-family: Calibri, sans-serif; }.xl64 { font-weight: 700; font-family: Calibri, sans-serif; }.xl65 { font-weight: 700; font-family: Calibri, sans-serif; text-align: center; border: 0.5pt solid windowtext; }.xl66 { color: black; font-weight: 700; font-family: Verdana; text-align: left; border: 0.5pt solid windowtext; }.xl67 { font-weight: 700; font-family: Calibri, sans-serif; border: 0.5pt solid windowtext; }.xl68 { color: black; font-family: Verdana; text-align: right; border: 0.5pt solid windowtext; }.xl69 { color: black; font-family: Verdana; text-align: left; border: 0.5pt solid windowtext; }.xl70 { font-family: Calibri, sans-serif; border: 0.5pt solid windowtext; }</style>

Is it possible to do this with a regular formula, or would I require a UDF please?

I am not sure how to tackle this please, so would be very grateful for your expert guidance.

Many thanks in advance

Wednesday
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Georgia, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-family: Calibri, sans-serif; }.xl64 { color: black; font-family: Verdana; text-align: right; border: 0.5pt solid rgb(170, 170, 170); }.xl65 { color: black; font-family: Verdana; text-align: left; border: 0.5pt solid rgb(170, 170, 170); }.xl66 { font-weight: 700; font-family: Calibri, sans-serif; }.xl67 { font-weight: 700; font-family: Calibri, sans-serif; text-align: center; }.xl68 { color: black; font-weight: 700; font-family: Verdana; text-align: left; border: 0.5pt solid rgb(170, 170, 170); }</style>

 

[jtakw]

Hi,

If you're still interested in a Formula solution (last sample - employee #200 - has no Spouse & 1 Child):

Book1
	A	B	C	D	E
1	ID	LAST NAME	FIRST NAME	Status	RESULTS REQUIRED
2	84	Doe	Joe	Employee	EMPLOYEE
3	88	Smith	Alan	Employee	EMPLOYEE
4	192	Jones	Jack	Employee	EMPLOYEE, SPOUSE + 3 CHILDREN
5	192	Jones	Emma	spouse
6	192	Jones	John	child
7	192	Jones	Jane	child
8	192	Jones	Jessica	child
9	195	Davies	Mark	Employee	EMPLOYEE
10	197	Lewis	Evan	Employee	EMPLOYEE, SPOUSE + 1 CHILD
11	197	Lewis	Maria	spouse
12	197	Lewis	Victor	child
13	199	Clark	Kevin	Employee	EMPLOYEE
14	200	Bear	Papa	Employee	EMPLOYEE + 1 CHILD
15	200	Bear	Baby	child
Sheet393

Cell Formulas
Range	Formula
E2	=IF(COUNTIF(A$2:A2,A2)=1,SUBSTITUTE("EMPLOYEE"&IF(COUNTIFS(A$2:A$15,A2,D$2:D$15,"spouse"),", SPOUSE","")&IF(COUNTIFS(A$2:A$15,A2,D$2:D$15,"child")," + "&COUNTIFS(A$2:A$15,A2,D$2:D$15,"child")&" CHILDREN",""),"1 CHILDREN","1 CHILD"),"")

Change/adjust cell references/range as needed, formula copied down.

 

[jtakw]

@ Fluff,

I would think to adjust your UDF for cases of Single Parents...

 

[WednesdayC]

Wow Jtakw

This is fantastic! Thank you so much.
You spotted that I forgot to account for cases where there are children and no spouses, and your formula even takes care of that scenario. I did adapt Fluff's UDF to account for that, but this formula is less volatile, so I will be using this solution instead.

How on earth you came up with it is beyond me. Huge thanks.

Regards
Wednesday

 

[jtakw]

You're welcome, glad it works for you.