How to filter a table which has data on one row but then more data related to that row on multiplero

Occasional Visitor

Hi please can someone help me to apply a filter on below table which I created as an example. I have data in first 9 columns which will always stay the same, but then I have more data to the right of this data which varies on multiple rows.  I basically want to filter on the sponsors name and it show me all the tenants, names, gender and DOB's as well but I can only see the first row when I do the filter.

 

 

Sponsor statusSponsor nameSponsor contact no.AddressPost CodeSponsor emailSponsor GenderSponsor DOBSponsor Age Guest NameGuest GenderGuest DOB
ActiveJoe BloggsxxxxxxxxxxxxxxLondon HousexxxxxxxxxxxxxxxMalexxxxxx30Tenant 1Malexxxxxx
         Tenant 2Femalexxxxxx
ActiveMary JoxxxxxxxxxxxxxxNelson WayxxxxxxxxxxxxxxxMalexxxxxx47Tenant 1 nameFemalexxxxxx
         Tenant 2 nameMalexxxxxx
         Tenant 3 nameFemalexxxxxx
WithdrawnShaida DxxxxxxxxxxxxxxIlford xxxxxxxxxxxxxxxMalexxxxxx56Tenant 1 nameFemalexxxxxx
         Tenant 2 nameFemalexxxxxx
         Tenant 3 nameMalexxxxxx
         Tenant 4 nameFemalexxxxxx
1 Reply
The first nine columns are not the same. Don't leave blank fields. The sponsor's name belongs on every row. It may be easier for you, as a human to read the table this way, but it's not a good Excel Table when filled in like this. You need to use Excel properly in order for it to behave properly when doing such things as filtering.