Urgent - How to using formula to compare 2 worksheets

[Guest]

Hi all,

I have 2 worksheets to compare as follows:-

sheet 1 with 6 courses to be completed by learners (these are a compulsory
courses)

course 1
course 2
course 3
course 4
course 5
course 6

In worksheet 2
I have hundred learners who have completed some course from the above

Learner A has comepleted
course 1
course 3
course 5

Learner B has completed
Course 2
course 3
course 5
coursse 6

Learner C has completed all course
course 1
Course 2
course 3
course 4
course 5
course 6


How to set a formula to know who dose not completed the courses for a
hundred over learners


Thank you in advanced.

 

[Guest]

Can you be more specific on the structure of your sheets: content of columns
and rows!
Stefi


„Calculate Date range†ezt írta:

 

[Guest]

Hi Stefi,
The information are in rows.

Can you be more specific on the structure of your sheets: content of columns
and rows!
Stefi


„Calculate Date range†ezt írta:

 

[Guest]

Suppose column A contains Learners' entries in sheet2 (starting in A1),
separated by an empty row, then install this function in a normal module:

Function splitlearner(learnerrow)
splitlearner = Range("A" & learnerrow & ":A65536").Find(What:="",
After:=Range("A" & learnerrow), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row
End Function

Insert this formula in sheet2, cell B1
=IF(AND(NOT(ISBLANK(A1)),LEFT(A1,6)<>"course"),IF(splitlearner(ROW())-ROW()-1<6,"Not completed!",""),"")

Drag it down until the last row containing data!


Regards,
Stefi

„Calculate Date range†ezt írta: