How to get specific cell contents of an Excel sheet at another sheet dynamically?

Question

I have an Excel file including thousands rows as follow. First column includes names and second column include group of each. I want to have all names belong to group "A" at another sheet dynamically. because name and group list may be changes. In other words, what command or function I should use to list all names belong to group "A"?

Answer 1

There are 3 ways to do this. The options are below. One thing you did not include in your question is what the results should look like.

1. Equations like =FILTER(A:B,B:B="A")
2. Pivot tables to use this convert data to table, then create pivot table. This requires refresh when new data is added.
3. Power query to use this convert data to table, then go to Data>From Table/Range. This requires refresh when data is added but you can change the "connection properties" under Data>Refresh All?.

Now if you want all in group "A" to be listed with in one cell, then I would use option 3 with groupby as talk about here.

If the answer works for you the expectation is that you checkmark it and upvote it. If the answer does not work for you add a comment at the bottom and the problem you experience. For your situation you will need to adapt the answer.