VBA to Categorise based on columns

[Argh_Work]

Hi All,
I was hoping that someone could please help me with some VBA code to automate a process.
I have 2 pages in my spread sheet the first looks like this:

A2	B2	C2	D2	E2
ID.	Type	AGE	Category	Score
669972	1	59	N	3
132125	5	24	Y	9
151515	1	31	N	3
393838	1	65	Y	56
787897	3	23	Y	2

The second contains the below categories.
I'd like to automate the process so the information in sheet 1 is compared to the matrix in sheet 2 and on a third page the Id number is listed with the applicable code.

i.e
In the example of ID 669972 they are a type 1 their age is 59 and they are a N category their score is 3 therefore ID. 669972 = Code 130

In the example of ID 393838 they are a type 1 their age is 65 their category is Y and their score is 2 = ID 393838 Code 136

A2	B2	C2	D2	E2
Type	Age	Category	Score	Code
5	0-17	Y		123
5	18-64	Y		124
5	65+	Y		125
1	0-17	Y	>17	126
1	0-17	Y	<17	127
1	0-17	Y	0	128
1	18-64	N	>14	129
1	18-64	N	<14	130
1	18-64	N	0	131
1	18-64	Y	>14	132
1	18-64	Y	<14	133
1	18-64	Y	0	134
1	65+	Y	>15	135
1	65+	Y	<15	136
1	65+	Y	0	137
2	0-17	Y	>12	138
2	0-17	Y	<12	139
2	0-17	Y	0	140
2	18-64	Y	>10	141
2	18-64	Y	<10	142
2	18-64	Y	0	143
2	65+	Y	>11	144
2	65+	Y	<11	145
2	65+	Y	0	146
3	0-17	Y	>12	147
3	0-17	Y	<12	148
3	0-17	Y	0	149
3	18-64	Y	>11	150
3	18-64	Y	<11	151
3	18-64	Y	0	152
3	65+	Y	>11	153
3	65+	Y	<11	154
3	65+	Y	0	155
4	0-17	Y	>9	156
4	0-17	Y	<9	157
4	0-17	Y	0	158
4	18-64	Y	>8	159
4	18-64	Y	<8	160
4	18-64	Y	0	161
4	65+	Y	>9	162
4	65+	Y	<8	163
4	65+	Y	0	164
9	0-17	Y	0	165
9	18-64	Y	0	166
9	65+	Y	0	167

I'd really appreciate assistance it would make my work life so much easier.
Thanks

 

[Peter_SSs]

1. Are you still using Excel 2013?
   
   
2. Would you be open to slightly changing the layout of the second sheet if that made the process a bit easier?
   
   
3. In the example below, what would be the result if their score was 17? Asking because your table only gives >17 and <17

In the example of ID 669972 they are a type 1 their age is 59 and they are a N category their score is 3 therefore ID. 669972 = Code 130

Similarly, down the bottom of your sheet 2 table you have a score section that shows >9 and <8. That doesn't allow for a score of 8 or 9 or anything in between.

BTW, are scores always whole numbers?

 

[Argh_Work]

Thanks for the response Peter.
I can definitely change the format of the sheets if it is easier.
Yes to excel 2013. (Organisational choice....)
Sorry it should be =<17 etc
We round up to whole numbers.
Thanks so much for considering the problem.
@Peter_SSs

 

[Peter_SSs]

Thanks for the clarifications. My suggestions below may need a bit of tweaking but I think should allow a solution in the end.

First some changes to the second sheet.

1. I have split the Age ranges into two columns. The yellow 200 values could be something else so long as they are at least as large as the greatest age you will ever have in the data. There is no actual need for the original age brackets column (B) but you may choose to keep it anyway.
   
   
2. I have made the Score column into two columns so we can represent the relevant score ranges. The yellow 1000 values could be something else so long as they are at least as large as the greatest score you will ever have in the data. There is no actual need for the original score column (F) but you may choose to keep it anyway. In your sample data some of the score cells are empty or simply equal to zero. I wasn't certain what to do with those rows so they may need changing.

I have also assumed that the 'Code' column contains actual numbers, not numbers stored as text.

Argh_Work.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Type	Age	Age Start	Age End	Category	Score	Score Start	Score End	Code
3	5	0-17	0	17	Y		0	1000	123
4	5	18-64	18	64	Y		0	1000	124
5	5	65+	65	200	Y		0	1000	125
6	1	0-17	0	17	Y	>17	18	1000	126
7	1	0-17	0	17	Y	<17	1	17	127
8	1	0-17	0	17	Y	0	0	0	128
9	1	18-64	18	64	N	>14	15	1000	129
10	1	18-64	18	64	N	<14	1	14	130
11	1	18-64	18	64	N	0	0	0	131
12	1	18-64	18	64	Y	>14	15	1000	132
13	1	18-64	18	64	Y	<14	1	14	133
14	1	18-64	18	64	Y	0	0	0	134
15	1	65+	65	200	Y	>15	16	1000	135
16	1	65+	65	200	Y	<15	1	15	136
17	1	65+	65	200	Y	0	0	0	137
18	2	0-17	0	17	Y	>12	13	1000	138
19	2	0-17	0	17	Y	<12	1	12	139
20	2	0-17	0	17	Y	0	0	0	140
21	2	18-64	18	64	Y	>10	11	1000	141
22	2	18-64	18	64	Y	<10	0	10	142
23	2	18-64	18	64	Y	0	0	0	143
24	2	65+	65	200	Y	>11	12	1000	144
25	2	65+	65	200	Y	<11	1	11	145
26	2	65+	65	200	Y	0	0	0	146
27	3	0-17	0	17	Y	>12	13	1000	147
28	3	0-17	0	17	Y	<12	1	12	148
29	3	0-17	0	17	Y	0	0	0	149
30	3	18-64	18	64	Y	>11	12	1000	150
31	3	18-64	18	64	Y	<11	1	11	151
32	3	18-64	18	64	Y	0	0	0	152
33	3	65+	65	200	Y	>11	12	1000	153
34	3	65+	65	200	Y	<11	1	11	154
35	3	65+	65	200	Y	0	0	0	155
36	4	0-17	0	17	Y	>9	10	1000	156
37	4	0-17	0	17	Y	<9	1	9	157
38	4	0-17	0	17	Y	0	0	0	158
39	4	18-64	18	64	Y	>8	9	1000	159
40	4	18-64	18	64	Y	<8	1	8	160
41	4	18-64	18	64	Y	0	0	0	161
42	4	65+	65	200	Y	>9	10	1000	162
43	4	65+	65	200	Y	<8	1	9	163
44	4	65+	65	200	Y	0	0	0	164
45	9	0-17	0	17	Y	0	0	0	165
46	9	18-64	18	64	Y	0	0	0	166
47	9	65+	65	200	Y	0	0	0	167
Sheet2

Then on the first sheet, try this formula.

Argh_Work.xlsm
	A	B	C	D	E	F
1
2	ID.	Type	AGE	Category	Score	Code
3	669972	1	59	N	3	130
4	132125	5	24	Y	9	124
5	151515	1	31	N	3	130
6	393838	1	65	Y	56	135
7	787897	3	23	Y	2	151
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=SUMPRODUCT(--(B3=Sheet2!A$3:A$47),--(C3>=Sheet2!C$3:C$47),--(C3<=Sheet2!D$3:D$47),--(D3=Sheet2!E$3:E$47),--(E3>=Sheet2!G$3:G$47),--(E3<=Sheet2!H$3:H$47),Sheet2!I$3:I$47)