Formula to pull information

[morangsters]

Hi from Canada! I found some very helpful formulas thus far to help simplify a very data-filled admin job. I can not seem to figure out how to get the information that I want presented and hope that someone can help. I am not as fluent in Excel as I wish to be...

I have a list of close to 500 students bron down in 3 columns - Student Id's, names, and campus (there are 4 campus' around Canada). Students can apply for a monthly program between Oct & May. My manager provides this list with a different column for each month and an "Approved" or "Denied" next to each student who applied for that particular month.

I want to create a separate sheet within the workbook that tells me each month, who is on the list, without having to recopy all of the information. I want to have fluidity in the sheet that if the person working in Toronto Campus can pull their information from the same sheet as the one who is working in Vancouver, just by changing the parameter from YYZ to YVR ( I used data validity drop down arrows to change the campus).

For similar spreadsheets, I use Vlookup, but I have to enter the Student ID for the rest of the information to populate. In this scenario, I want to enter the Month and Campus and see all the IDs/Names populated. The challenge I am hit with is the Approved/Denied within the month column.

Any ideas?

Thank you so much in advance!

 

[morangsters]

student ID	Name	Campus	oct	nov	dec	jan
0123	a	yyz	approved	approved	denied	denied
0234	b	yul	approved	approved	apprived	denied
0456	c	yvr	approved	denied	approved	denied
0567	d	yyz	denied	denied	denied	approved
124	e	yyz	approved	denied	approved	approved
125	f	yyz	denied	approved	denied	denied
126	g	yul	approved	approved	denied	approved

<tbody>
</tbody>

 

[sandy666]

post a link to shared excel file with representative source data and expected result. Use GoogleDrive, OneDrive , DropBox or any similar.

does your data look like this?

ID	Name	Campus	Month	Status

 

[morangsters]

https://docs.google.com/spreadsheets/d/1GhVNrl1Bm2k3d11TESzyaUpcd__oBFErTUX3YIAYKP0/edit?usp=sharing

 

[sandy666]

and basic question: what is your excel version? (2010 or higher?)

 

[morangsters]

Excel for office 365 MSO 32-bit ?
It's part of our Sharepoint

 

[sandy666]

enough to say 365
I'll try with PowerQuery (Get&Transform) but it will take a time because of temperature here ,+38C

 

[sandy666]

something like this?

Month	Campus	Status		Student ID	Student name	Campus	months	Month	Status
oct	YUL	Approved		72307	1	YUL	8​	oct	Approved
nov	YUL	Denied		51008	2	YUL	8​	oct	Approved
				63025	3	YUL	8​	oct	Approved
				63135	4	YUL	8​	oct	Approved
				78131	5	YUL	8​	oct	Approved
				10062	6	YUL	8​	oct	Approved
				77997	7	YUL	8​	oct	Approved
				78027	8	YUL	8​	oct	Approved
				32924	9	YUL	8​	oct	Approved
				77594	10	YUL	8​	oct	Approved
				18873	11	YUL	8​	oct	Approved
				92757	12	YUL	8​	oct	Approved
				94545	13	YUL	8​	oct	Approved
				90865	14	YUL	8​	oct	Approved
				77917	15	YUL	8​	oct	Approved
				55432	16	YUL	8​	oct	Approved
				55432	16	YUL	8​	nov	Denied
				3125	17	YUL	8​	oct	Approved
				3125	17	YUL	8​	nov	Denied
				9596	18	YUL	8​	oct	Approved
				9596	18	YUL	8​	nov	Denied
				78039	19	YUL	8​	oct	Approved
				78039	19	YUL	8​	nov	Denied
				77948	20	YUL	8​	oct	Approved
				77948	20	YUL	8​	nov	Denied
				77995	21	YUL	8​	oct	Approved
				77995	21	YUL	8​	nov	Denied
				33793	22	YUL	8​	oct	Approved
				33793	22	YUL	8​	nov	Denied
				21938	23	YUL	8​	oct	Approved
				21938	23	YUL	8​	nov	Denied
				89652	24	YUL	8​	oct	Approved
				89652	24	YUL	8​	nov	Denied
				41153	25	YUL	8​	oct	Approved
				41153	25	YUL	8​	nov	Denied
				77951	26	YUL	8​	oct	Approved
				77951	26	YUL	8​	nov	Denied
				7292	27	YUL	8​	oct	Approved
				7292	27	YUL	8​	nov	Denied
				54525	28	YUL	8​	oct	Approved
				54525	28	YUL	8​	nov	Denied
				21138	29	YUL	8​	oct	Approved
				21138	29	YUL	8​	nov	Denied
				59396	30	YUL	8​	oct	Approved
				59396	30	YUL	8​	nov	Denied
				85847	31	YUL	7​	oct	Approved
				85847	31	YUL	7​	nov	Denied
				4777	32	YUL	8​	oct	Approved
				4777	32	YUL	8​	nov	Denied
				65260	33	YUL	8​	oct	Approved
				65260	33	YUL	8​	nov	Denied
				3011	37	YUL	8​	oct	Approved
				3011	37	YUL	8​	nov	Denied
				32801	38	YUL	8​	oct	Approved
				32801	38	YUL	8​	nov	Denied
				99904	39	YUL	8​	oct	Approved
				99904	39	YUL	8​	nov	Denied
				897	40	YUL	8​	oct	Approved
				897	40	YUL	8​	nov	Denied
				52949	41	YUL	8​	oct	Approved
				52949	41	YUL	8​	nov	Denied
				22169	42	YUL	8​	oct	Approved
				22169	42	YUL	8​	nov	Denied
				31517	43	YUL	8​	oct	Approved
				31517	43	YUL	8​	nov	Denied
				2614	44	YUL	8​	oct	Approved
				2614	44	YUL	8​	nov	Denied
				6671	45	YUL	8​	oct	Approved
				6671	45	YUL	8​	nov	Denied
				22018	46	YUL	8​	oct	Approved
				22018	46	YUL	8​	nov	Denied
				15999	48	YUL	8​	oct	Approved
				15999	48	YUL	8​	nov	Denied
				24453	50	YUL	8​	oct	Approved
				24453	50	YUL	8​	nov	Denied
				31986	51	YUL	8​	oct	Approved
				31986	51	YUL	8​	nov	Denied
				33351	52	YUL	8​	oct	Approved
				33351	52	YUL	8​	nov	Denied
				33462	53	YUL	8​	oct	Approved
				33462	53	YUL	8​	nov	Denied
				33512	54	YUL	8​	oct	Approved
				33512	54	YUL	8​	nov	Denied
				38931	55	YUL	4​	oct	Approved
				16635	56	YUL	8​	oct	Approved
				16635	56	YUL	8​	nov	Denied
				39591	57	YUL	2​	nov	Denied
				43979	58	YUL	8​	oct	Approved
				43979	58	YUL	8​	nov	Denied
				43812	59	YUL	3​	oct	Approved
				43863	60	YUL	8​	oct	Approved
				43863	60	YUL	8​	nov	Denied
				203706	61	YUL	8​	oct	Approved
				203706	61	YUL	8​	nov	Denied
				204357	63	YUL	8​	oct	Approved
				204357	63	YUL	8​	nov	Denied
				211463	64	YUL	8​	oct	Approved
				211463	64	YUL	8​	nov	Denied
				210958	65	YUL	8​	oct	Approved
				210958	65	YUL	8​	nov	Denied
				61028	402	YUL	8​	oct	Approved
				99650	403	YUL	8​	oct	Approved
				99578	404	YUL	8​	oct	Approved
				49228	405	YUL	8​	oct	Approved
				52782	406	YUL	8​	oct	Approved
				53699	407	YUL	8​	oct	Approved
				85511	408	YUL	8​	oct	Approved
				68575	410	YUL	8​	oct	Approved
				75053	411	YUL	8​	oct	Approved
				65983	412	YUL	8​	oct	Approved

 

[sandy666]

or like this:

Student ID	Student name	Campus	months	oct	nov	dec	jan
10062	6	YUL	8​	Approved	Approved	Approved	Approved
15391	415	YUL	2​		Approved
15999	48	YUL	8​	Approved		Approved
16635	56	YUL	8​	Approved		Approved
18873	11	YUL	8​	Approved	Approved	Approved	Approved
203706	61	YUL	8​	Approved		Approved
204357	63	YUL	8​	Approved		Approved
210958	65	YUL	8​	Approved		Approved
21138	29	YUL	8​	Approved		Approved
211463	64	YUL	8​	Approved		Approved
21938	23	YUL	8​	Approved		Approved
22018	46	YUL	8​	Approved		Approved
22169	42	YUL	8​	Approved		Approved
22405	413	YUL	8​		Approved
24453	50	YUL	8​	Approved		Approved
25238	36	YUL	2​			Approved
2614	44	YUL	8​	Approved		Approved
3011	37	YUL	8​	Approved		Approved
3125	17	YUL	8​	Approved		Approved
31517	43	YUL	8​	Approved		Approved
31986	51	YUL	8​	Approved		Approved
32801	38	YUL	8​	Approved		Approved
32924	9	YUL	8​	Approved	Approved	Approved	Approved
33351	52	YUL	8​	Approved		Approved
33462	53	YUL	8​	Approved		Approved
33512	54	YUL	8​	Approved		Approved
33537	417	YUL	8​		Approved
33793	22	YUL	8​	Approved		Approved
38931	55	YUL	4​	Approved
41153	25	YUL	8​	Approved		Approved
43812	59	YUL	3​	Approved
43863	60	YUL	8​	Approved		Approved
43979	58	YUL	8​	Approved		Approved
45297	416	YUL	8​		Approved
4777	32	YUL	8​	Approved		Approved
49228	405	YUL	8​	Approved	Approved	Approved	Approved
51008	2	YUL	8​	Approved	Approved	Approved	Approved
52782	406	YUL	8​	Approved	Approved	Approved	Approved
52949	41	YUL	8​	Approved		Approved
53699	407	YUL	8​	Approved	Approved	Approved	Approved
54525	28	YUL	8​	Approved		Approved
55432	16	YUL	8​	Approved		Approved
59396	30	YUL	8​	Approved		Approved
61028	402	YUL	8​	Approved	Approved	Approved	Approved
63025	3	YUL	8​	Approved	Approved	Approved	Approved
63135	4	YUL	8​	Approved	Approved	Approved	Approved
65260	33	YUL	8​	Approved		Approved
65983	412	YUL	8​	Approved	Approved	Approved	Approved
6671	45	YUL	8​	Approved		Approved
68575	410	YUL	8​	Approved	Approved	Approved	Approved
72307	1	YUL	8​	Approved	Approved	Approved	Approved
72760	409	YUL	3​		Approved
7292	27	YUL	8​	Approved		Approved
74782	414	YUL	8​		Approved
75053	411	YUL	8​	Approved	Approved	Approved	Approved
77594	10	YUL	8​	Approved	Approved	Approved	Approved
77917	15	YUL	8​	Approved	Approved	Approved	Approved
77948	20	YUL	8​	Approved		Approved
77951	26	YUL	8​	Approved		Approved
77995	21	YUL	8​	Approved		Approved
77997	7	YUL	8​	Approved	Approved	Approved	Approved
78027	8	YUL	8​	Approved	Approved	Approved	Approved
78039	19	YUL	8​	Approved		Approved
78131	5	YUL	8​	Approved	Approved	Approved	Approved
85511	408	YUL	8​	Approved	Approved	Approved	Approved
85847	31	YUL	7​	Approved		Approved
89652	24	YUL	8​	Approved		Approved
897	40	YUL	8​	Approved		Approved
90865	14	YUL	8​	Approved	Approved	Approved	Approved
92757	12	YUL	8​	Approved	Approved	Approved	Approved
94545	13	YUL	8​	Approved	Approved	Approved	Approved
9596	18	YUL	8​	Approved		Approved
99578	404	YUL	8​	Approved	Approved	Approved	Approved
99650	403	YUL	8​	Approved	Approved	Approved	Approved
99761	34	YUL	6​			Approved
99904	39	YUL	8​	Approved		Approved

or PQ with PivotTable

Month	jan
Campus	YUL
Status	Denied
Student ID	Student name	months
13346	49	5​
15999	48	8​
16635	56	8​
16699	47	1​
203706	61	8​
203774	62	3​
204357	63	8​
210958	65	8​
21138	29	8​
211463	64	8​
21938	23	8​
22018	46	8​
22169	42	8​
22405	413	8​
24453	50	8​
2614	44	8​
3011	37	8​
3125	17	8​
31517	43	8​
31986	51	8​
32801	38	8​
33351	52	8​
33462	53	8​
33512	54	8​
33537	417	8​
33793	22	8​
41153	25	8​
43157	418	5​
43863	60	8​
43979	58	8​
45297	416	8​
4777	32	8​
52949	41	8​
54525	28	8​
55432	16	8​
59396	30	8​
65215	35	5​
65260	33	8​
6671	45	8​
7292	27	8​
74782	414	8​
77948	20	8​
77951	26	8​
77995	21	8​
78039	19	8​
85847	31	7​
89652	24	8​
897	40	8​
9596	18	8​
99761	34	6​
99904	39	8​

 

[morangsters]

You're on the right track. Only those who were approved are required to populate the list. I also want to enter OCTOBER (or November or January etc...) and have all those who applied for that particular month to populate.