Vba function - search range for state of completion

[jordanbuchan359]

Hi,

Hoping you can help out with this.. I'll try and explain it as best I can.

I have a list of completed certifications which I'd like to map to an overall certification.

In the example below, Jake has satisfied the all of the requirements and has been granted the overall certification, whereas Pamela is yet to complete the sub cert AC and hence is not granted the overall certification:

Name	Sub Cert	Complete	Overall Cert
Jake	AA	Yes	TRUE
Jake	AB	Yes	TRUE
Jake	AC	Yes	TRUE
Jake	AD	Yes	TRUE
Pamela	AA	Yes	FALSE
Pamela	AB	Yes	FALSE
Pamela	AD	Yes	FALSE

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody>
</tbody>

The data range could potentially be quite large (several thousand rows) and would cover a multitude of certifications. I'm unsure on what approach to take, any assistance would be greatly appreciated. I apologise if this request exceeds the normal scope of assistance.

 

[jordanbuchan359]

I'm sure you'll be happy with the result so far! Unfortunately not, I'll make an attempt at creating something later today.

 

[Yongle]

Here is a simple method without VBA to provide the TableOfCompletion
Create a new workbook and follow these instructions to test the method

1. Create sheets named "Modules", "DataDump","StageOfCompletion" - there are NO spaces in those names

2. Paste these 10 rows into "Modules" starting at cell A1

Module	Subject
UK-STD-History A	History
UK-STD-History B	History
UK-STD-Maths Algebra	Maths
UK-STD-Maths Trigonometry	Maths
UK-STD-Maths Calculus	Maths
UK-STD-Maths Statistics	Maths
UK-STD-Art History Renaissance	Art History
UK-STD-Art History Soviet Propaganda	Art History
UK-STD-Art History Medieaval	Art History

<tbody>
</tbody>

3. Paste these values into "DataDump" starting at cell A1

Full Name	Module Name	Learner Status	LookUp	Subject status
Alex	UK-STD-History A	Complete
Alex	UK-STD-History B	Complete
Alex	UK-STD-Maths Algebra	Complete
Alex	UK-STD-Maths Trigonometry	Complete
Alex	UK-STD-Maths Calculus	Complete
Alex	UK-STD-Maths Statistics	Complete
Pamela	UK-STD-History A	Complete
Pamela	UK-STD-History B	Complete
Pamela	UK-STD-Maths Algebra	Complete
Pamela	UK-STD-Maths Trigonometry	Complete
Pamela	UK-STD-Maths Calculus	Complete
Jenny	UK-STD-Art History Renaissance	Complete
Jenny	UK-STD-Art History Soviet Propaganda	Complete

<tbody>
</tbody>

4. Paste these values into "StageOfCompletion" starting at A1

Student	History	Maths	Art History
Alex
Pamela
Jenny
Jack
John

<tbody>
</tbody>

5. Formulas in "DataDump"
In cell D2 and copy down
=VLOOKUP(B2,Modules!A:B,2,0)

In cell D2 and copy down
=IF(COUNTIFS(A:A,A2,D:D,D2)-COUNTIF(Modules!B:B,DataDump!D2)=0,"Complete","Incomplete")

6. Array formula in "StageOfCompletion" , in cell B2 copy down and across
To commit an array formula use {Ctrl}{SHIFT}{Enter} and the formula will be enclosed in { } (do not try typing them in manualy - that does not work)
=IFERROR(INDEX(DataDump!$E:$E,MATCH($A2&B$1,DataDump!$A:$A&DataDump!$D:$D,0)),"")

If you would prefer VBA instead, let me know

At the moment this only looks to the values in the DataDump. If a student studying a subject has gained zero certificates to date obviously nothing appears in the table - if you want the table of ccompletion to include all subjects taken by the student then another lookup table is required listing all subjects for each student, and the formula amended for the extra condition

 

[Yongle]

Sorry - just spotted that I referred to D2 twice - oops!!
This is what I meant to write...

5. Formulas in "DataDump"
In cell D2 and copy down
=VLOOKUP(B2,Modules!A:B,2,0)
In cell E2 and copy down
=IF(COUNTIFS(A:A,A2,D:D,D2)-COUNTIF(Modules!B:B,DataDump!D2)=0,"Complete","Incomplete")

 

[jordanbuchan359]

Yongle - Apologies for the delay in replying. It's been a hectic week! Just tried out all of the formulas and they work perfectly ! My only concern is the calculations.. at the moment the data only contains a small number of students, but this will expand to potentially hundreds if not thousands. Would VBA be a better solution?

Also, I do have a sheet for Expected subjects that shares an identical format to the StageofCompletion sheet. This is populated form.

Appreciate all the help so far!

Regards,
Jordan

 

[Yongle]

VBA
A few thousand formulas will slow things down a little and VBA would speed things up
Now that you have something that works by formula it will be a lot easier to create some VBA to replace many formulas

Sheet "Modules"
I cannot think of any way that VBA can accurately discern the "Subject" from the "Module Name"
- removing "UK-STD-" leaves the string beginning with the subject which is perfect
- but there is no consistent pattern that I can see allowing the correct "end" of the string to be removed
- this means that sheet "Modules" must be maintained manually

I assume that the data dump is always consistent in its layout
- what is the sheet name?
- which columns are used?
- what is in each column?
- in which row are the headings?
- which is the first row of data?

I will pull some VBA together for you in the next day or two

 

[jordanbuchan359]

Yes, the datadump format will remain the same. Manually altering the modules shouldn't be overly taxing!

With regard to the data dump:

- The sheet name is DataDump
- Columns A:F
- Column Headers:

Full Name

Acquired /Completion Date

Name

Learner Status

LOOKUP

SUBJECT STATUS

<tbody>
</tbody>

- Headers are in the first row.
- Data begins directly below.

 

[jordanbuchan359]

How's that code coming along? I've searched online for similar methods, but none of them fit the bill! Eager to see your implementation so I can learn from it.

 

[Yongle]

- not even looked at it yet

 

[kvsrinivasamurthy]

The list of all modules should be provided separately. Then only desired result can be shown.

 

[Yongle]

@kvsrinivasamurthy
- what are you trying to tell us?