Lookup over a range and return 2 columns

[jl2509]

Hi All,

Another conundrum which I need help with if possible.

The image below shows a list of departments and the number of purchase orders they have open, although the list is greater than the 3 sample departments and this is just a sample of the data.
On a separate sheet "Dashboard" I have a drop down box in Cell M6, with the department names listed.

By selecting the drop down box and lets say "Mechanical Dept" is selected, then the names and # POs > 0 will be added below the drop down box from cell M9 as shown in the second image?

Many thanks

 

[Jeffrey Mahoney]

You should combine all those department tables. Add another column called department in that single table. Here is a simple answer

Book4
	A	B	C	D	E	F	G	H	I	J
1	Department	Name	POs			Dept List		Choose Department
2	Art	Hailee Prue	0			Art		Mechanical
3	Art	Veronica Bertina	0			Mechanical
4	Art	Trixie Terrance	0			Physics
5	Mechanical	Henry Karl	1					Department	Name	POs
6	Mechanical	Prince Mary Lou	1					Mechanical	Henry Karl	1
7	Mechanical	Stacey Briggs	0					Mechanical	Prince Mary Lou	1
8	Physics	Wrenley Rue	1							0
9	Physics	Izaiah Tiana	1							0
10	Physics	Bette Blaire	1							0
11	Physics	Tabitha Cash	0							0
12										0
13										0
14										0
15										0
16										0
17										0
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=SORT(UNIQUE(A2:A11))
H6:I7	H6	=FILTER(A2:B11,(A2:A11=H2)*(C2:C11<>0),"")
J6:J17	J6	=SUMIFS($C$2:$C$11,$A$2:$A$11,$H$2,$B$2:$B$11,I6)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=F2#

 

[jl2509]

Thanks for the response Jeffrey
Unfortunately, I cannot put them all in one column as they are imported from SQL data from separate external sheets by department

If this is not possible how I initially proposed, I guess I will just need to look at alternative methods but its not my preference.

 

[Joe4]

Thanks for the response Jeffrey
Unfortunately, I cannot put them all in one column as they are imported from SQL data from separate external sheets by department

If this is not possible how I initially proposed, I guess I will just need to look at alternative methods but its not my preference.

You could use the new FILTER function to get what you want. The only issue is that you do not know which of the various ranges to apply it to. So it would work fine if creating that formula manually each time, but I don't know if you are going to be able to create one dynamic formula that would work for any instance.

I do not know if you are going to be able to do what you want without using VBA. You can only do so much with poorly formatted/structured data, without having to manually interact with it first.
If you decide to go the VBA route, I would probably recommend have VBA do what Jeff proposed, and copy all your data into one long list. And then you can use the formulas he proposed.

 

[Peter_SSs]

It will get you better/faster responses in the future if you can provide your sample data in a form that can be easily copied for testing, rather than an image. I suggest that you investigate XL2BB

Try something like this. Adjust ranges if required.

jl2509.xlsm
	B	C	D	E	F	G	H	I
1
2
3
4	Art Dept			Mechanical Dept			Physics Dept
5
6
7	Po's	Name		Po's	Name		Po's	Name
8	0	a		1	f		1	p
9	0	b		1	g		1	q
10	0	c		0	h		1	r
11	1	d		1	i		0	s
12	1	e		0	j		0	t
13				1	k		0	u
14				1	l		1	v
15				1	m		0	w
16				0	n
17				1	o
18
Sheet1

jl2509.xlsm
	M	N
6	Mechanical Dept
7
8	Open Pos	Name
9	1	f
10	1	g
11	1	i
12	1	k
13	1	l
14	1	m
15	1	o
16
17
Dashboard

Cell Formulas
Range	Formula
M9:N15	M9	=LET(c,MATCH(M6,Sheet1!B4:X4,0),dpt,CHOOSECOLS(Sheet1!B8:X100,c,c+1),FILTER(dpt,TAKE(dpt,,1)=1,""))
Dynamic array formulas.

 

[Joe4]

I stand corrected! It looks like Peter may have managed to come up with a whopper of a formula that will seem to work on your data structure!

 

[jl2509]

Thanks for the feedback Peter. Works a treat
BTW, I cannot install XL2BB due to hardware restrictions

 

[Joe4]

Note: When marking a post as the solution, please mark the original post actually containing the solution, and not your own post acknowledging that some other post is the solution.
I have updated this thread for you.

 

[Peter_SSs]

Thanks for the feedback Peter. Works a treat

You're welcome. Thanks for the follow-up.

BTW, I cannot install XL2BB due to hardware restrictions

In future, I suggest that you mention this in the first post so that we don't keep asking.

Provided the data is not too big, instead you can simply copy/paste from your worksheet like below and tell us what the range is (mine is B4:I17). It isn't quite as good as XL2BB but at least we can copy from that and paste into our worksheets for testing, rather than having to manually type it all out.

Art Dept			Mechanical Dept			Physics Dept
Po's	Name		Po's	Name		Po's	Name
0	a		1	f		1	p
0	b		1	g		1	q
0	c		0	h		1	r
1	d		1	i		0	s
1	e		0	j		0	t
			1	k		0	u
			1	l		1	v
			1	m		0	w
			0	n
			1	o

 

[jl2509]

Hello Jeffrey

Looking at some of the advice given, I am trying to implement your proposal but the example you have given does not capture all of the data required from the example data?