VBA Lookup to Find/Display All Members in a Team

[Necroscope]

Hi.

I have a table containing staff member names (column A), team they belong to, which is numbered between 1 to 30 (column B) and contact info / location info in columns C and D. The whole range is A4:D434.

What I'd like to do is enter a name in A1 and then display a list of the other staff members who belong to the same team. I'll display this in a Userform.

So, for example, if A1 contains "Joe Bloggs" and Joe is in team 3, I want to display a pop up (Userform) that shows everyone else in team 3, along with their contact info/location:

I.e. it might look like this:

Team 3 members -

Joe Bloggs	01234 567890	London
John Smith	09876 543210	Edinburgh
Jane Doe	07070 135790	New York

Any help with the VBA would be appreciated...

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[Necroscope]

Hi Alan.

Please accept my apologies for the oversight; I meant no disrespect. My intention was only to try and increase the number of people who might be able to help. However, after reading the "cross posting" article, I see now how this could be construed as discourteous and will ensure that I do not make this mistake again.

However, I have attempted to post a link but I just get an error saying that I am "...not allowed to post any kinds of links, images or videos..." until I've posted a few times...

This is the link I was attempting to provide (minus the www bit): "mrexcel.com/forum/excel-questions/1099259-finding-listing-all-members-team.html#post5285176"

Kind regards.

[bakerman2]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Necroscope]

Hi bakerman2.

I've copy/pasted some details in to a new workbook to give a rough idea of what I was looking to achieve.

Thanks for looking at this.

[bakerman2]

For starters.

Advanced Filter method.

Please Login or Register  to view this content.

[bakerman2]

Other method. Filter in ListBox.

Please Login or Register  to view this content.

[Necroscope]

Hi bakerman2.

Both solutions work perfectly but I like the 2nd one as it's easier for me to understand. I have now added this to the main workbook and it works great. Thank you so much for taking the time to help with this.

[bakerman2]

You're welcome and thanks for rep+.

PS:
2nd method (ListBox Filter) is OK to use as long as your data doesn't exceed about 3K rows. After that it might start to lag and it's better to use 1st method (Advanced Filter)

[alansidman]

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

[Necroscope]

Hi bakerman2.

The list will not exceed 420-ish rows so the 2nd method is fine.