Extract list of people

[ashani]

Hi,

I'm looking to extract list of staff and filter it to the higher sales volume.

In column C I have around 1000 staff name and D their team names

In Column A1 I have a team name so when I enter Team name, it should bring all the staff names down. I have used Unique Filter formula but it doesn't allow me to filter staff by number, so any other formula will be really helpful.

Many thanks,

 

[psgoldberg]

Your problem is unclear (to me). Put a snapshot of your file with sample data and your attempt at a formula via xL2BB and it is more likely to get a response.

 

[Fluff]

Can you posted some sampled data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[alansidman]

MOCK_DATA.xlsx
	A	B	C
1	Lookup	first_name	Department
2	Marketing	Valenka	Accounting
3		Hadley	Services
4		Chase	Research and Development
5		Trevar	Legal
6		Gussie	Marketing
7		Edik	Marketing
8		Kin	Support
9		Gale	Support
10		Dirk	Marketing
11		Iolande	Accounting
12		Amelina	Training
13		Sonja	Research and Development
14		Mose	Business Development
15		Clo	Sales
16		Brear	Support
17		Ruben	Legal
18		Holden	Training
19		Stanislas	Engineering
20		Peggi	Sales
21		Willey	Services
22		August	Research and Development
23		Karole	Business Development
24		Dorette	Sales
25		Callie	Support
26		Patti	Support
27		Janel	Human Resources
28		Sella	Sales
29		Hadria	Human Resources
30		Krystyna	Research and Development
31		Elayne	Training
32		Evelina	Human Resources
33		Thea	Accounting
34		Nolana	Training
35		Barclay	Product Management
36		Barris	Legal
37		Carroll	Product Management
38		Josy	Services
39		Allin	Support
40		Aryn	Support
41		Adler	Legal
42		Kip	Product Management
43		Trudy	Research and Development
44		Janet	Human Resources
45		Cari	Research and Development
46		Aguie	Sales
47		Cary	Marketing
48		Leonie	Accounting
49		Stephi	Training
50		Selena	Engineering
51		Sharona	Legal
data

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$P$3:$P$14

Load A1:A2 to Power Query. In Power Query Editor, right click on A2 and select Drill Down. Close and Load to connection.

Load your table B:C to Power Query. In PQ Editor, filter for any department. In the M code for the filter line, change the department to Table1 (assumes the first mini table loaded is named Table 1)

Close and Load your Table to a new worksheet. Click on Refresh All with every change in filtering.

first_name	Department
Gussie	Marketing
Edik	Marketing
Dirk	Marketing
Cary	Marketing