excel how to display table with list of members

[bbsin]

hi I have a list of names, with it's table number

may I know how to display them into the table number with the list of people in?

Thank you

As attached excel

[#2]

HIi @bbsin 

With Excel 2021 or 365 you case use the FILTER function - see attachment

[#3]

hi @L z.

Great . Thank you

[#4]

Hi @L z. 

my end cannot work . it show SPILL

do advise thx

[#5]

List of people by table
Table	1
#SPILL!
redundancy data

You need to make sure there is no other data occupied in the array space. 

@bbsin 

[#6]

@bbsin 

See How to correct a #SPILL! error. 

[#7]

Thank you. Will try it out.

Cheers,

[#8]

hi

I have a question, may I know how display name that are "Award" from a range table range

to show which table and name that are = Awards in a table to display
the names and it's table number

thanks

[#9]

Not sure I understand. Could you supply a sample with dummy data + the expected result? Thanks

[#10]

Hi 

@L z. 

Example I like to show the Award 1 and Award 2
in the table with guest name and it's table no that match to Award 1 and so for.

as attached

Thank you

[#11]

Hi @bbsin 

Your sample was confusing as some Gest Names had "Awardx". Highlighted in orange in the attached file is what I modified with the hope this reflects your actuals

in F5:

=LET(
    flt, FILTER(A3:C26, D3:D26=F3),
    INDEX(flt, SEQUENCE(ROWS(flt)), {3,1})
)

(If it was me I would format your List as a Table - see some benefits here )

[#12]

@bbsin 

please check this if I got your point. Cause like L z. said it really confused me for a long time. 

It's pretty easy with the FILTER function. 

=INDEX(FILTER($A$3:$C$26,$C$3:$C$26=F3),SEQUENCE(ROWS(FILTER($A$3:$C$26, $C$3:$C$26=F3))),{3,1})

I strongly recommend you to use Excel Table to structurize data, this way it could automatically resize the data range if you add. Plus, the function would be more understandable.

=INDEX(FILTER(List[[Table '#]:[Guest Name]],List[Guest Name]=F3),SEQUENCE(ROWS(FILTER(List[[Table '#]:[Guest Name]],List[Guest Name]=F3))),{3,1})

[#13]

LET function is way better than mine!

[#14]

Thank you for your help.

can I also check I have another list of names with team member in rows

with Data and I like to display in the list tab

The team members and it's department

Can advise how to use formula to pull from the data as attached file?  Thank you

[#15]

Thank you for your help @PowerDekor

can I also check I have another list of names with team member in rows

with Data and I like to display in the list tab

The team members and it's department

Can advise how to use formula to pull from the data as attached file?  Thank you

[#16]

@PowerDekor 

I user the fitler and the table display automatically

is there any way not to show the SPILL and 

the arrangement was auto pick,  they empty cells are showing follow by names in the guest column, how can i not sure the empty cell in the display table with the formula?  Thank yoyu

[#17 · Accepted solution]

@bbsin 

There is no need to manually set every filter function. Since there are relative references to table number, you could easily complete every table sheet by just copy and paste it.

And you don't have to reference one column in every filter function. Below should be better.

=FILTER(List[[Guest Name]:[Catoegry]],List[Table '#]=I9)

 

[#18]

thank you. this works.