Attaching Rows Together

Question

I need a way to attach dissimilar rows together for the sake of filtering them.

My spreadsheet contains data on many different families. Each entry begins with the head of household with columns giving different information about the head and the family as a whole. Following rows contain information about other members of the family.

I originally had it set up so that the family's last name was in every row of the first column of the entry. However, this presented a problem. Some of the families have the same last name and filtering in alphabetical order causes the other household members to mix together. Also, one would not be able to see information regarding  everyone in the family if they filter by information pertaining to the household, as it is only in the first row. Certain aspects prevent me from being able to just put all of the information in every row.

For example (commas indicate column breaks)

Last   , First   , code ,other family, medical, insurance  

Smith, Jane  , 1234 , blank           , 1/1/16  , 1/1/16-1/1/17,

Smith, blank, blank , Joe Man     , 1/1/16   , blank              ,  

What I need is a way to somehow group the rows pertaining to one family together, so that pulling up any specific information would pull up all relevent information.

However, the grouping function does not work. when I expand the group, filtering still only shows the first row, while minimizing the group prevents filtering at all.

Is there any other method I could try? 

Sorry for the long information, but I want to be as specific as I can. I am trying to make this worksheet user friendly and the ability to organize through filters in necessary.

Thank you

Answer 1

Here is a subroutine that may partly answer your needs.

The image shows my input and output (output sent to new sheet)

best wishes

Sub tryme()

  k = 1

  LastRow = Cells(Rows.Count, "A").End(xlUp).Row

  newName = Range("A2")

  newRow = Range("C2") & " " & Range("A2") & " " & Range("B2")

  For j = 3 To LastRow

    If Cells(j, "A") = newName And Cells(j, "B") = "" Then

      newRow = newRow & " " & Cells(j, "D")

    Else

      Worksheets("Sheet3").Cells(k, "A") = newRow

      k = k + 1

      newName = Cells(j, "A")

      newRow = Cells(j, "C") & " " & Cells(j, "A") & " " & Cells(j, "B")

    End If

  Next j

  Worksheets("Sheet3").Cells(k, "A") = newRow

End Sub

Answer 2

Add a Family ID column.