Exam Question bank generator for different students categories

[xkotx]

Hi Guys,
Is there any chance that someone could help me with creating an excel question bank generator for 5 different student category ? Some questions can be applicable to two or three different student category.

For now my "data base" example looks like that:

The ideal solution would be to pick student category, for example A + C or just single A or B and that excel based on applicability(Y- yes / N-No) will produce list of questions that are applicable to that Student category and then list those questions in separate sheet? I'm not sure is something like that even possible in excel...
All help will be very appreciated.

Thank you!

 

[earthworm]

Please give some example . Its still not clear

 

[xkotx]

Hi, My apologies. Let me clarify. I have list of questions, around 60 and 5 students category. Some of those questions are applicable only to student category A which is fine in this cause because I can filter those questions by setting filter in C2 which will list all question with "Y" in the cell. However some exams are for Category A & B for example. In this cause if I set the filter firstly in Cat A and one of the questions was not applicable to cat A student but is to cat B that question will not show after filter is applied. If I would set the filter to cat A as "Y" than copy questions to different sheet, go back unmark filter and set it for cat B I will have many questions that are duplicated which "remove duplicates" function will not mark as duplicates(not sure why).

Basically I want excel to generate list of questions based on the data for one or for mixed categories of students where cell was marked as "Y" as applicable and to save that list in separate sheet ready to be copied and pasted to word document. The Ideal solution would be to have some sort of tick box where I can mark that, this test is applicable to student Cat A & B & C or C&A or just B ect. and excel will filter all question looking for applicability for those categories and generate complete list at the end.

Hope the above will help to understand what's my problem and will clarify any questions that you could have if not please let me know.

Thank you!!

 

[earthworm]

I think i will make below approach to make it more easy

Book1
	A	B	C	D	E	F	G
1	Reference	A	B	C	D	E	Test
2	Question 1	Y	Y	Y	Y	Y	CategoryA B C D E
3	Question 2	N	N	N	Y	N	Category D
4	Question 3	N	N	N	Y	N	Category D
5	Question 4	N	Y	N	Y	N	Category B D
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	="Category" &IF(B2="Y",B$1,"")&" "&IF(C2="Y",C$1,"")&" "&IF(D2="Y",D$1,"")&" "&IF(E2="Y",E$1,"")&" "&IF(F2="Y",F$1,"")

 

[xkotx]

Hi,
Thank you very much for the formula but I'm looking to place Question in the test column rather than category. Based on category move all applicable questions: Question 1, Question 2, or 4 depending is it applicable to the category that the test is prepared for. For example if I pick that test will be for CAT A & B all questions with Y for that category will be moved to Test column or new sheet. Sorry but its not that easy to explain over text...

Thank you

 

[earthworm]

Hi,
Thank you very much for the formula but I'm looking to place Question in the test column rather than category. Based on category move all applicable questions: Question 1, Question 2, or 4 depending is it applicable to the category that the test is prepared for. For example if I pick that test will be for CAT A & B all questions with Y for that category will be moved to Test column or new sheet. Sorry but its not that easy to explain over text...

Thank you

Its very easy . I have already solved the difficult part . Now you only need apply that formula on all questions to segregate in first step . You are looking from different and difficult angle . However use that formula to further segregate questions and shape your requirement through another formulas.

 

[xkotx]

Ok great I will try now

 

[Peter_SSs]

@xkotx
What version of Excel are you using? I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[xkotx]

Ok it worked! I have applied a macro which is assigned to button that filter the required questions and move them to new sheet.

Thank you again!!

 

[Peter_SSs]

Glad you have something working, but it would still be useful to know your version(s) for the future.