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

[Shaida555]

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 status	Sponsor name	Sponsor contact no.	Address	Post Code	Sponsor email	Sponsor Gender	Sponsor DOB	Sponsor Age	Guest Name	Guest Gender	Guest DOB
Active	Joe Bloggs	xxxxxxxxxxxxxx	London House	xxxxxxx	xxxxxxxx	Male	xxxxxx	30	Tenant 1	Male	xxxxxx
									Tenant 2	Female	xxxxxx
Active	Mary Jo	xxxxxxxxxxxxxx	Nelson Way	xxxxxxx	xxxxxxxx	Male	xxxxxx	47	Tenant 1 name	Female	xxxxxx
									Tenant 2 name	Male	xxxxxx
									Tenant 3 name	Female	xxxxxx
Withdrawn	Shaida D	xxxxxxxxxxxxxx	Ilford	xxxxxxx	xxxxxxxx	Male	xxxxxx	56	Tenant 1 name	Female	xxxxxx
									Tenant 2 name	Female	xxxxxx
									Tenant 3 name	Male	xxxxxx
									Tenant 4 name	Female	xxxxxx

[#2]

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.