LinkBack URL
About LinkBacks
I have a list with names of students, their marks and a number of their preferences.
I want to filter the names as I tried to explain in the sample file.
How can do this?
I have a list with names of students, their marks and a number of their preferences.
I want to filter the names as I tried to explain in the sample file.
How can do this?
See if this helps you.
The approach I do gives me the correct answer for math but for the others disciplines no.
In P2 use the following formula and copy down (helper column)
Formula:
Please Login or Register to view this content.
In M2 use the formula (valid to M2:N..)
Formula:Please Login or Register to view this content.
IN O2 use the following formula
Formula:Please Login or Register to view this content.
See the file
Thank you very much.
As you said it gives the correct answer for math only.
This proposal converts the range No.:Preference5 into a table, then sorts by Mark. It also employs numerous helper columns which may be moved and/or hidden for aesthetic purposes.
Column C (inserted) is populated by typing the number 1 in C2 then uses: =SUM(C2,B2) for C3:C12
Column L is populated using: =IF(COUNTIFS(G$2:G2,G2)>INDEX(B$2:B$12,MATCH(G2,A$2:A$12,0)),"",G2)
Columns M:P are populated using: =IF(OR(SUMPRODUCT(--($L2:L2<>""))>0,SUM(SUMPRODUCT(--($L$2:L$21=H2)),COUNTIFS(M$1:M1,H2))=INDEX($B$2:$B$12,MATCH(H2,$A$2:$A$12,0))),"",H2)
Column Q is populated using: =L2&M2&N2&O2&P2
For the output:
The Subject column is populated using: =INDEX(A$2:A$12,MATCH(ROWS(A$1:A1),C$2:C$12))
The Name column is populated using: =INDEX(E$2:E$21,AGGREGATE(15,6,(ROW(A$2:A$21)-ROW(A$1))/(Q$2:Q$21=T2),COUNTIFS(T$2:T2,T2)))
The Marks column is populated using: =INDEX(F$2:F$21,MATCH(U2,E$2:E$21,0))
The Preference column is populated using: =SUMPRODUCT((COLUMN(L$1:P$1)-COLUMN(K$1))*(INDEX(L$2:P$21,MATCH(U2,E$2:E$21,0),0)<>""))
Conditional formatting is added to columns L:P to indicate when a subject is filled.
Conditional formatting is added to column E to indicate when a person is not scheduled.
The final result could also be displayed as a pivot table (modeled on Sayfa3) which references the range T1:W21 on Sayfa1.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you very much for your interest.
Your result gives almost the same as the result I have. But there there some differences between them.
And I have a question: Mark of Name1 is 23, mark of Name3 is 40 which is bigger and both have Biyology in their preference lists, so biyology should be assinged to Name3.
To see the differences beetween he results please see the attached file.
I believe that I now understand. The attached is similar to what I posted above except that column L is inserted between the 'preference 5' column and the first helper column.
The formula that populates M2:Q21 is: =IF(OR(SUMPRODUCT(--($L2:L2<>""))>0,SUMPRODUCT(--($M$1:$Q1=G2))=INDEX($B$2:$B$12,MATCH(G2,$A$2:$A$12,0))),"",G2)
Let us know if you have any questions.
Thank you very vey much JeteMc. That is OK now.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks