SOLVED

excel how to display table with list of members

Brass Contributor

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

17 Replies

HIi @bbsin 

 

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

hi @L z.

Great . Thank you

Hi @L z. 

 

my end cannot work . it show SPILL

bbsin_0-1664861036879.png

 

do advise thx

 

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 

Thank you. Will try it out.

Cheers,
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
Not sure I understand. Could you supply a sample with dummy data + the expected result? Thanks

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

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

 

_Screenshot.png

 

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 )

@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.

屏幕截图 2022-10-05 192813.png

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

 

LET function is way better than mine!

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

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

@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

bbsin_0-1665058073084.png

 

best response confirmed by bbsin (Brass Contributor)
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)

 Media1 00_00_00-00_00_30.gif

thank you. this works.
1 best response

Accepted Solutions
best response confirmed by bbsin (Brass Contributor)
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)

 Media1 00_00_00-00_00_30.gif

View solution in original post