Help on what type of formula should be use?

[RUERIE]

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 1	Module A		Module C
Course 2		Module B		Module D
Course 3	Module A	Module B	Module C
Course 4	Module A	Module B	Module C	Module D
Course 5	Module A	Module B	Module C

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

 

[Redwolfx]

VBA is probably a good route to take for expanded modules

 

[Redwolfx]

If you wanted to put the Courses Exempt on the right side of the student chart for each student you could use

Sub Courses()
Dim C1 As String
Dim C2 As String
Dim C3 As String
Dim C4 As String
Dim C5 As String
Dim Mycell As Range
 
For Each Mycell In Range("F2:F6")
C1 = ""
C2 = ""
C3 = ""
C4 = ""
C5 = ""
        If Mycell(1, -3).Value >= Sheets("Sheet3").Range("B2").Value And Mycell(1, -2).Value >= Sheets("Sheet3").Range("C2").Value And Mycell(1, -1).Value >= Sheets("Sheet3").Range("D2").Value And Mycell(1, 0).Value >= Sheets("Sheet3").Range("E2").Value Then
        C1 = "1, "
        End If
    
        If Mycell(1, -3).Value >= Sheets("Sheet3").Range("B3").Value And Mycell(1, -2).Value >= Sheets("Sheet3").Range("C3").Value And Mycell(1, -1).Value >= Sheets("Sheet3").Range("D3").Value And Mycell(1, 0).Value >= Sheets("Sheet3").Range("E3").Value Then
        C2 = "2, "
        End If
        If Mycell(1, -3).Value >= Sheets("Sheet3").Range("B4").Value And Mycell(1, -2).Value >= Sheets("Sheet3").Range("C4").Value And Mycell(1, -1).Value >= Sheets("Sheet3").Range("D4").Value And Mycell(1, 0).Value >= Sheets("Sheet3").Range("E4").Value Then
        C3 = "3, "
        End If
        
        If Mycell(1, -3).Value >= Sheets("Sheet3").Range("B5").Value And Mycell(1, -2).Value >= Sheets("Sheet3").Range("C5").Value And Mycell(1, -1).Value >= Sheets("Sheet3").Range("D5").Value And Mycell(1, 0).Value >= Sheets("Sheet3").Range("E5").Value Then
        C4 = "4, "
        End If
        
        If Mycell(1, -3).Value >= Sheets("Sheet3").Range("B6").Value And Mycell(1, -2).Value >= Sheets("Sheet3").Range("C6").Value And Mycell(1, -1).Value >= Sheets("Sheet3").Range("D6").Value And Mycell(1, 0).Value >= Sheets("Sheet3").Range("E6").Value Then
        C5 = "5"
        End If
        
    Mycell.Value = C1 & C2 & C3 & C4 & C5
Next Mycell
End Sub

 

[RUERIE]

oh my! THANKS

Can you brief me through a little on the VBA code. ?
If I expand my modules, how should i edit the vba code?

again i'm sorry for my questions... i am still new to vba... =(

 

[Redwolfx]

So the VBA is as follows.

C1-C5 are strings (essentially a series of characters)
myCell is a range (a cell or cells)

We are using a For loop. This means it will go through each cell in the range that you give it. If you increase the number of students you will increase the F6.

Then I make sure each of the strings is set to Blank (""). This is done because if you don't once the string changes it will remain that way.

Then we come to the If Statements.

There is 1 if statement for each course. As mentioned before myCell is a range (Cell or Cells, in this case a Cell between F2:F6 When you add (1, -3) after mycell you are modifying the position of that cell (Row, Column) (1 represents the place of the cell).

So the range starts with F2
    F2(1,-3)=B2    >= covers if the course has a module or it is blank and the student has the module
                                          This is the Cell that holds Module A                           C2
If (F2(1,-3)         >=            Sheets("Sheet3").Range("B2").Value AND                  F2(1,-2) >= ......

If all that is true Then C1 = "1, "

Then it does the same thing for Course 2, 3, 4, and 5

Then at the end it strings together the values for C1 C2 C3 C4 and C5

Then it loops back up and does it for F3

So to expand the code for additional modules would still take some work, You would have to add additional mycell(1,-4), mycell(1,-5) etc. but it is relatively quick to do that.

 

[RUERIE]

THANKS!!! Please allow me to attempt this again!!
I have tried with my VBA, but somehow the PC had to die on me.. Im awaiting to reinstall the excel program.. i will attempt this and again THANKS a MILLION!

I am so inspired to improve my excel skills!