Help on what type of formula should be use?

RUERIE

New Member
Joined
Mar 18, 2014
Messages
13
Hi Everyone, I been cracking my head out but I think Excel should be able to resolve just maybe I am not knowledgeable enough...

Am using Excel 2010, I need to provide a formula that can generate the course the student is exempted from and what modules student needs to take in order to complete Course 4

E.G) If Student completed Module ABC, the result should reflect Course 1, 2, 3 & 5.
I would also need a formula that is able to show Course 4 just require Student to complete Module D

I was thinking of using Vlookup with CONCATENATE function.. but that doesn't seem to make it..
Is it possible to generate a formula for this? Appreciate the help and replies!!

Course 1Module AModule C
Course 2Module BModule D
Course 3Module AModule BModule C
Course 4Module AModule BModule CModule D
Course 5Module AModule BModule C

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
 
Both is okay! As it is more of keeping the data and referring to on a long term basis.
Which would you recommend? Can you clarify more on the form? Do i need to use VBA? or?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
No VBA Necessary.

So if the courses are Hard coded with the Modules that correspond you don't actually need the Course information.

This formula (I apologize for the length)

Looks for a name in Cell A14

Assumes Student Data on a sheet (Mine is in A8:E12)

Column A Consists of Student Names
Column B Module A
Column C Module B
Column D Module C
Column E Module D

Matches the name to the Sheet and returns the information you wanted.

Anything can be put into the columns to indicate the student has satisfied the module.

First Formula returns Courses Exempt:

Code:
=IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0),"1, ","") & IF(AND(VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,5,FALSE)<>0),"2, ","") & IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0),"3, ","") & IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0,VLOOKUP($A$14,$A:$E,5,FALSE)<>0),"4, ","") & IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0),"5","")

Second Formula Gives the Remaining Modules Needed:
Code:
=IF(VLOOKUP($A$14,$A:$E,2,FALSE)=0,"A, ","") & IF(VLOOKUP($A$14,$A:$E,3,FALSE)=0,"B, ","") & IF(VLOOKUP($A$14,$A:$F,4,FALSE)=0,"C, ","") & IF(VLOOKUP($A$14,$A:$E,5,FALSE)=0,"D","")

If you need help with the formulas, give me the sheet name and what cell you want to put in a name to look up on your Main sheet
 
Last edited:
Upvote 0
No Apology needed! let me try and I'll update if I can work it out or not..

Thanks a lot! I really Appreciate it! =)
 
Upvote 0
No VBA Necessary.

So if the courses are Hard coded with the Modules that correspond you don't actually need the Course information.

This formula (I apologize for the length)

Looks for a name in Cell A14

Assumes Student Data on a sheet (Mine is in A8:E12)

Column A Consists of Student Names
Column B Module A
Column C Module B
Column D Module C
Column E Module D

Matches the name to the Sheet and returns the information you wanted.

Anything can be put into the columns to indicate the student has satisfied the module.

First Formula returns Courses Exempt:

Code:
=IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0),"1, ","") & IF(AND(VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,5,FALSE)<>0),"2, ","") & IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0),"3, ","") & IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0,VLOOKUP($A$14,$A:$E,5,FALSE)<>0),"4, ","") & IF(AND(VLOOKUP($A$14,$A:$E,2,FALSE)<>0,VLOOKUP($A$14,$A:$E,3,FALSE)<>0,VLOOKUP($A$14,$A:$E,4,FALSE)<>0),"5","")

Second Formula Gives the Remaining Modules Needed:
Code:
=IF(VLOOKUP($A$14,$A:$E,2,FALSE)=0,"A, ","") & IF(VLOOKUP($A$14,$A:$E,3,FALSE)=0,"B, ","") & IF(VLOOKUP($A$14,$A:$F,4,FALSE)=0,"C, ","") & IF(VLOOKUP($A$14,$A:$E,5,FALSE)=0,"D","")

If you need help with the formulas, give me the sheet name and what cell you want to put in a name to look up on your Main sheet


Hi There! Im sorry to bother you again...

But i seem to get a circular error.

My static information is on sheet 3, A1:F5


Course 1Module A Module C
Course 2 Module B Module D
Course 3Module AModule BModule C
Course 4Module AModule BModule CModule D
Course 5Module AModule BModule C

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>


My student info is on sheet 4, A1:A5 like this

And the formulas to be on Cell A8 and A9 respectively. Will symbols work as well for vlookup??

Thanks again!
 
Upvote 0
So you will just need to make a slight adjustment. The circular error is because you are looking A:E and the formula is in A. Try these

=IF(AND(VLOOKUP($A$14,$A1:$E5,2,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,4,FALSE)<>0),"1, ","") & IF(AND(VLOOKUP($A$14,$A1:$E5,3,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,5,FALSE)<>0),"2, ","") & IF(AND(VLOOKUP($A$14,$A1:$E5,2,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,3,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,4,FALSE)<>0),"3, ","") & IF(AND(VLOOKUP($A$14,$A1:$E5,2,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,3,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,4,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,5,FALSE)<>0),"4, ","") & IF(AND(VLOOKUP($A$14,$A1:$E5,2,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,3,FALSE)<>0,VLOOKUP($A$14,$A1:$E5,4,FALSE)<>0),"5","")


=IF(VLOOKUP($A$14,$A1:$E5,2,FALSE)=0,"A, ","") & IF(VLOOKUP($A$14,$A1:$E5,3,FALSE)=0,"B, ","") & IF(VLOOKUP($A$14,$A1:$E5,4,FALSE)=0,"C, ","") & IF(VLOOKUP($A$14,$A1:$E5,5,FALSE)=0,"D","")
 
Upvote 0
So you will just need to make a slight adjustment. The circular error is because you are looking A:E and the formula is in A. Try these

Thanks! Its working now!

However, I just realized that if I change the required modules on the course 1. The answer will not be true anymore..
Is there anyway I could change the formula to reflect the change?
And if I would also like to find out from the student, what modules is require to complete course 1.
Is it possible to use back the similar formula?

Can I clarify on the below formula?
This is a IF and logic test function?, "1 stands for the course, <> refers to what? and ","" means another logic test?

IF(AND(VLOOKUP($A$13,$A:$E,2,FALSE)<>0,VLOOKUP($A$13,$A:$E,4,FALSE)<>0),"1, ","")

Again im sorry for the questions...
 
Upvote 0
If there is a new course which may consist over 80 modules...
Would you recommend the same type of formula??

Or is there a 2nd option?
 
Upvote 0
The modules required to satisfy a course are hard coded into the formula.

As far as the formula you questioned, The Formula breaks down as follows

Both A and B need to be true to satisfy the if statement
=IF( AND(A,B) , Value if True, Value if false)

The "<>" is a way of saying Not Equal to basically < less than or > Greater than.

Let me think about additional ways this can be accomplished. I'd recommend that this is NOT a way to handle one with 80 modules.
 
Upvote 0
Thanks for the explaination!
I would think if the course consists over 80 modules,the formulas would be realllly tooo long!

I was thinking if it would be require to use vba or forms.. if there is increasing more modules...
 
Upvote 0
Don't Hate me

This is still not used for 80 modules, but it will work if you change required modules for a course.

Code:
=IF(ISERROR(OR(IF(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(B2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(B2),FALSE))<>"",FIND(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(B2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(B2),FALSE)),VLOOKUP($A$8,$A$2:$E$6,2,FALSE),1)),IF(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(C2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(C2),FALSE))<>"",FIND(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(C2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(C2),FALSE)),VLOOKUP($A$8,$A$2:$E$6,3,FALSE),1)),IF(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(D2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(D2),FALSE))<>"",FIND(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(D2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(D2),FALSE)),VLOOKUP($A$8,$A$2:$E$6,4,FALSE),1)),IF(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(E2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(E2),FALSE))<>"",FIND(IF(VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(E2),FALSE)=0,"",VLOOKUP("Course 1",Sheet3!$A:$Z,COLUMN(E2),FALSE)),VLOOKUP($A$8,$A$2:$E$6,5,FALSE),1)))),"","1, ") & IF(ISERROR(OR(IF(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(B3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(B3),FALSE))<>"",FIND(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(B3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(B3),FALSE)),VLOOKUP($A$8,$A$2:$E$6,2,FALSE),1)),IF(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(C3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(C3),FALSE))<>"",FIND(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(C3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(C3),FALSE)),VLOOKUP($A$8,$A$2:$E$6,3,FALSE),1)),IF(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(D3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(D3),FALSE))<>"",FIND(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(D3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(D3),FALSE)),VLOOKUP($A$8,$A$2:$E$6,4,FALSE),1)),IF(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(E3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(E3),FALSE))<>"",FIND(IF(VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(E3),FALSE)=0,"",VLOOKUP("Course 2",Sheet3!$A:$Z,COLUMN(E3),FALSE)),VLOOKUP($A$8,$A$2:$E$6,5,FALSE),1)))),"","2, ") & IF(ISERROR(OR(IF(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(B4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(B4),FALSE))<>"",FIND(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(B4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(B4),FALSE)),VLOOKUP($A$8,$A$2:$E$6,2,FALSE),1)),IF(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(C4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(C4),FALSE))<>"",FIND(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(C4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(C4),FALSE)),VLOOKUP($A$8,$A$2:$E$6,3,FALSE),1)),IF(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(D4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(D4),FALSE))<>"",FIND(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(D4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(D4),FALSE)),VLOOKUP($A$8,$A$2:$E$6,4,FALSE),1)),IF(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(E4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(E4),FALSE))<>"",FIND(IF(VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(E4),FALSE)=0,"",VLOOKUP("Course 3",Sheet3!$A:$Z,COLUMN(E4),FALSE)),VLOOKUP($A$8,$A$2:$E$6,5,FALSE),1)))),"","3, ") & IF(ISERROR(OR(IF(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(B5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(B5),FALSE))<>"",FIND(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(B5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(B5),FALSE)),VLOOKUP($A$8,$A$2:$E$6,2,FALSE),1)),IF(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(C5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(C5),FALSE))<>"",FIND(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(C5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(C5),FALSE)),VLOOKUP($A$8,$A$2:$E$6,3,FALSE),1)),IF(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(D5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(D5),FALSE))<>"",FIND(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(D5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(D5),FALSE)),VLOOKUP($A$8,$A$2:$E$6,4,FALSE),1)),IF(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(E5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(E5),FALSE))<>"",FIND(IF(VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(E5),FALSE)=0,"",VLOOKUP("Course 4",Sheet3!$A:$Z,COLUMN(E5),FALSE)),VLOOKUP($A$8,$A$2:$E$6,5,FALSE),1)))),"","4, ") & IF(ISERROR(OR(IF(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(B6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(B6),FALSE))<>"",FIND(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(B6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(B6),FALSE)),VLOOKUP($A$8,$A$2:$E$6,2,FALSE),1)),IF(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(C6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(C6),FALSE))<>"",FIND(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(C6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(C6),FALSE)),VLOOKUP($A$8,$A$2:$E$6,3,FALSE),1)),IF(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(D6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(D6),FALSE))<>"",FIND(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(D6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(D6),FALSE)),VLOOKUP($A$8,$A$2:$E$6,4,FALSE),1)),IF(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(E6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(E6),FALSE))<>"",FIND(IF(VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(E6),FALSE)=0,"",VLOOKUP("Course 5",Sheet3!$A:$Z,COLUMN(E6),FALSE)),VLOOKUP($A$8,$A$2:$E$6,5,FALSE),1)))),"","5")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
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