VBA to use a weighting and rescaling table against information in a separate sheet

[Argh_Work]

Hi All, desperate for help.

I have a scenario that I'd really appreciate a piece of VBA code to run.

I have a table of information in sheet 1. It runs across the rows.
Based on the data in the Score column (Column B) and the Age in the Age column (Column C). I would like the code to select the corresponding row from the Table in sheet 2 and process the information.
For example 1 the score is 1 and the age is 20 therefore the correct row would be 3 in sheet 2
Example 2 the score is 3 and the age is 72 therefore the correct row would be 10 in sheet 2
Example 3 the score is 5 and the age is 29 therefor the correct row would be 21 n sheet 2

Table Sheet 1

I

Table Sheet 2

The Information then needs to be weighted and rescaled
Examples below for the rescaling and weighting based on the table in sheet 2,
Taking the data from sheet one and multiplying it by the weighting sheet 2 and then multiplying it by the rescaling amount

After the process is run it would be amazing if the results could be displayed in a third sheet in the below format

This is well beyond my abilities but I would love a VBA code I could run as it is a becoming a really large data set that I am working with in sheet 1.

Thanks so much for your assistance,

 

[StephenCrump]

Here's one approach, using VBA to populate Excel formulae:

Sub Test()

    Dim N As Long, StartRow1 As Long, StartRow3 As Long
    Dim score As String, wts As String, scl As String, s As String
  
    StartRow1 = 2
    StartRow3 = 2
    N = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row - StartRow1 + 1
    score = "Sheet2!B$2:B$22"
    wts = "Sheet2!D$2:P$22"
    scl = "Sheet2!Q$2:Q$22"
    s = "Match(Sheet1!B" & StartRow1 & "," & score & ",)+Match(Sheet1!C" & StartRow1 & ",{0,18,65})-1"
  
    On Error Resume Next
    Range("MyResults").ClearContents
    On Error GoTo 0
  
    With Worksheets("Sheet3").Range("A" & StartRow3).Resize(N, 3)
        .Columns(1).Formula = "=Sheet1!A" & StartRow1
        .Columns(2).Formula = "=Sheet1!C" & StartRow1
        .Columns(3).Formula = "=Sumproduct(Sheet1!D" & StartRow1 & ":P" & StartRow1 & ",Index(" & wts & "," & s & ",))*Index(" & scl & "," & s & ")"
        .Value = .Value 'optional to convert formulae to values
        .Name = "MyResults"
    End With
  
End Sub

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID	Score	Age	W1	W2	W3	W4	W5	W6	W7	W8	W9	W10	W11	W12	W13
2	Example 1	1	20	0	4	0	0	0	0	4	3	4	1	0	0	0
3	Example 2	3	72	1	1	1	1	1	1	1	1	1	1	2	1	0
4	Example 3	5	29	0	1	0	0	3	0	1	2	1	0	0	0	0
Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Score	Age	W1	W2	W3	W4	W5	W6	W7	W8	W9	W10	W11	W12	W13	Scale
2		1	0
3		1	18	0.1	0.1	0.1	2.5	0.8	9.4	0.1	0.1	0.1	1.2	0.2	0.4	0.0	0.7947
4		1	65
5		2	0
6		2	18
7		2	65
8		3	0
9		3	18
10		3	65	0.1	0.1	0.1	2.3	0.1	4.9	0.1	0.1	0.1	4.1	0.2	0.3	0.0	0.9600
11		4	0
12		4	18
13		4	65
14		7	0
15		7	18
16		7	65
17		9	0
18		9	18
19		9	65
20		5	0
21		5	18	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0000
22		5	65
Sheet2

	A	B	C
1	ID	Age	Weighted score
2	Example 1	20	2.15
3	Example 2	72	12.19
4	Example 3	29	0.00
Sheet3

Cell Formulas
Range	Formula
A2:A4	A2	=Sheet1!A2
B2:B4	B2	=Sheet1!C2
C2:C4	C2	=SUMPRODUCT(Sheet1!D2:P2,INDEX(Sheet2!D$2:P$22,MATCH(Sheet1!B2,Sheet2!B$2:B$22,)+MATCH(Sheet1!C2,{0,18,65})-1,))*INDEX(Sheet2!Q$2:Q$22,MATCH(Sheet1!B2,Sheet2!B$2:B$22,)+MATCH(Sheet1!C2,{0,18,65})-1)

 

[Argh_Work]

@StephenCrump Thank you so much for this. I've done initially testing and it is exactly what I need. I really appreciate your work.

 

[StephenCrump]

I'm glad we could help. Thanks for posting back.