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

kinkate18nic

New Member
Joined
Aug 9, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have an excel master sheet in the below format:
1677133850337.png


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:

1677134094460.png


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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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
 
Upvote 0
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’)
 
Upvote 0
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
 
Upvote 0
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:
Code:
=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:
Code:
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
2CNO Name of the student GRoll NoSUB1SUB2SUB3SUB4SUB5SUB6
3SCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGResult R inSCode
4100100100100100100C in
51001910001
61001910002402234467B240331215E40831215E418233356C1
71001910011#N/D
8
9
Class9Result
Cell Formulas
RangeFormula
E6:X6,E7E6=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.
 
Upvote 0
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:
Code:
=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:
Code:
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
2CNO Name of the student GRoll NoSUB1SUB2SUB3SUB4SUB5SUB6
3SCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGSCODEIA/PRTHTOTGResult R inSCode
4100100100100100100C in
51001910001
61001910002402234467B240331215E40831215E418233356C1
71001910011#N/D
8
9
Class9Result
Cell Formulas
RangeFormula
E6:X6,E7E6=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.
 
Upvote 0
If we can forget about CNO:
Excel Formula:
=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
 
Upvote 1
Solution
If we can forget about CNO:
Excel Formula:
=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.
 
Upvote 0
If we can forget about CNO:
Excel Formula:
=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?
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top