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:-

ID	LAST NAME	FIRST NAME	Status	RESULTS REQUIRED
84	Doe	Joe	Employee	EMPLOYEE
88	Smith	Alan	Employee	EMPLOYEE
192	Jones	Jack	Employee	EMPLOYEE, SPOUSE + 3 CHILDREN
192	Jones	Emma	spouse
192	Jones	John	child
192	Jones	Jane	child
192	Jones	Jessica	child
195	Davies	Mark	Employee	EMPLOYEE
197	Lewis	Evan	Employee	EMPLOYEE, SPOUSE + 1 CHILD
197	Lewis	Maria	spouse
197	Lewis	Victor	child
199	Clark	Kevin	Employee	EMPLOYEE

<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>
</tbody>

<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.