Extract/Filter Names From A List AND Filter Out Blank Cells

[Necroscope]

I have a table with a list of staff names (about 400 rows) and a column with the team they belong to (1-6). I then have 31 columns, one for each day of the month and text entered in some of those cells. Not every cell is populated though.

What I'm looking to do, in another sheet, is...
a) create a new table with a list of just Team 1 staff and
b) only show their entries for whichever day(s) they have info in those cells (and not include all the blank cells)

I.e. if someone only had data in the 2 Jun and 7 Jun columns I'd want the table to show their name and then the 2 Jun / 7 Jun details and nothing else.

I have attached a small example of this. I tried to use FILTER with Index/Match and Vlookup to search for their name and then return the relevant results but haven't got this to work... Can anyone help with this?

[Fluff13]

How about in E5 dragged down

Please Login or Register  to view this content.

[AliGW]

This maybe?

=SORT(FILTER('Full List'!$B$5:$L$15,('Full List'!$C$5:$C$15=1)))

Or this:

=LET(s,SORT(FILTER('Full List'!$B$5:$L$15,('Full List'!$C$5:$C$15=1))),IF(s=0,"",s))

[Necroscope]

Hi Fluff13.

This formula does the job perfectly, thanks!

[Necroscope]

Hi AliGW.

Both those formulas do the job of pulling the names for team 1 but also all of the data - it doesn't filter out the blanks though. However, the formula from Fluff13 filters out the blanks so I can use that one. Thanks for taking the time to reply.

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Fluff13]

Glad to help & thanks for the feedback.