Vba function - search range for state of completion

jordanbuchan359

New Member
Joined
Jun 15, 2018
Messages
15
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:

NameSub CertCompleteOverall Cert
JakeAAYesTRUE
JakeABYesTRUE
JakeACYesTRUE
Jake
ADYesTRUE
PamelaAAYesFALSE
PamelaABYesFALSE
PamelaADYesFALSE

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,651
Office Version
365
Platform
Windows
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
 
Last edited:

Reggie Perrin

New Member
Joined
Jun 23, 2018
Messages
12
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #FFFFFF;background-color: #305496;;">Name</td><td style="color: #FFFFFF;background-color: #305496;;">AA</td><td style="color: #FFFFFF;background-color: #305496;;">AB</td><td style="color: #FFFFFF;background-color: #305496;;">AC</td><td style="color: #FFFFFF;background-color: #305496;;">AD</td><td style="color: #FFFFFF;background-color: #305496;;">Complete</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Jake</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Pamela</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">E5:H5,"No"</font>),"No","Yes"</font>)</td></tr></tbody></table></td></tr></table><br />
 

jordanbuchan359

New Member
Joined
Jun 15, 2018
Messages
15
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:

Overall completion
StudentMathHistoryArt
JakeTRUETRUEFALSE
PamelaFALSETRUETRUE

<tbody>
</tbody>
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,651
Office Version
365
Platform
Windows
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?
NameSub Cert
CompleteOverall Cert
JakeAA
YesTRUE
JakeAB
YesTRUE
JakeAC
YesTRUE
JakeAD
YesTRUE
PamelaAA
YesFALSE
PamelaAB
YesFALSE
PamelaAD
YesFALSE

<tbody>
</tbody>
 

Reggie Perrin

New Member
Joined
Jun 23, 2018
Messages
12
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

New Member
Joined
Jun 15, 2018
Messages
15
Yongle, Reggie,

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

Full NameModule NameLearner Status
AlexHistory - 1Complete
AlexHistory - 2Complete
AlexMath - 1 Complete
AlexMath - 2Complete
AlexMath - 3Complete
AlexMath - 4Complete
PamelaHistory - 1Complete
PamelaHistory - 2Complete
PamelaMath - 1 Complete
PamelaMath - 2Complete
PamelaMath - 3Complete

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

Well-known Member
Joined
Mar 11, 2015
Messages
4,651
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 15, 2018
Messages
15
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,651
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top