Formula to search code or letters in another cell and return it in different cell

[kiebel]

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

G

R

S

T

U

V

1

Course code <tbody> </tbody>

COM 121

COM 122

COM 123

COM 124

2

CREDIT UNIT

CW

EX

TS

GRADE

CW

EX

TS

GRADE

CW

EX

TS

GRADE

CW

EX

TS

GRADE

3

NAME

MATRIC. NO.

<tbody> </tbody> Column to search <tbody> </tbody>

Remaining courses to clear

4

KIEBEL, Manfred

HM14/2016

12

13

25

F

30

20

50

CD

Abs

Rpt COM 121, COM 123, COM 122

Rpt COM 121, COM 123

<tbody>
</tbody>

CELL F4, J4, N4 AND R4 ,These Cells search cell S4 and indicate Abs if the content correspond with the course code in cell C2, G2, K2 or O2

CELL T4 should indicate 'Nil' if all course code are cleared or courses code that are not cleard should now appear here


PLEASE I NEED FORMULA TO SOLVE THIS PROBLEM

 

[portews]

This method uses regular formulas in the grade to return an "Abs" if it's a required course.When you get a grade for the course, overwrite the formula with a grade. You put the call for the User Defined Function below in the right most column of each student row. It sees which courses are required (in second most right hand column), searches for the course name in row 1, and looks at the grade associated with that course. If it's not completed, or failed, it returns the courses that need to be completed. If they are all completed, it returns "Nil". The function may need some tweaking depending on how the spreadsheet is built. Are there only 4 course columns? Are there header columns for the required courses and needed courses column? Are they always the same columns?

You'll need one of these for each course. This one goes in F4 for the course in C1. Change C to whatever column has the course number. Change the S to whatever column has the required courses.

=IF(IFERROR(FIND($C$1,S4),0)>0,"Abs","")

Put this code in a Module:

Function compareCourses() As String
Dim RqdCourses() As String
Dim Rng As Range
Dim x As Long
[COLOR=#00ff00]'what row is the function in[/COLOR]
x = Application.Caller.Row
[COLOR=#00ff00]'find the last used column in row one.[/COLOR]
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
[COLOR=#00ff00]'if there are headers in the two Rpt columns, you'll need this to find the last course, if not, use LastCol in it's place.[/COLOR]
lastCourse = Cells(1, lastCol - 2).End(xlToLeft).Column
[COLOR=#00ff00]'turn off events[/COLOR]
Application.EnableEvents = False
[COLOR=#00ff00]'set the beginning text[/COLOR]
temp = "Rpt "
[COLOR=#00ff00]'put the required courses into an array[/COLOR]
RqdCourses = Split(Replace(Cells(x, lastCol - 1), "Rpt ", ""), ", ")
[COLOR=#00ff00]    'loop thru each required course[/COLOR]
    For i = 0 To UBound(RqdCourses)
[COLOR=#00ff00]        'if there is something in that item[/COLOR]
        If Trim(RqdCourses(i)) <> "" Then
[COLOR=#00ff00]            'set the columns to find the course names[/COLOR]
            With Sheets("Sheet1").Range(Cells(1, 3), Cells(1, lastCourse))
[COLOR=#00ff00]                'search for the course name[/COLOR]
                Set Rng = .Find(What:=RqdCourses(i), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
[COLOR=#00ff00]                'where the course name is found[/COLOR]
                If Not Rng Is Nothing Then
[COLOR=#00ff00]                    'if the course name is Abs or a grade of F.  Add more Or statements for other failing grades.[/COLOR]
                    If Rng.Offset(x - 1, 3) = "Abs" Or Rng.Offset(x - 1, 3) = "F" Then
[COLOR=#00ff00]                        'write the course name[/COLOR]
                        temp = temp & Rng.Value & ", "
                    End If
                End If
            End With
        End If
Next
[COLOR=#00ff00]'get rid of the last comma and space[/COLOR]
If Right(temp, 2) = ", " Then temp = Left(temp, Len(temp) - 2)
[COLOR=#00ff00]'if there are no courses listed[/COLOR]
If Len(temp) < 5 Then
[COLOR=#00ff00]    'return Nil[/COLOR]
    compareCourses = "Nil"
Else
[COLOR=#00ff00]    'otherwise return the course names.[/COLOR]
    compareCourses = temp
End If
[COLOR=#00ff00]'turn events back on[/COLOR]
Application.EnableEvents = True
End Function

Then, in the last column, put:

=compareCourses()

 

[kiebel]

The course(s) may be Nil for a particular student or 1 to 12 maximum
I will try the code and get back to you

 

[kiebel]

I input =compareCourses(F4,J4,N4,R4,S4) in Cell T4 and it gives me

#NAME?

<tbody>
</tbody>

 

[portews]

Don't put anything inside the parentheses.

 

[kiebel]

It still show #NAME?