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.

 

[Yongle]

Not sure if you need VBA
With Name in column A etc, Try this formula in D2 copied down
=IF(COUNTIFS(A:A,A2,B:B,"AA")+COUNTIFS(A:A,A2,B:B,"AB")+COUNTIFS(A:A,A2,B:B,"AC")+COUNTIFS(A:A,A2,B:B,"AD")=4,TRUE,FALSE)

This will only work if there are 4 certificates that always apply to all

 

[Reggie Perrin]

You could use sub cert in columns instead, then you'd need just one row per student, with a column at the end for Overall cert with the following formula.

Book1
	D	E	F	G	H	I
4	Name	AA	AB	AC	AD	Complete
5	Jake	Yes	Yes	Yes	Yes	Yes
6	Pamela	Yes	Yes	Yes	No	No
Sheet1

Cell Formulas
Range	Formula
I5	=IF(COUNTIF(E5:H5,"No"),"No","Yes")

 

[jordanbuchan359]

Thanks for your replies, unfortunately neither of the methods will work. The data is pulled as a report in the format from my original post. The number of sub certifications to acheive overall certification wont always be four... for example, History may require 2, maths 4 and Art 5.

Ideally, I'd like VBA to run a check of the data and return a value somewhere else in the sheet... something like this:

[TABLE="width: 271"]
<tbody>[TR]
[TD="colspan: 4"]Overall completion[/TD]
[/TR]
[TR]
[TD]Student[/TD]
[TD]Math[/TD]
[TD]History[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]Jake[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Pamela[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

You could create
- a named range for each subject detailing which certifications are required for that subject
- a table detailing which subjects each student is taking

Those could then be looked up, in conjuntion with your data, to create your table of overall completion

Does your data really look like this? Or does Sub Cert contain the subject name?
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl64"]Name[/TD]
[TD="class: cms_table_xl64, width: 64"]Sub Cert
[/TD]
[TD="class: cms_table_xl64, width: 73"]Complete[/TD]
[TD="class: cms_table_xl64, width: 88"]Overall Cert
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Jake[/TD]
[TD="class: cms_table_xl63"]AA
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]TRUE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Jake[/TD]
[TD="class: cms_table_xl63"]AB
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]TRUE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Jake[/TD]
[TD="class: cms_table_xl63"]AC
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]TRUE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Jake[/TD]
[TD="class: cms_table_xl63"]AD
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]TRUE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Pamela[/TD]
[TD="class: cms_table_xl63"]AA
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]FALSE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Pamela[/TD]
[TD="class: cms_table_xl63"]AB
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]FALSE[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]Pamela[/TD]
[TD="class: cms_table_xl63"]AD
[/TD]
[TD="class: cms_table_xl63"]Yes[/TD]
[TD="class: cms_table_xl63"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Reggie Perrin]

So your initial table isn't the full data? The subject is included as well? Without it you can't reference the grades needed.

 

[jordanbuchan359]

Yongle, Reggie,

Apologies, It seems my oversimplification only made things harder to understand. Here's a better representation:

<colgroup><col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> </colgroup><tbody>
[TD="class: xl63, width: 83"]Full Name[/TD]
[TD="class: xl63, width: 109"]Module Name[/TD]
[TD="class: xl63, width: 99"]Learner Status[/TD]

[TD="class: xl63"]Alex[/TD]
[TD="class: xl63"]History - 1[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Alex[/TD]
[TD="class: xl63"]History - 2[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Alex[/TD]
[TD="class: xl63"]Math - 1 [/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Alex[/TD]
[TD="class: xl63"]Math - 2[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Alex[/TD]
[TD="class: xl63"]Math - 3[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Alex[/TD]
[TD="class: xl63"]Math - 4[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]History - 1[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]History - 2[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]Math - 1 [/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]Math - 2[/TD]
[TD="class: xl63"]Complete[/TD]

[TD="class: xl63"]Pamela[/TD]
[TD="class: xl63"]Math - 3[/TD]
[TD="class: xl63"]Complete[/TD]

</tbody>

The data dump only contains completed modules - for example in the table above Pamela is missing math - 4. I do have a seperate list of all module offerings, which I could use to create named ranges. I've also created a userform which populates an expected subject table. The only thing I'm missing is how to reference actual vs expected.

Oh, and the learner status column only applies to the individual module, not the overall subject.

Thanks for the help btw!

 

[Yongle]

ok - that makes life easier
Can you check all module names and confirm that none of them contain more than one hyphen (if that is so, can use it to strip out subject name)

 

[jordanbuchan359]

All of the module names contain more than one hyphen (Another simplifcation.. my bad). All of the names begin with the following format "UK-STD-" followed by the subject and module. e.g.

UK-STD-Maths Algebra
UK-STD-History B

Etc..

 

[Yongle]

Thanks - I will get back to this AFTER the England football match! And will post a suggestion either later today or tomorrow morning

In principle we need a look-up table - so that the module name can provide the subject and the number of modules per subject

Do you already have such a table in the workbook?
- if you do, post the first few rows so that I can see exactly what it looks like
- if not, I will think of the easiest way to lay one out to keep formulas simple etc