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]

@Yongle - Have you had a chance to look at the VBA implementation? I ran a simulation with several thousand rows of data to see how excel would cope and alas, the sheet crashed.

 

[Yongle]

Apologies jordanbuchan359 - this thread fell off my radar - I will look at it this afternoon

 

[jordanbuchan359]

Apologies jordanbuchan359 - this thread fell off my radar - I will look at it this afternoon

No problem! Thanks for taking the time to look at it, your help is greatly appreciated.

 

[jordanbuchan359]

Hi Yongle,

Just a heads up. I managed to rein in the calculation time by adding limits to the array formulas.