Comparing two arrays in excel

Question

I have two similar arrays, one for tracking the qualifications of employees, one for tracking the qualifications required for jobs. I wish to compare the two, and return TRUE if the employee is qualified for the job. Here's how my array's look:

Employee | Qualification1 | Qualification2 | Qualification3 
Dave     | 1              | 0              | 1
Bob      | 0              | 1              | 1

Job      | Qualification1 | Qualification2 | Qualification3 
Job1     | 0              | 1              | 1
Job2     | 1              | 0              | 1

The problem is that the data in the array and the size of the array are likely to change as new jobs are added etc. The arrays are both defined using an =OFFSET formula so they will change size with the data. What I am missing is some way to compare every value for a specific job to every value in a row in the other array. This just needs to output to a cell to tell me if each employee in turn is qualified (I plan on using this information in another formula afterwards). Also bear in mind it doesn't matter if the employee is overqualified for the job, so long as they also have the qualifications required for it.

If anybody has any ideas please let me know, I've been looking at this for like 3 days now

Thanks

Vityata · Accepted Answer · 2018-07-23 11:22:09Z

1

The easiest way to compare is to use the =Concatenate() formula and to compare the output string.

If the string is the same, the arrays are equal.

Concerning the qualifications, a possible solution is to go through some binary mathematics. The Excel formula for decimal to binary is quite easy - =DEC2BIN(11):

assign values like this 1,2,4,8,16 (2^n);
sum the values. E.g. 1 + 2 + 8 for the user with qualifications on the 1st,2nd and 4th position;
save the sum as binary;
then, if you want to check whether a qualification is possessed by the user, check the binary code and compare. E.g. 1011 is 11. And it shows, that the user has qualifications 1+2+8. And lacks qualification 4, thus there is a 0;

edited Jul 23, 2018 at 11:22

answered Jul 23, 2018 at 9:58

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Surani Matharaarachchi Over a year ago

Is this fulfilling below requirement?

Also bear in mind it doesn't matter if the employee is overqualified for the job, so long as they also have the qualifications required for it.

Vityata Over a year ago

@SuraniMatharaarachchi - it gives the basics only :)

Jake Pierrepont Over a year ago

@Vityata - Thanks for the answer, I tried similar approaches but the problem then comes with dealing with the exception cases where the employee has more qualifications than required, that bit of the formula has so many variables in it that I struggled to wrap my head around it, let alone code it.

Vityata Over a year ago

@JakePierrepont - you can probably use =COUNTIFS() and count whether the cells contain 1. Or =SUMIFS().

Jake Pierrepont Over a year ago

Thanks for the explanation, I was close to giving up :)

|

p._phidot_ · Accepted Answer · 2018-07-23 16:35:11Z

Assuming your sample data starts @ A1. Where A1 = Employee and A5 = Job, you may try :

G1 = Job1
H1 = Job2
F2 = Dave
F3 = Bob

G2  

 =IF((IF(INDEX($B$5:$B$7,MATCH(G$1,$A$5:$A$7,0))=0,1,IF(INDEX($B$1:$B$3,MATCH($F2,$A$1:$A$3,0))*INDEX($B$5:$B$7,MATCH(G$1,$A$5:$A$7,0))=1,1,0))*IF(INDEX($C$5:$C$7,MATCH(G$1,$A$5:$A$7,0))=0,1,IF(INDEX($C$1:$C$3,MATCH($F2,$A$1:$A$3,0))*INDEX($C$5:$C$7,MATCH(G$1,$A$5:$A$7,0))=1,1,0))*IF(INDEX($D$5:$D$7,MATCH(G$1,$A$5:$A$7,0))=0,1,IF(INDEX($D$1:$D$3,MATCH($F2,$A$1:$A$3,0))*INDEX($D$5:$D$7,MATCH(G$1,$A$5:$A$7,0))=1,1,0)))=1,"Qualified","No")

Select G2 and drag until H3. Done.

In index/match, the source/ref range is always adjustable. Set it once, and just drag it.

Hope that helps. (:

Collectives™ on Stack Overflow

Comparing two arrays in excel

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related