• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding a match across two sets of data

S

sana1

New Member
I have two sets of data X (Course) and Y (Student). X shows the list of courses available with 4 criterias which define a course. Y shows the list of students with the same criterias.

Data X

DATA

X 1 2 3 4

A Y N Y Y

B N Y Y Y

C N N Y N

D Y Y Y Y

E Y Y N N


Data Y

DATA

Y 1 2 3 4

a Y Y Y Y

b Y N Y Y

c Y N Y N

d N Y Y Y

e N N Y N

f Y Y N N

g N Y Y N

h Y Y N N


I would like to see which student is mapped to which course depending on the 4 criterias. Eg: a->D. While 1-3 criterias are standard, the criteria 4 is flexible ie if 4 is Y then it would be used for matching else only first 3 criterias will be used.Eg: c has N under 4, so we use only criteria 1-3 for matching and we get c->A. One way is to concatenate these criterias to get an unique string and then lookup but this becomes complicated if we have a lot of data and a large no. of flexible criterias.
 
Hi ,


I have a couple of questions :


1. Will two courses have the same criteria ? If yes , then when matching , which course should be displayed ? The first match , the last match or all matches ?


2. You say that 4 is to be used only if it is a Y ; is this to be applied only when 4 is a Y in the courses or students or both ? What I mean is A has the combination Y N Y Y , while b has Y N Y Y and c has Y N Y N ; I assume that both will map to A. Suppose it were the other way around ; suppose A had the combination Y N Y N , and b and c had their existing combinations ; which of them would map to A ?


Narayan
 
Thanks for responding. Here are the answers:


1) Each of these courses are unique. So, no two courses will have the same combination


2) It is a one to many mapping. One course can be mapped to multiple students, vice versa is not true (# of courses are lesser than # of students).

'What I mean is A has the combination Y N Y Y , while b has Y N Y Y and c has Y N Y N ; I assume that both will map to A': Yes thats right

'Suppose it were the other way around ; suppose A had the combination Y N Y N , and b and c had their existing combinations ; which of them would map to A': c->A, since b has Y under 4, it will be considered when looking for a match.


Let me know if this answers your question.
 
Hi ,


The solution is to create 4 ranges named Criteria_1 , Criteria_2 , Criteria_3 and Criteria_4 , referring to the respective criteria columns , in my worksheet , based on your sample date , I have used E5:E9 , F5:F9 , G5:G9 and H5:H9.


=INDEX(COURSES!$D$5:$D$9,IF(O9="Y",

MATCH(L9&M9&N9&O9,Book17.xls!Criteria_1&Book17.xls!Criteria_2&Book17.xls!Criteria_3&Book17.xls!Criteria_4,0),

MATCH(L9&M9&N9,Book17.xls!Criteria_1&Book17.xls!Criteria_2&Book17.xls!Criteria_3,0)))


Please note that I've split the formula over three lines for ease of reading ; when entering this in Excel , please combine the entire text into one formula. The formula is an array formula , and needs to be entered with CTRL SHIFT ENTER.


The course data is on the sheet labelled COURSES ( D5:H9 ) , and the students data is on the sheet labelled STUDENTS ( K9:O16 ) , while my workbook is named Book17. Change all of these to suit your data.


Narayan
 
Thanks for the solution. This would work. But I have more than 12 criterias out of which 3 are flexible criteria (# might go up in future). In this case the match formula would become very complicated and in case of changes it will become very cumbersome. Is it possible to have a macro or a formula which is easier to amend.
 
Hi ,


With more than 12 criteria , some of them flexible , I think any formula will be a nightmare , unless you have a lot of helper columns to split the formula into manageable chunks.


Better still would be a VBA procedure , but I for one cannot draft a procedure unless you can give complete details of your worksheet such as the data , criteria , rules for the flexibility ,... If you can upload a specimen worksheet , I can work on it.


Narayan
 
Hi, sana1!

Check this:

http://www.2shared.com/file/kZwp-3B_/Finding_a_match_across_two_set.html

Just Alt-F8 and select and run macro "CreateTableZ".

Regards!
 
You must log in or register to reply here.
Back
Top