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:

<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>
[TD="class: xl64, width: 84"]Name[/TD]
[TD="class: xl64, width: 64"]Sub Cert[/TD]
[TD="class: xl64, width: 73"]Complete[/TD]
[TD="class: xl64, width: 88"]Overall Cert[/TD]

[TD="class: xl63"]Jake[/TD]
[TD="class: xl63"]AA[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]TRUE[/TD]

[TD="class: xl63"]Jake[/TD]
[TD="class: xl63"]AB[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]TRUE[/TD]

[TD="class: xl63"]Jake[/TD]
[TD="class: xl63"]AC[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]TRUE[/TD]

[TD="class: xl63"]Jake
[/TD]
[TD="class: xl63"]AD[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]TRUE[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]AA[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]FALSE[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]AB[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]FALSE[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]AD[/TD]
[TD="class: xl63"]Yes[/TD]
[TD="class: xl63"]FALSE
[/TD]

</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
[TABLE="width: 334"]
<tbody>[TR]
[TD]Module[/TD]
[TD]Subject[/TD]
[/TR]
[TR]
[TD]UK-STD-History A[/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD]UK-STD-History B[/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD]UK-STD-Maths Algebra[/TD]
[TD]Maths[/TD]
[/TR]
[TR]
[TD]UK-STD-Maths Trigonometry[/TD]
[TD]Maths[/TD]
[/TR]
[TR]
[TD]UK-STD-Maths Calculus[/TD]
[TD]Maths[/TD]
[/TR]
[TR]
[TD]UK-STD-Maths Statistics[/TD]
[TD]Maths[/TD]
[/TR]
[TR]
[TD]UK-STD-Art History Renaissance[/TD]
[TD]Art History[/TD]
[/TR]
[TR]
[TD]UK-STD-Art History Soviet Propaganda[/TD]
[TD]Art History[/TD]
[/TR]
[TR]
[TD]UK-STD-Art History Medieaval[/TD]
[TD]Art History[/TD]
[/TR]
</tbody>[/TABLE]

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

<tbody>
[TD="class: xl67"]Full Name[/TD]
[TD="class: xl67"]Module Name[/TD]
[TD="class: xl67"]Learner Status[/TD]
[TD="class: xl67"]LookUp[/TD]
[TD="class: xl67, width: 92"]Subject status[/TD]

[TD="class: xl68, width: 101"]Alex[/TD]
[TD="class: xl65"]UK-STD-History A[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Alex[/TD]
[TD="class: xl65"]UK-STD-History B[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Alex[/TD]
[TD="class: xl65"]UK-STD-Maths Algebra[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Alex[/TD]
[TD="class: xl65"]UK-STD-Maths Trigonometry[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Alex[/TD]
[TD="class: xl65"]UK-STD-Maths Calculus[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Alex[/TD]
[TD="class: xl65"]UK-STD-Maths Statistics[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Pamela[/TD]
[TD="class: xl65"]UK-STD-History A[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Pamela[/TD]
[TD="class: xl65"]UK-STD-History B[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Pamela[/TD]
[TD="class: xl65"]UK-STD-Maths Algebra[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Pamela[/TD]
[TD="class: xl65"]UK-STD-Maths Trigonometry[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Pamela[/TD]
[TD="class: xl65"]UK-STD-Maths Calculus[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl68, width: 101"]Jenny[/TD]
[TD="class: xl65"]UK-STD-Art History Renaissance[/TD]
[TD="class: xl68, width: 169"]Complete[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl69, width: 101"]Jenny[/TD]
[TD="class: xl66"]UK-STD-Art History Soviet Propaganda[/TD]
[TD="class: xl69, width: 169"]Complete[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

</tbody>

4. Paste these values into "StageOfCompletion" starting at A1
[TABLE="width: 374"]
<tbody>[TR]
[TD]Student[/TD]
[TD]History[/TD]
[TD]Maths[/TD]
[TD]Art History[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pamela[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jenny[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 764"]
<tbody>[TR]
[TD="align: center"]Full Name
[/TD]
[TD="align: center"]Acquired /Completion Date
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Learner Status[/TD]
[TD="align: center"]LOOKUP[/TD]
[TD="align: center"]SUBJECT STATUS
[/TD]
[/TR]
</tbody>[/TABLE]

- 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?