Excel formula to display values from one sheet to another based on various conditions

[kinkate18nic]

I have an excel master sheet in the below format:

There are 29 subjects, however a student might opt for max 5 or 6 subjects out of this 29 and only those subjects will have marks data, the marks will be blank for the subjects which is not opted by the students. Only one school name is shown in this sheet, however there will be multiple school name(cno is the school code).

I want the above master data in school wise sheets of the below format:

Here, what I will do is just paste the roll number of students and rest of the data should automatically populate from the master sheet. Here I have max 6 subjects as placeholders, when i place the rollno say the first one, the formula should check which subjects are enrolled by student and display the subject names in the columns and marks data below each of the.


Is this possible using combinations of xlookup or any suggestion wold be appreciated.

Thanks

 

[Anthony47]

I suggest that you attach a sample workbook populated with sample data that can be used for better understanding the question and testing the possible solutions. Share it via a filesharing service, like Dropbox, or OneDrive, or GoogleDrive.
You should also specify which is your Office version.

 

[kinkate18nic]

I suggest that you attach a sample workbook populated with sample data that can be used for better understanding the question and testing the possible solutions. Share it via a filesharing service, like Dropbox, or OneDrive, or GoogleDrive.
You should also specify which is your Office version.

Here is the sample file link sample file
here is what I am trying to accomplish, little different from the original post and it actually reduces a requirement:

I want to just paste the roll number of students in the result sheet and the rest of the data should automatically populate from the master sheet. Here I have max 6 subjects as placeholders, when I place the roll no say the first one, the formula should check which subjects are enrolled by the student and display the subject names in the columns and marks data below each of the. since there are 14 subjects in master sheet and I want to display just the ones student opted and with its marks, the order of subjects doesn't matter in result sheet as subject code is also being pulled from the master sheet for each subject

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or 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’)

 

[kinkate18nic]

What version of Excel are you using?

I suggest that you update your Account details (or 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’)

Sorry, i missed mentioning it before. Office 365

 

[Anthony47]

I understand that line 5 in your Class9Result should be ignored (and indeed Roll No 910006 has subscribed to more than 6 subject, its data could not fit there)
You also say that also CNO should be tested, but there is not such a column in the data layout of Class9Result; so I used column A for my testing.
What I suggest is, in E6:

=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,myCNO,A6,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myCNO&myRoll,INDEX(myBT,0,2)&(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)

Then, copy E6 downward

The formula uses the function LET, that let you declare a Variable and its value (this is my best not technical description of that feature!), in pair. These are the Variable /their definition that you need to customize the formula:

myRoll / D6                            'myRoll points to Roll No on the output sheet
myBT / 'Class-9-IA-PR-DCF '!$A$6:$CB$15      'this is the Big Table on the source sheet, modify the last Row (15, in my test)
myCNO / A6                                   'myCNO points to the CNO on the Output Sheet; I USED COLUMN A

In my test enviroment I got the following results:

byMRxxx_C30226_SampleData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2	CNO	Name of the student	G	Roll No	SUB1					SUB2					SUB3					SUB4					SUB5					SUB6
3					SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	Result	R in	SCode
4								100					100					100					100					100					100			C in
5	1001			910001
6	1001			910002	402	23	44	67	B2	403	3	12	15	E	408	3	12	15	E	418	23	33	56	C1
7	1001			910011	#N/D
8
9
Class9Result

Cell Formulas
Range	Formula
E6:X6,E7	E6	=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,myCNO,A6,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myCNO&myRoll,INDEX(myBT,0,2)&(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)
Dynamic array formulas.

 

[kinkate18nic]

I understand that line 5 in your Class9Result should be ignored (and indeed Roll No 910006 has subscribed to more than 6 subject, its data could not fit there)
You also say that also CNO should be tested, but there is not such a column in the data layout of Class9Result; so I used column A for my testing.
What I suggest is, in E6:

=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,myCNO,A6,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myCNO&myRoll,INDEX(myBT,0,2)&(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)

Then, copy E6 downward

The formula uses the function LET, that let you declare a Variable and its value (this is my best not technical description of that feature!), in pair. These are the Variable /their definition that you need to customize the formula:

myRoll / D6                            'myRoll points to Roll No on the output sheet
myBT / 'Class-9-IA-PR-DCF '!$A$6:$CB$15      'this is the Big Table on the source sheet, modify the last Row (15, in my test)
myCNO / A6                                   'myCNO points to the CNO on the Output Sheet; I USED COLUMN A

In my test enviroment I got the following results:

byMRxxx_C30226_SampleData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2	CNO	Name of the student	G	Roll No	SUB1					SUB2					SUB3					SUB4					SUB5					SUB6
3					SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	SCODE	IA/PR	TH	TOT	G	Result	R in	SCode
4								100					100					100					100					100					100			C in
5	1001			910001
6	1001			910002	402	23	44	67	B2	403	3	12	15	E	408	3	12	15	E	418	23	33	56	C1
7	1001			910011	#N/D
8
9
Class9Result

Cell Formulas
Range	Formula
E6:X6,E7	E6	=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,myCNO,A6,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myCNO&myRoll,INDEX(myBT,0,2)&(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)
Dynamic array formulas.

Thanks a lot for the formula.

In my second post, I mentioned my requirement changed a bit but didn't mention it in detail. Now we do not have to check SCODE, it's only the roll number. So in the result sheet, I will paste the roll number and the opted subjects for those roll numbers should appear under those SUB columns. And yes, there will be max 5 or 6 subjects opted by a student in actual masterhseet, in case the sample data has more for some, it would have been just an oversight.

I guess I can play with your formula and modify it to meet my current requirements, I want to avoid wasting your time once more due to my failure to communicate my changes to the requirements initially.

Thanks again.

 

[Anthony47]

If we can forget about CNO:

=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myRoll,(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)

Insert the formula in Class9Result-E6, then copy dowm

 

[kinkate18nic]

If we can forget about CNO:

=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myRoll,(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)

Insert the formula in Class9Result-E6, then copy dowm

Working like a charm!!

Thanks a ton.

 

[kinkate18nic]

If we can forget about CNO:

=LET(myRoll,D6,myBT,'Class-9-IA-PR-DCF '!$A$6:$CB$15,head,'Class-9-IA-PR-DCF '!$A$3:$CB$3,myRow,INDEX(myBT,MATCH(myRoll,(INDEX(myBT,0,10)),0),0),myChoices,IF((head="SCODE")*(myRow<>""),COLUMN(myRow),""),opted,FILTER(myChoices,myChoices<>"")-11,cippa,FILTER(myRow,NOT(ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5),opted,0)))),cippa)

Insert the formula in Class9Result-E6, then copy dowm

Just a quick query, what if I have the same format but with more options subjects options, subjects list at starts from instead of K starts at L and end in EU instead of CB, will changing to this (myBT,0,11) will work?