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>

 

[sandy666]

maybe something like this with PowerQuery?

ID	LAST NAME	FIRST NAME	Status		ID	LAST NAME	FIRST NAME	Status
84​	Doe	Joe	Employee		84​	Doe	Joe	Employee
88​	Smith	Alan	Employee		88​	Smith	Alan	Employee
192​	Jones	Jack	Employee		192​	Jones	Jack, Emma, John, Jane, Jessica	Employee, spouse, child, child, child
192​	Jones	Emma	spouse		195​	Davies	Mark	Employee
192​	Jones	John	child		197​	Lewis	Evan, Maria, Victor	Employee, spouse, child
192​	Jones	Jane	child		199​	Clark	Kevin	Employee
192​	Jones	Jessica	child
195​	Davies	Mark	Employee
197​	Lewis	Evan	Employee
197​	Lewis	Maria	spouse
197​	Lewis	Victor	child
199​	Clark	Kevin	Employee

 

[WednesdayC]

Hi Sandy

Thank you. I have never heard of Power Query before. Does it work for Excel 2011 for Mac please?

Regards

Wednesday

 

[sandy666]

Ops, Mac doesn't support PowerQuery, sorry

 

[WednesdayC]

Hi Sandy

Thanks anyway. Is it not possible with a regular formula?

Regards

Wednesday

 

[sandy666]

I don't know Maybe someone else will give a solution with formula

Have a nice day

 

[WednesdayC]

Hi Sandy

You too.

Regards

Wednesday

 

[Fluff]

Based on your data how about, a UDF

Function ListStatus(Cl As Range, Rng As Range) As String
   Dim i As Long
   If Not Cl.Offset(, 3) = "Employee" Then Exit Function
   ListStatus = Cl.Offset(, 3).Value
   i = Application.CountIf(Rng, Cl)
   Select Case i
      Case 2
         ListStatus = ListStatus & ", Spouse"
      Case 3
         ListStatus = ListStatus & ", Spouse + 1 Child"
      Case Is > 3
         ListStatus = ListStatus & ", Spouse + " & i - 2 & " Children"
   End Select
End Function

Used
=ListStatus(A2,A:A)

 

[WednesdayC]

Dear Fluff

You are a star!

Thank you so much. It works a treat.

A very grateful

Wednesday

 

[Fluff]

You're welcome & thanks for the feedback