Comparing arrays from 2 sheets and returning values that match the pattern (VBA?)

NotExcelSavvy

New Member
Joined
May 3, 2021
Messages
1
Platform
  1. Windows
I am attempting to build a workbook in which the user can enter information about learner enrolment in courses and this gets mapped to the various qualifications the learner can achieve
I have the following information
List of learners with the courses they are enrolled in (a row for each course a learner is enrolled in)
List of qualifications with the courses required to achieve each one

The courses can be mapped to qualifications as follows:
  • 1 course mapped to 1 qualification (Learner X is enrolled in Course 1, they will achieve Qual 1)
  • 1 course mapped to multiple qualifications (Learner Y is enrolled in Course 2, they will achieve Qual 2 and Qual 3)
  • Multiple courses map to 1 qualification (Learner Z is enrolled in Course 1, 2 and 3, they will achieve Qual 4)
I first consolidated the courses the learners are enrolled in so each learner has one row, the various courses are represented by columns, each enrolled course is shown by X. (imgs attached: Learners Database > Learners Information). Similarly I set up the Qualification Mapping (img attached) with the same columns (courses) and courses required for each qual are shown by X (not sure if this is the best way, open to other suggestions).

I want to be able to compare all the courses the learner has completed with the courses that are required for each qualification, to see which ones they are eligible for (result shown in yellow cells in Learners Information image). I'm not sure how to achieve that though.

I'm assuming a comparison of this sort would be done through VBA? If so could anyone please suggest what the code would look like?

Is there a better way to set up this workbook?

Notes:
There are 150 qualifications achieved through various combinations of 150 courses
There will be a large database of learners (1000+)

Really appreciate any help!!
 

Attachments

  • Learners Database.PNG
    Learners Database.PNG
    18.4 KB · Views: 4
  • Learners Information.PNG
    Learners Information.PNG
    16.2 KB · Views: 4
  • Qualification Mapping.PNG
    Qualification Mapping.PNG
    7.2 KB · Views: 4

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,176,512
Messages
5,903,476
Members
435,031
Latest member
traceson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top