Speed up excel file (maybe VBA?)

[spanishnick]

Hi,

I have a very large file (100'000+ rows and 40+ columns), each column containing property characteristics. Each property characteristic is assigned a weight on a different sheet (using a combination of Index+Match, i.e. INDEX('Sheet1'!$E$96:$E$143, MATCH(A2, 'Sheet1'!$C$96:$C$143, 0)) and, with this weight, I am estimating the property value by multiplying each weight (so I only have a single column with PRODUCT(INDEX(...), INDEX(...), ....). I would like to give the option for individuals to "play" with the weights and see the impact on total property value, so need the calculations to be updated, and setting manual calculations does not solve the issue as it can still take 2-3 minutes to generate the final output. I was wondering if there was an alternative (i.e. using VBA?).

Thank you

 

[offthelip]

Your problem is similar to Dorians problem on this thread:
Is there a smarter way to do this: 1-Copy Formula >> 2-Paste Formula Down >> 3-Hardcode Pasted Range process I use frequently?
At the end of that thread I posted some code that did the same as Index/Match, but entirely in VBA by using the dictionary object. the code is very fast. The main point about doing it this way is so that you can control exactly what get recalculated and when . I suspect by careful design of what get recalculated at what point by putting it all in VBA, when you are playing around with weights you will get a usable workbook.

 

[spanishnick]

Hi @offthelip,

Thank you for your response and your help. I am very new to VBA (first time using it in fact!) and I'm not sure I was able to edit the code (see beliw)

Sub dictionary()
 ' this does an index match by using the dictionary object
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
  
   Dim outarr(1 To 161, 1 To 1)
   Set Dic = CreateObject("Scripting.dictionary")
   With Sheets("Baseline Weights")
      Ary = .Range("B5", .Range("B" & Rows.Count).End(xlUp).Offset(, 4)).Value2
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 3)
   Next i
  
   For i = 1 To 160
    outarr(i, 1) = Dic(Ary(i, 4))
   Next i
   .Range("au2:au161") = outarr
   End With
  
   End Sub

The data I would like to generate is in columns AJ, AL, AN and AP in sheet "Data2" and column AO in sheet "Data", using weights from "Baseline Weights" sheet (see below a subset of the data). If you could provide me with additional assistance, I would be eternally grateful!

* Sheet Baseline Weights

Test data.xlsx
	B	C	D	E
2	Features	Result	Baseline Weight	Test Weight
3	Coefficient		231,859.13	231,859.13
4	Square Root of Surface Area (in sq ft)
5	Original Use	Domestic	+ 0 %	+ 0 %
6		Bank	+ 106 %	+ 106 %
7		Car Dealership	+ 24 %	+ 24 %
8		Filling Station	+ 53 %	+ 53 %
9		Guesthouse	+ 4 %	+ 4 %
10		Hotel	+ 70 %	+ 70 %
11		Industrial Manufacturing	- 37 %	- 37 %
12		Industrial Warehouse	- 47 %	- 47 %
13		Mixed Retail / Office	+ 13 %	+ 13 %
14		Motor Garage	- 30 %	- 30 %
15		Office	+ 37 %	+ 37 %
16		Private Clinic	+ 72 %	+ 72 %
17		Private School	+ 44 %	+ 44 %
18		Retail	+ 3 %	+ 3 %
19		Supermarket	+ 7 %	+ 7 %
20	Institutional Property Type	Government Offices	+ 79 %	+ 79 %
21		Parliament Buildings	+ 79 %	+ 79 %
22		Police Buildings	+ 70 %	+ 70 %
23		Police Compounds	+ 70 %	+ 70 %
24		Court Buildings	+ 79 %	+ 79 %
25		Municipal Offices	+ 79 %	+ 79 %
26		Army barracks and installations	+ 43 %	+ 43 %
27		Fire Station	+ 43 %	+ 43 %
28	Domestic Ground Use	Yes	+ 0 %	+ 0 %
29		No	+ 5 %	+ 5 %
30	Street Quality	Bad	- 10 %	- 10 %
31		Average	+ 0 %	+ 0 %
32		Good	+ 4 %	+ 4 %
33	Street Lighting	Yes	+ 0 %	+ 0 %
34		No	+ 0 %	+ 0 %
35	Number of Lanes	None	- 15 %	- 15 %
36		One	+ 0 %	+ 0 %
37		Two	+ 2 %	+ 2 %
38		Four	+ 4 %	+ 4 %
39	Street Access	Easy	+ 0 %	+ 0 %
40		Difficult	- 7 %	- 7 %
41	Drainage	Yes	+ 2 %	+ 2 %
42		No	+ 0 %	+ 0 %
43	Features	Beach	+ 24 %	+ 24 %
44		Environmental Hazard	- 15 %	- 15 %
45		Main Road with High Visibility	+ 18 %	+ 18 %
46		Informal Settlement	- 21 %	- 21 %
47		Commercial Corridor	+ 25 %	+ 25 %
48		High-valued Area	+ 0 %	+ 0 %
49	Potential to Build	Yes	+ 3 %	+ 3 %
50		No	+ 0 %	+ 0 %
51	Water	Yes	+ 2 %	+ 2 %
52		No	+ 0 %	+ 0 %
53	Grade of Design	Traditional	+ 0 %	+ 0 %
54		Modern/Architectural	+ 0 %	+ 0 %
55	Wall Material	Low-value material	- 49 %	- 49 %
56		Medium-value material	- 28 %	- 28 %
57		High-value material	+ 0 %	+ 0 %
58	Wall Quality	Bad	- 11 %	- 11 %
59		Average	+ 0 %	+ 0 %
60		Good	+ 25 %	+ 25 %
61	Wall Finish	No finish	+ 0 %	+ 0 %
62		Painted or Whitewashed	+ 0 %	+ 0 %
63		Aluco Bond Metal	+ 0 %	+ 0 %
64		Tile	+ 0 %	+ 0 %
65		Glazed Curtain Wall	+ 0 %	+ 0 %
66		Stone	+ 0 %	+ 0 %
67	Fence Material	No fence	+ 0 %	+ 0 %
68		Low-value material	+ 0 %	+ 0 %
69		Medium-value material	+ 0 %	+ 0 %
70		High-value material	+ 0 %	+ 0 %
71	Fence Condition	No fence	+ 0 %	+ 0 %
72		Bad	+ 0 %	+ 0 %
73		Average	+ 0 %	+ 0 %
74		Good	+ 0 %	+ 0 %
75	Roof Material	Not Visible	+ 0 %	+ 0 %
76		Low-value material	- 21 %	- 21 %
77		High-value material	+ 0 %	+ 0 %
78	Roof Condition	Not Visible	+ 0 %	+ 0 %
79		Bad	- 38 %	- 38 %
80		Average	+ 0 %	+ 0 %
81		Good	+ 20 %	+ 20 %
82	Windows	Low-value material	- 46 %	- 46 %
83		Medium-value material	+ 0 %	+ 0 %
84		High-value material	+ 15 %	+ 15 %
85	Air Condition	Yes	+ 54 %	+ 54 %
86		No	+ 0 %	+ 0 %
87	Security	Yes	+ 43 %	+ 43 %
88		No	+ 0 %	+ 0 %
89	Garage	Yes	+ 0 %	+ 0 %
90		No	+ 0 %	+ 0 %
91	Outbuilding	Yes	+ 10 %	+ 10 %
92		No	+ 0 %	+ 0 %
93	Pool	Yes	+ 101 %	+ 101 %
94		No	+ 0 %	+ 0 %
95	Veranda	Yes	+ 3 %	+ 3 %
96		No	+ 0 %	+ 0 %
97	Location (Ward)	399	+ 0 %	+ 0 %
98		400	+ 10 %	+ 10 %
99		401	- 3 %	- 3 %
100		402	+ 27 %	+ 27 %
101		403	+ 3 %	+ 3 %
102		404	- 16 %	- 16 %
103		405	- 6 %	- 6 %
104		406	- 5 %	- 5 %
105		407	+ 7 %	+ 7 %
106		408	+ 10 %	+ 10 %
107		409	- 9 %	- 9 %
108		410	- 14 %	- 14 %
109		411	- 35 %	- 35 %
110		412	+ 22 %	+ 22 %
111		413	+ 11 %	+ 11 %
112		414	+ 52 %	+ 52 %
113		415	+ 45 %	+ 45 %
114		416	+ 15 %	+ 15 %
115		417	+ 56 %	+ 56 %
116		418	+ 0 %	+ 0 %
117		419	+ 5 %	+ 5 %
118		420	+ 68 %	+ 68 %
119		421	+ 106 %	+ 106 %
120		422	+ 60 %	+ 60 %
121		423	+ 36 %	+ 36 %
122		424	+ 38 %	+ 38 %
123		425	+ 78 %	+ 78 %
124		426	+ 18 %	+ 18 %
125		427	+ 240 %	+ 240 %
126		428	+ 189 %	+ 189 %
127		429	+ 192 %	+ 192 %
128		430	+ 231 %	+ 231 %
129		431	+ 233 %	+ 233 %
130		432	+ 139 %	+ 139 %
131		433	+ 176 %	+ 176 %
132		434	+ 26 %	+ 26 %
133		435	+ 71 %	+ 71 %
134		436	+ 237 %	+ 237 %
135		437	+ 178 %	+ 178 %
136		438	+ 196 %	+ 196 %
137		439	+ 62 %	+ 62 %
138		440	+ 120 %	+ 120 %
139		441	+ 123 %	+ 123 %
140		442	+ 126 %	+ 126 %
141		443	+ 129 %	+ 129 %
142		444	+ 98 %	+ 98 %
143		445	+ 41 %	+ 41 %
144		446	+ 180 %	+ 180 %
145	Special Properties	Bollore Office	+ 3819 %	+ 3819 %
146		Bollore Warehouse	+ 1426 %	+ 1426 %
147		Bollore Garage	+ 1894 %	+ 1894 %
148		Dangote Office	+ 346 %	+ 346 %
149		Dangote Warehouse	+ 73 %	+ 73 %
150		SLPA Warehouse	+ 119 %	+ 119 %
151		SLPA Office	+ 461 %	+ 461 %
152		SLPA House Form	+ 310 %	+ 310 %
153		SLPA Industrial	+ 158 %	+ 158 %
154		SLPA Motor Garage	+ 186 %	+ 186 %
155		SLPA Canteen	+ 322 %	+ 322 %
156		Nectar House Form	+ 174 %	+ 174 %
157		Nectar Warehouse	+ 46 %	+ 46 %
158		Nectar Cargo	+ 182 %	+ 182 %
159		Nectar Office	+ 275 %	+ 275 %
160		Leocem Industrial Manufacturing	- 37 %	- 37 %
161		Leocem Office	+ 37 %	+ 37 %
Baseline Weights

Cell Formulas
Range	Formula
E5:E161	E5	=D5

* Sheet Data

Test data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1	ward	zone_clean	marker2	surface_area	original_use	domestic_use	road_status	street_lighting	number_lanes	street_access	has_drainage	beach	hazard	road_visib	informal_settlement	commercial_corridor	high_valued_area	high_valued_area_name	has_potential_to_build	has_water	grade_of_design	wall_category	state_of_repair	wall_finish	fence_category	fence_condition	roof_category	roof_repair	windows_category	airconditioning	security	owns_garage	pool	owns_outbuilding	veranda	annual_rent	ln_annual_rent	model2_exp	test_exp	model2_error	test_exp	test_error
2	399	1	1	6865.98	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	Yes	Modern/Architectural	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	Yes	No	Yes	No	No	Yes	32500000	17.2968	32216356	39,267,161.39	1.22	39,267,162	1.21
3	399	1	2	1628.53	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	High-value material	Average	No finish	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	4500000	15.3196	6838487.5	7,057,747.79	1.66	7,057,748	1.57
4	399	1	3	1764.73	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	High-value material	Average	No finish	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	Yes	Yes	3250000	14.9942	6871429.5	8,081,650.52	2.25	8,081,651	2.49
5	399	1	4	1702.31	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	High-value material	Average	Low-value material	Good	High-value material	No	No	No	No	No	Yes	7250000	15.7965	10458102	9,957,874.95	1.39	9,957,875	1.37
6	399	1	5	380.42	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	Low-value material	Average	No finish	No fence	No fence	Low-value material	Average	Low-value material	No	No	No	No	No	No	400000	12.8992	877425.8125	912,066.78	2.36	912,067	2.28
7	399	1	6	2709.53	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	No	No	No	No	Yes	Yes	8250000	15.9257	11832942	13,819,330.54	1.69	13,819,331	1.68
8	399	1	7	1821.36	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	7375000	15.8136	7232029	7,463,905.71	1.02	7,463,906	1.01
9	399	1	8	2967.13	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	Yes	Traditional	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	No	No	No	No	No	Yes	10500000	16.1669	16730881	16,761,998.55	1.37	16,761,999	1.60
10	399	1	9	1354.39	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No		Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	8125000	15.9105	6236402.5	6,436,356.94	0.80	6,436,357	0.79
Data

Cell Formulas
Range	Formula
AO2:AO10	AO2	=PRODUCT((INDEX('Baseline Weights'!$E$97:$E$144,MATCH(A2,'Baseline Weights'!$C$97:$C$144,0))+1), (INDEX('Baseline Weights'!$E$28:$E$29,MATCH(F2,'Baseline Weights'!$C$28:$C$29,0))+1), (INDEX('Baseline Weights'!$E$30:$E$32,MATCH(G2,'Baseline Weights'!$C$30:$C$32,0))+1), (IF(ISNA(INDEX('Baseline Weights'!$E$33:$E$34,MATCH(H2,'Baseline Weights'!$C$33:$C$34,0))),1,INDEX('Baseline Weights'!$E$33:$E$34,MATCH(H2,'Baseline Weights'!$C$33:$C$34,0)))+1), (INDEX('Baseline Weights'!$E$35:$E$38,MATCH(I2,'Baseline Weights'!$C$35:$C$38,0))+1), (INDEX('Baseline Weights'!$E$39:$E$40,MATCH(J2,'Baseline Weights'!$C$39:$C$40,0))+1), (INDEX('Baseline Weights'!$E$41:$E$42,MATCH(K2,'Baseline Weights'!$C$41:$C$42,0))+1), IF(L2="Yes",'Baseline Weights'!$D$43+1,1), IF(M2="Yes",'Baseline Weights'!$D$44+1,1), IF(N2="Yes",'Baseline Weights'!$D$45+1,1), IF(O2="Yes",'Baseline Weights'!$D$46+1,1), IF(P2="Yes",'Baseline Weights'!$D$47+1,1), IF(Q2="Yes",'Baseline Weights'!$D$48+1,1), (INDEX('Baseline Weights'!$E$49:$E$50, MATCH(S2, 'Baseline Weights'!$C$49:$C$50, 0))+1), (INDEX('Baseline Weights'!$E$51:$E$52, MATCH(T2, 'Baseline Weights'!$C$51:$C$52, 0))+1), IF(ISNA(INDEX('Baseline Weights'!$E$53:$E$54, MATCH(U2, 'Baseline Weights'!$C$53:$C$54, 0))), 1, INDEX('Baseline Weights'!$E$53:$E$54, MATCH(U2, 'Baseline Weights'!$C$53:$C$54, 0))+1), (INDEX('Baseline Weights'!$E$55:$E$57, MATCH(V2, 'Baseline Weights'!$C$55:$C$57, 0))+1), (INDEX('Baseline Weights'!$E$58:$E$60, MATCH(W2, 'Baseline Weights'!$C$58:$C$60, 0))+1), (INDEX('Baseline Weights'!$E$61:$E$66, MATCH(X2, 'Baseline Weights'!$C$61:$C$66, 0))+1), (INDEX('Baseline Weights'!$E$67:$E$70, MATCH(Y2, 'Baseline Weights'!$C$67:$C$70, 0))+1), (INDEX('Baseline Weights'!$E$71:$E$74, MATCH(Z2, 'Baseline Weights'!$C$71:$C$74, 0))+1), (INDEX('Baseline Weights'!$E$71:$E$74, MATCH(Z2, 'Baseline Weights'!$C$71:$C$74, 0))+1), (INDEX('Baseline Weights'!$E$75:$E$77, MATCH(AA2, 'Baseline Weights'!$C$75:$C$77, 0))+1), (INDEX('Baseline Weights'!$E$78:$E$81, MATCH(AB2, 'Baseline Weights'!$C$78:$C$81, 0))+1), (INDEX('Baseline Weights'!$E$82:$E$84, MATCH(AC2, 'Baseline Weights'!$C$82:$C$84, 0))+1), (INDEX('Baseline Weights'!$E$85:$E$86, MATCH(AD2, 'Baseline Weights'!$C$85:$C$86, 0))+1), (INDEX('Baseline Weights'!$E$87:$E$88, MATCH(AE2, 'Baseline Weights'!$C$87:$C$88, 0))+1), (IF(ISNA(INDEX('Baseline Weights'!$E$89:$E$90, MATCH(AF2, 'Baseline Weights'!$C$89:$C$90, 0))), 1, INDEX('Baseline Weights'!$E$89:$E$90, MATCH(AF2, 'Baseline Weights'!$C$89:$C$90, 0)))+1), (INDEX('Baseline Weights'!$E$93:$E$94, MATCH(AG2, 'Baseline Weights'!$C$93:$C$94, 0))+1), (INDEX('Baseline Weights'!$E$91:$E$92, MATCH(AH2, 'Baseline Weights'!$C$91:$C$92, 0))+1), (INDEX('Baseline Weights'!$E$95:$E$96, MATCH(AI2, 'Baseline Weights'!$C$95:$C$96, 0))+1), SQRT(D2), 'Baseline Weights'!$D$3)
AP2:AP10	AP2	=AO2/AJ2

 

[spanishnick]

* Sheet Data2

Test data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	ward	original_use	institutional_type	domestic_use	road_status	street_lighting	number_lanes	street_access	has_drainage	beach	hazard	road_visib	informal_settlement	commercial_corridor	high_valued_area	has_potential_to_build	has_water	grade_of_design	wall_category	state_of_repair	wall_finish	fence_category	fence_condition	roof_category	roof_repair	windows_category	airconditioning	security	owns_garage	pool	owns_outbuilding	veranda	comm_surface_area	dom_surface_area	inst_surface_area	base	commercial	test_commercial	domestic	test_domestic	institutional	test_institutional	test_property	quintile	test_property	test_quintile
2	432			Yes	Average	No	One	Easy	Yes	No	No	No	No	No	No	No	Yes	Traditional	High-value material	Average	No finish	Low-value material	Bad	Low-value material	Average	Medium-value material	No	No	No	No	Yes	Yes		319.26		516,035.34	-	-	9,220,441.12	9,220,441.19	-	-	9,220,441.12	4	9,220,441.19	4
3	418			Yes	Bad	No	None	Difficult	No	No	No	No	No	No	No	No	No	Traditional	Low-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	Low-value material	No	No	No	No	Yes	Yes		227.24		60,993.11	-	-	919,439.52	919,439.53	-	-	919,439.52	1	919,439.53	1
4	419			Yes	Bad	No	None	Difficult	No	No	No	No	No	No	No	Yes	No	Traditional	Medium-value material	Average	No finish	No fence	No fence	Low-value material	Good	Low-value material	No	No	No	No	No	Yes		627.85		67,727.79	-	-	1,697,050.92	1,697,050.93	-	-	1,697,050.92	1	1,697,050.93	1
5	446			Yes	Average	No	One	Difficult	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	No	No	No	No	No	Yes		2079.69		872,882.57	-	-	39,806,603.28	39,806,603.57	-	-	39,806,603.28	5	39,806,603.57	5
6	433			Yes	Average	No	One	Easy	Yes	No	No	No	No	No	No	No	Yes	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Average	Low-value material	No	No	No	No	Yes	No		219.82		312,426.00	-	-	4,632,130.25	4,632,130.28	-	-	4,632,130.25	3	4,632,130.28	3
7	400			Yes	Average	No	One	Easy	Yes	No	No	No	No	No	No	Yes	Yes	Traditional	High-value material	Average	Painted or Whitewashed	High-value material	Average	Low-value material	Average	High-value material	No	Yes	No	No	No	Yes		2908.71		365,723.32	-	-	19,724,363.94	19,724,364.09	-	-	19,724,363.94	5	19,724,364.09	5
8	407	Domestic		Yes	Bad	No	One	Easy	Yes	No	No	No	No	No	No	No	Yes	Traditional	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	Medium-value material	No	No	No	No	No	Yes	335.89	2351.23		283,534.82	5,196,428.33	5,196,428.37	13,748,457.06	13,748,457.16	-	-	18,944,885.39	5	18,944,885.53	5
9	443			Yes	Average	No	None	Easy	No	No	No	No	No	No	No	Yes	Yes	Traditional	High-value material	Average	Painted or Whitewashed	High-value material	Average	Low-value material	Average	Medium-value material	No	Yes	No	No	No	Yes		1072.11		551,716.99	-	-	18,064,919.48	18,064,919.61	-	-	18,064,919.48	5	18,064,919.61	5
10	408			Yes	Bad	No	None	Difficult	No	No	Yes	No	Yes	No	No	No	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes		700.48		99,145.18	-	-	2,624,034.08	2,624,034.10	-	-	2,624,034.08	2	2,624,034.10	2
Data2

Cell Formulas
Range	Formula
AR2:AR10,AT2:AT10	AR2	=IF(AQ2<=PERCENTILE.INC(AQ:AQ,0.2),1,IF(AQ2<=PERCENTILE.INC(AQ:AQ,0.4),2,IF(AQ2<=PERCENTILE.INC(AQ:AQ,0.6),3,IF(AQ2<=PERCENTILE.INC(AQ:AQ,0.8),4,5))))
AS2:AS10	AS2	=AL2+AN2+AP2
AJ2:AJ10	AJ2	=PRODUCT((INDEX('Baseline Weights'!$E$97:$E$144,MATCH(A2,'Baseline Weights'!$C$97:$C$144,0))+1), (INDEX('Baseline Weights'!$E$28:$E$29,MATCH(D2,'Baseline Weights'!$C$28:$C$29,0))+1), (INDEX('Baseline Weights'!$E$30:$E$32,MATCH(E2,'Baseline Weights'!$C$30:$C$32,0))+1), (IF(ISNA(INDEX('Baseline Weights'!$E$33:$E$34,MATCH(F2,'Baseline Weights'!$C$33:$C$34,0))),1,INDEX('Baseline Weights'!$E$33:$E$34,MATCH(F2,'Baseline Weights'!$C$33:$C$34,0)))+1), (INDEX('Baseline Weights'!$E$35:$E$38,MATCH(G2,'Baseline Weights'!$C$35:$C$38,0))+1), (INDEX('Baseline Weights'!$E$39:$E$40,MATCH(H2,'Baseline Weights'!$C$39:$C$40,0))+1), (INDEX('Baseline Weights'!$E$41:$E$42,MATCH(I2,'Baseline Weights'!$C$41:$C$42,0))+1), IF(J2="Yes",'Baseline Weights'!$D$43+1,1), IF(K2="Yes",'Baseline Weights'!$D$44+1,1), IF(L2="Yes",'Baseline Weights'!$D$45+1,1), IF(M2="Yes",'Baseline Weights'!$D$46+1,1), IF(N2="Yes",'Baseline Weights'!$D$47+1,1), IF(O2="Yes",'Baseline Weights'!$D$48+1,1), (INDEX('Baseline Weights'!$E$49:$E$50, MATCH(P2, 'Baseline Weights'!$C$49:$C$50, 0))+1), (INDEX('Baseline Weights'!$E$51:$E$52, MATCH(Q2, 'Baseline Weights'!$C$51:$C$52, 0))+1), IF(ISNA(INDEX('Baseline Weights'!$E$53:$E$54, MATCH(R2, 'Baseline Weights'!$C$53:$C$54, 0))), 1, INDEX('Baseline Weights'!$E$53:$E$54, MATCH(R2, 'Baseline Weights'!$C$53:$C$54, 0))+1), (INDEX('Baseline Weights'!$E$55:$E$57, MATCH(S2, 'Baseline Weights'!$C$55:$C$57, 0))+1),(INDEX('Baseline Weights'!$E$58:$E$60, MATCH(T2, 'Baseline Weights'!$C$58:$C$60, 0))+1),(INDEX('Baseline Weights'!$E$61:$E$66, MATCH(U2, 'Baseline Weights'!$C$61:$C$66, 0))+1),(INDEX('Baseline Weights'!$E$67:$E$70, MATCH(V2, 'Baseline Weights'!$C$67:$C$70, 0))+1),(INDEX('Baseline Weights'!$E$71:$E$74, MATCH(W2, 'Baseline Weights'!$C$71:$C$74, 0))+1),(INDEX('Baseline Weights'!$E$71:$E$74, MATCH(W2, 'Baseline Weights'!$C$71:$C$74, 0))+1),(INDEX('Baseline Weights'!$E$75:$E$77, MATCH(X2, 'Baseline Weights'!$C$75:$C$77, 0))+1),(INDEX('Baseline Weights'!$E$78:$E$81, MATCH(Y2, 'Baseline Weights'!$C$78:$C$81, 0))+1),(INDEX('Baseline Weights'!$E$82:$E$84, MATCH(Z2, 'Baseline Weights'!$C$82:$C$84, 0))+1),(INDEX('Baseline Weights'!$E$85:$E$86, MATCH(AA2, 'Baseline Weights'!$C$85:$C$86, 0))+1),(INDEX('Baseline Weights'!$E$87:$E$88, MATCH(AB2, 'Baseline Weights'!$C$87:$C$88, 0))+1),(IF(ISNA(INDEX('Baseline Weights'!$E$89:$E$90, MATCH(AC2, 'Baseline Weights'!$C$89:$C$90, 0))), 1, INDEX('Baseline Weights'!$E$89:$E$90, MATCH(AC2, 'Baseline Weights'!$C$89:$C$90, 0)))+1),(INDEX('Baseline Weights'!$E$93:$E$94, MATCH(AD2, 'Baseline Weights'!$C$93:$C$94, 0))+1),(INDEX('Baseline Weights'!$E$91:$E$92, MATCH(AE2, 'Baseline Weights'!$C$91:$C$92, 0))+1),(INDEX('Baseline Weights'!$E$95:$E$96, MATCH(AF2, 'Baseline Weights'!$C$95:$C$96, 0))+1),'Baseline Weights'!$D$3)
AL2:AL10	AL2	=AJ2*SQRT(AG2)*IF(ISNA(VLOOKUP(B2, 'Baseline Weights'!C$5:E$19, 3, FALSE)), 1, VLOOKUP(B2, 'Baseline Weights'!C$5:E$19, 3, FALSE)+1)
AN2:AN10	AN2	=AJ2*SQRT(AH2)
AP2	AP2	=AJ2*SQRT(AI2)*'Baseline Weights'!$D$3*IF(ISNA(VLOOKUP(C2, 'Baseline Weights'!C$20:E$27, 3, FALSE)), IF(ISNA(VLOOKUP(C2, 'Baseline Weights'!C145:E161, 3, FALSE)), 1, VLOOKUP(C2, 'Baseline Weights'!C145:E161, 3, FALSE)+1), VLOOKUP(C2, 'Baseline Weights'!C$20:E$27, 3, FALSE)+1)
AP3:AP10	AP3	=(INDEX('Baseline Weights'!$E$97:$E$144,MATCH(A3,'Baseline Weights'!$C$97:$C$144,0))+1)*(INDEX('Baseline Weights'!$E$28:$E$29,MATCH(D3,'Baseline Weights'!$C$28:$C$29,0))+1)*(INDEX('Baseline Weights'!$E$30:$E$32,MATCH(E3,'Baseline Weights'!$C$30:$C$32,0))+1)*(IF(ISNA(INDEX('Baseline Weights'!$E$33:$E$34,MATCH(F3,'Baseline Weights'!$C$33:$C$34,0))),1,INDEX('Baseline Weights'!$E$33:$E$34,MATCH(F3,'Baseline Weights'!$C$33:$C$34,0)))+1)*(INDEX('Baseline Weights'!$E$35:$E$38,MATCH(G3,'Baseline Weights'!$C$35:$C$38,0))+1)*(INDEX('Baseline Weights'!$E$39:$E$40,MATCH(H3,'Baseline Weights'!$C$39:$C$40,0))+1)* (INDEX('Baseline Weights'!$E$41:$E$42,MATCH(I3,'Baseline Weights'!$C$41:$C$42,0))+1)*IF(J3="Yes",'Baseline Weights'!$D$43+1,1)*IF(K3="Yes",'Baseline Weights'!$D$44+1,1)*IF(L3="Yes",'Baseline Weights'!$D$45+1,1)*IF(M3="Yes",'Baseline Weights'!$D$46+1,1)*IF(N3="Yes",'Baseline Weights'!$D$47+1,1)*IF(O3="Yes",'Baseline Weights'!$D$48+1,1)*(INDEX('Baseline Weights'!$E$49:$E$50, MATCH(P3, 'Baseline Weights'!$C$49:$C$50, 0))+1)*(INDEX('Baseline Weights'!$E$51:$E$52, MATCH(Q3, 'Baseline Weights'!$C$51:$C$52, 0))+1)*IF(ISNA(INDEX('Baseline Weights'!$E$53:$E$54, MATCH(R3, 'Baseline Weights'!$C$53:$C$54, 0))), 1, INDEX('Baseline Weights'!$E$53:$E$54, MATCH(R3, 'Baseline Weights'!$C$53:$C$54, 0))+1)*(INDEX('Baseline Weights'!$E$55:$E$57, MATCH(S3, 'Baseline Weights'!$C$55:$C$57, 0))+1)*(INDEX('Baseline Weights'!$E$58:$E$60, MATCH(T3, 'Baseline Weights'!$C$58:$C$60, 0))+1)*(INDEX('Baseline Weights'!$E$61:$E$66, MATCH(U3, 'Baseline Weights'!$C$61:$C$66, 0))+1)*(INDEX('Baseline Weights'!$E$67:$E$70, MATCH(V3, 'Baseline Weights'!$C$67:$C$70, 0))+1)*(INDEX('Baseline Weights'!$E$71:$E$74, MATCH(W3, 'Baseline Weights'!$C$71:$C$74, 0))+1)*(INDEX('Baseline Weights'!$E$71:$E$74, MATCH(W3, 'Baseline Weights'!$C$71:$C$74, 0))+1)*(INDEX('Baseline Weights'!$E$75:$E$77, MATCH(X3, 'Baseline Weights'!$C$75:$C$77, 0))+1)*(INDEX('Baseline Weights'!$E$78:$E$81, MATCH(Y3, 'Baseline Weights'!$C$78:$C$81, 0))+1)*(INDEX('Baseline Weights'!$E$82:$E$84, MATCH(Z3, 'Baseline Weights'!$C$82:$C$84, 0))+1)*(INDEX('Baseline Weights'!$E$85:$E$86, MATCH(AA3, 'Baseline Weights'!$C$85:$C$86, 0))+1)*(INDEX('Baseline Weights'!$E$87:$E$88, MATCH(AB3, 'Baseline Weights'!$C$87:$C$88, 0))+1)*(IF(ISNA(INDEX('Baseline Weights'!$E$89:$E$90, MATCH(AC3, 'Baseline Weights'!$C$89:$C$90, 0))), 1, INDEX('Baseline Weights'!$E$89:$E$90, MATCH(AC3, 'Baseline Weights'!$C$89:$C$90, 0)))+1)*(INDEX('Baseline Weights'!$E$93:$E$94, MATCH(AD3, 'Baseline Weights'!$C$93:$C$94, 0))+1)*(INDEX('Baseline Weights'!$E$91:$E$92, MATCH(AE3, 'Baseline Weights'!$C$91:$C$92, 0))+1)*(INDEX('Baseline Weights'!$E$95:$E$96, MATCH(AF3, 'Baseline Weights'!$C$95:$C$96, 0))+1)*SQRT(AI3)*'Baseline Weights'!$D$3*IF(ISNA(VLOOKUP(C3, 'Baseline Weights'!C$20:E$27, 3, FALSE)), IF(ISNA(VLOOKUP(C3, 'Baseline Weights'!C146:E162, 3, FALSE)), 1, VLOOKUP(C3, 'Baseline Weights'!C146:E162, 3, FALSE)+1), VLOOKUP(C3, 'Baseline Weights'!C$20:E$27, 3, FALSE)+1)

 

[offthelip]

This is quite possible to do using the code that I have written as above.
However it would make the code much much simpler if the names in the headers in the Data2 sheet were the same as the categories in column A of sheet data. as an example the header in column A of sheet data2 is "ward" however the group of rows the match is done from is rows 97 to 144 which have "Location (Ward)" in column A of the Baseline weights sheet.
what I am thinking is loading into one dictionary all the values from the baseline sheets with the index being the concatenation of columns A and B for the baseline weights, then to lookup any value we just lookup in the dictionary with the input being the concatenation of the header and row value from sheet data2. If it is not possible to do this, then we can put in and extra lookup table to convert the header to the column A value. Using multiple dictionaries is probably going to make the code very slow.
Ideally it would be much easier and slightly faster if the "merged" cells in column A were not merged but the category was repeated on each row, Is this possible??

 

[spanishnick]

Hi @offthelip ,

Thank you for your suggestion. I can definitely change the names of the headers (i.e. "Location (Ward)" instead of "ward"). For the rest of your suggestions, unfortunately I don't think my skills in VBA (first time using it) would allow me to do it successfully. I am currently doing it my steps, by concatenating the column name variable with the different options in sheet "Baseline Weights" (column B), and use index-match to display the weights in column F in column AP of sheet "Data". The either would be to loop over each column to generate weights for each characteristics, then have a final column as a product of all previous columns (and then, later attempt to find a way to combine all computations into a single column). However, I am already struggling to complete the first step (see code below). Any help would be greatly appreciated.

Sub indexandmatchexample()

Dim weightsWs As Worksheet, dataWs As Worksheet
Dim weightLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, MatchRng As Range
Dim StringOne As String

Set weightsWs = ThisWorkbook.Worksheets("Baseline Weights")
Set dataWs = ThisWorkbook.Worksheets("Data")

weightLastRow = weightsWs.Range("B" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row

Set IndexRng = weightsWs.Range("B2:B" & dataLastRow)
Set MatchRng = weightsWs.Range("F2:F" & dataLastRow)

For x = 2 To weightLastRow
    
    On Error Resume Next
    
    StringOne = Range("A1").Value
    
    dataWs.Range("AP" & x).Value = Application.WorksheetFunction.Index( _
        IndexRng, _
        Application.WorksheetFunction.Match(dataWs.Range("A" & x).Value, StringOne & MatchRng, 0))

Next x

End Sub

* Baseline Weights sheet

Test.xlsm
	B	C	D	E	F
2		Features	Result	Baseline Weight	Test Weight
3		Coefficient		231,859.13	231,859.13
4		Square Root of Surface Area (in sq ft)
5	original_useDomestic	Original Use	Domestic	+ 0 %	+ 0 %
6	original_useBank		Bank	+ 106 %	+ 106 %
7	original_useCar Dealership		Car Dealership	+ 24 %	+ 24 %
8	original_useFilling Station		Filling Station	+ 53 %	+ 53 %
9	original_useGuesthouse		Guesthouse	+ 4 %	+ 4 %
10	original_useHotel		Hotel	+ 70 %	+ 70 %
11	original_useIndustrial Manufacturing		Industrial Manufacturing	- 37 %	- 37 %
12	original_useIndustrial Warehouse		Industrial Warehouse	- 47 %	- 47 %
13	original_useMixed Retail / Office		Mixed Retail / Office	+ 13 %	+ 13 %
14	original_useMotor Garage		Motor Garage	- 30 %	- 30 %
15	original_useOffice		Office	+ 37 %	+ 37 %
16	original_usePrivate Clinic		Private Clinic	+ 72 %	+ 72 %
17	original_usePrivate School		Private School	+ 44 %	+ 44 %
18	original_useRetail		Retail	+ 3 %	+ 3 %
19	original_useSupermarket		Supermarket	+ 7 %	+ 7 %
20	institutional_typeGovernment Offices	Institutional Property Type	Government Offices	+ 79 %	+ 79 %
21	institutional_typeParliament Buildings		Parliament Buildings	+ 79 %	+ 79 %
22	institutional_typePolice Buildings		Police Buildings	+ 70 %	+ 70 %
23	institutional_typePolice Compounds		Police Compounds	+ 70 %	+ 70 %
24	institutional_typeCourt Buildings		Court Buildings	+ 79 %	+ 79 %
25	institutional_typeMunicipal Offices		Municipal Offices	+ 79 %	+ 79 %
26	institutional_typeArmy barracks and installations		Army barracks and installations	+ 43 %	+ 43 %
27	institutional_typeFire Station		Fire Station	+ 43 %	+ 43 %
28	domestic_useYes	Domestic Ground Use	Yes	+ 0 %	+ 0 %
29	domestic_useNo		No	+ 5 %	+ 5 %
30	road_statusBad	Street Quality	Bad	- 10 %	- 10 %
31	road_statusAverage		Average	+ 0 %	+ 0 %
32	road_statusGood		Good	+ 4 %	+ 4 %
33	street_lightingYes	Street Lighting	Yes	+ 0 %	+ 0 %
34	street_lightingNo		No	+ 0 %	+ 0 %
35	number_lanesNone	Number of Lanes	None	- 15 %	- 15 %
36	number_lanesOne		One	+ 0 %	+ 0 %
37	number_lanesTwo		Two	+ 2 %	+ 2 %
38	number_lanesFour		Four	+ 4 %	+ 4 %
39	street_accessEasy	Street Access	Easy	+ 0 %	+ 0 %
40	street_accessDifficult		Difficult	- 7 %	- 7 %
41	has_drainageYes	Drainage	Yes	+ 2 %	+ 2 %
42	has_drainageNo		No	+ 0 %	+ 0 %
43	beachYes	Features	Beach	+ 24 %	+ 24 %
44	hazardYes		Environmental Hazard	- 15 %	- 15 %
45	road_visibYes		Main Road with High Visibility	+ 18 %	+ 18 %
46	informal_settlementYes		Informal Settlement	- 21 %	- 21 %
47	commercial_corridorYes		Commercial Corridor	+ 25 %	+ 25 %
48	high_valued_areaYes		High-valued Area	+ 0 %	+ 0 %
49	has_potential_to_buildYes	Potential to Build	Yes	+ 3 %	+ 3 %
50	has_potential_to_buildNo		No	+ 0 %	+ 0 %
51	has_waterYes	Water	Yes	+ 2 %	+ 2 %
52	has_waterNo		No	+ 0 %	+ 0 %
53	grade_of_designTraditional	Grade of Design	Traditional	+ 0 %	+ 0 %
54	grade_of_designModern/Architectural		Modern/Architectural	+ 0 %	+ 0 %
55	wall_categoryLow-value material	Wall Material	Low-value material	- 49 %	- 49 %
56	wall_categoryMedium-value material		Medium-value material	- 28 %	- 28 %
57	wall_categoryHigh-value material		High-value material	+ 0 %	+ 0 %
58	state_of_repairBad	Wall Quality	Bad	- 11 %	- 11 %
59	state_of_repairAverage		Average	+ 0 %	+ 0 %
60	state_of_repairGood		Good	+ 25 %	+ 25 %
61	wall_finishNo finish	Wall Finish	No finish	+ 0 %	+ 0 %
62	wall_finishPainted or Whitewashed		Painted or Whitewashed	+ 0 %	+ 0 %
63	wall_finishAluco Bond Metal		Aluco Bond Metal	+ 0 %	+ 0 %
64	wall_finishTile		Tile	+ 0 %	+ 0 %
65	wall_finishGlazed Curtain Wall		Glazed Curtain Wall	+ 0 %	+ 0 %
66	wall_finishStone		Stone	+ 0 %	+ 0 %
67	fence_categoryNo fence	Fence Material	No fence	+ 0 %	+ 0 %
68	fence_categoryLow-value material		Low-value material	+ 0 %	+ 0 %
69	fence_categoryMedium-value material		Medium-value material	+ 0 %	+ 0 %
70	fence_categoryHigh-value material		High-value material	+ 0 %	+ 0 %
71	fence_conditionNo fence	Fence Condition	No fence	+ 0 %	+ 0 %
72	fence_conditionBad		Bad	+ 0 %	+ 0 %
73	fence_conditionAverage		Average	+ 0 %	+ 0 %
74	fence_conditionGood		Good	+ 0 %	+ 0 %
75	roof_categoryNot Visible	Roof Material	Not Visible	+ 0 %	+ 0 %
76	roof_categoryLow-value material		Low-value material	- 21 %	- 21 %
77	roof_categoryHigh-value material		High-value material	+ 0 %	+ 0 %
78	roof_repairNot Visible	Roof Condition	Not Visible	+ 0 %	+ 0 %
79	roof_repairBad		Bad	- 38 %	- 38 %
80	roof_repairAverage		Average	+ 0 %	+ 0 %
81	roof_repairGood		Good	+ 20 %	+ 20 %
82	windows_categoryLow-value material	Windows	Low-value material	- 46 %	- 46 %
83	windows_categoryMedium-value material		Medium-value material	+ 0 %	+ 0 %
84	windows_categoryHigh-value material		High-value material	+ 15 %	+ 15 %
85	airconditioningYes	Air Condition	Yes	+ 54 %	+ 54 %
86	airconditioningNo		No	+ 0 %	+ 0 %
87	securityYes	Security	Yes	+ 43 %	+ 43 %
88	securityNo		No	+ 0 %	+ 0 %
89	owns_garageYes	Garage	Yes	+ 0 %	+ 0 %
90	owns_garageNo		No	+ 0 %	+ 0 %
91	owns_outbuildingYes	Outbuilding	Yes	+ 10 %	+ 10 %
92	owns_outbuildingNo		No	+ 0 %	+ 0 %
93	poolYes	Pool	Yes	+ 101 %	+ 101 %
94	poolNo		No	+ 0 %	+ 0 %
95	verandaYes	Veranda	Yes	+ 3 %	+ 3 %
96	verandaNo		No	+ 0 %	+ 0 %
97	ward399	Location (Ward)	399	+ 0 %	+ 0 %
98	ward400		400	+ 10 %	+ 10 %
99	ward401		401	- 3 %	- 3 %
100	ward402		402	+ 27 %	+ 27 %
101	ward403		403	+ 3 %	+ 3 %
102	ward404		404	- 16 %	- 16 %
103	ward405		405	- 6 %	- 6 %
104	ward406		406	- 5 %	- 5 %
105	ward407		407	+ 7 %	+ 7 %
106	ward408		408	+ 10 %	+ 10 %
107	ward409		409	- 9 %	- 9 %
108	ward410		410	- 14 %	- 14 %
109	ward411		411	- 35 %	- 35 %
110	ward412		412	+ 22 %	+ 22 %
111	ward413		413	+ 11 %	+ 11 %
112	ward414		414	+ 52 %	+ 52 %
113	ward415		415	+ 45 %	+ 45 %
114	ward416		416	+ 15 %	+ 15 %
115	ward417		417	+ 56 %	+ 56 %
116	ward418		418	+ 0 %	+ 0 %
117	ward419		419	+ 5 %	+ 5 %
118	ward420		420	+ 68 %	+ 68 %
119	ward421		421	+ 106 %	+ 106 %
120	ward422		422	+ 60 %	+ 60 %
121	ward423		423	+ 36 %	+ 36 %
122	ward424		424	+ 38 %	+ 38 %
123	ward425		425	+ 78 %	+ 78 %
124	ward426		426	+ 18 %	+ 18 %
125	ward427		427	+ 240 %	+ 240 %
126	ward428		428	+ 189 %	+ 189 %
127	ward429		429	+ 192 %	+ 192 %
128	ward430		430	+ 231 %	+ 231 %
129	ward431		431	+ 233 %	+ 233 %
130	ward432		432	+ 139 %	+ 139 %
131	ward433		433	+ 176 %	+ 176 %
132	ward434		434	+ 26 %	+ 26 %
133	ward435		435	+ 71 %	+ 71 %
134	ward436		436	+ 237 %	+ 237 %
135	ward437		437	+ 178 %	+ 178 %
136	ward438		438	+ 196 %	+ 196 %
137	ward439		439	+ 62 %	+ 62 %
138	ward440		440	+ 120 %	+ 120 %
139	ward441		441	+ 123 %	+ 123 %
140	ward442		442	+ 126 %	+ 126 %
141	ward443		443	+ 129 %	+ 129 %
142	ward444		444	+ 98 %	+ 98 %
143	ward445		445	+ 41 %	+ 41 %
144	ward446		446	+ 180 %	+ 180 %
145	original_useBollore Office	Special Properties	Bollore Office	+ 3819 %	+ 3819 %
146	original_useBollore Warehouse		Bollore Warehouse	+ 1426 %	+ 1426 %
147	original_useBollore Garage		Bollore Garage	+ 1894 %	+ 1894 %
148	original_useDangote Office		Dangote Office	+ 346 %	+ 346 %
149	original_useDangote Warehouse		Dangote Warehouse	+ 73 %	+ 73 %
150	original_useSLPA Warehouse		SLPA Warehouse	+ 119 %	+ 119 %
151	original_useSLPA Office		SLPA Office	+ 461 %	+ 461 %
152	original_useSLPA House Form		SLPA House Form	+ 310 %	+ 310 %
153	original_useSLPA Industrial		SLPA Industrial	+ 158 %	+ 158 %
154	original_useSLPA Motor Garage		SLPA Motor Garage	+ 186 %	+ 186 %
155	original_useSLPA Canteen		SLPA Canteen	+ 322 %	+ 322 %
156	original_useNectar House Form		Nectar House Form	+ 174 %	+ 174 %
157	original_useNectar Warehouse		Nectar Warehouse	+ 46 %	+ 46 %
158	original_useNectar Cargo		Nectar Cargo	+ 182 %	+ 182 %
159	original_useNectar Office		Nectar Office	+ 275 %	+ 275 %
160	original_useLeocem Industrial Manufacturing		Leocem Industrial Manufacturing	- 37 %	- 37 %
161	original_useLeocem Office		Leocem Office	+ 37 %	+ 37 %
Baseline Weights

Cell Formulas
Range	Formula
B97:B144	B97	="ward"&D97
F5:F161	F5	=E5

* Data sheet

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1	ward	zone_clean	marker2	surface_area	original_use	domestic_use	road_status	street_lighting	number_lanes	street_access	has_drainage	beach	hazard	road_visib	informal_settlement	commercial_corridor	high_valued_area	has_potential_to_build	has_water	grade_of_design	wall_category	state_of_repair	wall_finish	fence_category	fence_condition	roof_category	roof_repair	windows_category	airconditioning	security	owns_garage	pool	owns_outbuilding	veranda	annual_rent	ln_annual_rent	model2_exp	test_exp	model2_error	test_exp	test_error
2	399	1	1	6865.98	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	Yes	Modern/Architectural	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	Yes	No	Yes	No	No	Yes	3.3E+07	17.2968	3.2E+07	39,267,161.39	1.22	39,267,162	1.21
3	399	1	2	1628.53	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	No finish	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	4500000	15.3196	6838488	7,057,747.79	1.66	7,057,748	1.57
4	399	1	3	1764.73	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	No finish	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	Yes	Yes	3250000	14.9942	6871430	8,081,650.52	2.25	8,081,651	2.49
5	399	1	4	1702.31	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	High-value material	Average	Low-value material	Good	High-value material	No	No	No	No	No	Yes	7250000	15.7965	1E+07	9,957,874.95	1.39	9,957,875	1.37
6	399	1	5	380.42	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	Low-value material	Average	No finish	No fence	No fence	Low-value material	Average	Low-value material	No	No	No	No	No	No	400000	12.8992	877426	912,066.78	2.36	912,067	2.28
7	399	1	6	2709.53	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	No	No	No	No	Yes	Yes	8250000	15.9257	1.2E+07	13,819,330.54	1.69	13,819,331	1.68
8	399	1	7	1821.36	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	7375000	15.8136	7232029	7,463,905.71	1.02	7,463,906	1.01
9	399	1	8	2967.13	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	Yes	Traditional	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	No	No	No	No	No	Yes	1.1E+07	16.1669	1.7E+07	16,761,998.55	1.37	16,761,999	1.60
10	399	1	9	1354.39	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	8125000	15.9105	6236403	6,436,356.94	0.80	6,436,357	0.79
Data

Cell Formulas
Range	Formula
AN2:AN10	AN2	=PRODUCT((INDEX('Baseline Weights'!$F$97:$F$144,MATCH(A2,'Baseline Weights'!$D$97:$D$144,0))+1), (INDEX('Baseline Weights'!$F$28:$F$29,MATCH(F2,'Baseline Weights'!$D$28:$D$29,0))+1), (INDEX('Baseline Weights'!$F$30:$F$32,MATCH(G2,'Baseline Weights'!$D$30:$D$32,0))+1), (IF(ISNA(INDEX('Baseline Weights'!$F$33:$F$34,MATCH(H2,'Baseline Weights'!$D$33:$D$34,0))),1,INDEX('Baseline Weights'!$F$33:$F$34,MATCH(H2,'Baseline Weights'!$D$33:$D$34,0)))+1), (INDEX('Baseline Weights'!$F$35:$F$38,MATCH(I2,'Baseline Weights'!$D$35:$D$38,0))+1), (INDEX('Baseline Weights'!$F$39:$F$40,MATCH(J2,'Baseline Weights'!$D$39:$D$40,0))+1), (INDEX('Baseline Weights'!$F$41:$F$42,MATCH(K2,'Baseline Weights'!$D$41:$D$42,0))+1), IF(L2="Yes",'Baseline Weights'!$E$43+1,1), IF(M2="Yes",'Baseline Weights'!$E$44+1,1), IF(N2="Yes",'Baseline Weights'!$E$45+1,1), IF(O2="Yes",'Baseline Weights'!$E$46+1,1), IF(P2="Yes",'Baseline Weights'!$E$47+1,1), IF(Q2="Yes",'Baseline Weights'!$E$48+1,1), (INDEX('Baseline Weights'!$F$49:$F$50, MATCH(R2, 'Baseline Weights'!$D$49:$D$50, 0))+1), (INDEX('Baseline Weights'!$F$51:$F$52, MATCH(S2, 'Baseline Weights'!$D$51:$D$52, 0))+1), IF(ISNA(INDEX('Baseline Weights'!$F$53:$F$54, MATCH(T2, 'Baseline Weights'!$D$53:$D$54, 0))), 1, INDEX('Baseline Weights'!$F$53:$F$54, MATCH(T2, 'Baseline Weights'!$D$53:$D$54, 0))+1), (INDEX('Baseline Weights'!$F$55:$F$57, MATCH(U2, 'Baseline Weights'!$D$55:$D$57, 0))+1), (INDEX('Baseline Weights'!$F$58:$F$60, MATCH(V2, 'Baseline Weights'!$D$58:$D$60, 0))+1), (INDEX('Baseline Weights'!$F$61:$F$66, MATCH(W2, 'Baseline Weights'!$D$61:$D$66, 0))+1), (INDEX('Baseline Weights'!$F$67:$F$70, MATCH(X2, 'Baseline Weights'!$D$67:$D$70, 0))+1), (INDEX('Baseline Weights'!$F$71:$F$74, MATCH(Y2, 'Baseline Weights'!$D$71:$D$74, 0))+1), (INDEX('Baseline Weights'!$F$71:$F$74, MATCH(Y2, 'Baseline Weights'!$D$71:$D$74, 0))+1), (INDEX('Baseline Weights'!$F$75:$F$77, MATCH(Z2, 'Baseline Weights'!$D$75:$D$77, 0))+1), (INDEX('Baseline Weights'!$F$78:$F$81, MATCH(AA2, 'Baseline Weights'!$D$78:$D$81, 0))+1), (INDEX('Baseline Weights'!$F$82:$F$84, MATCH(AB2, 'Baseline Weights'!$D$82:$D$84, 0))+1), (INDEX('Baseline Weights'!$F$85:$F$86, MATCH(AC2, 'Baseline Weights'!$D$85:$D$86, 0))+1), (INDEX('Baseline Weights'!$F$87:$F$88, MATCH(AD2, 'Baseline Weights'!$D$87:$D$88, 0))+1), (IF(ISNA(INDEX('Baseline Weights'!$F$89:$F$90, MATCH(AE2, 'Baseline Weights'!$D$89:$D$90, 0))), 1, INDEX('Baseline Weights'!$F$89:$F$90, MATCH(AE2, 'Baseline Weights'!$D$89:$D$90, 0)))+1), (INDEX('Baseline Weights'!$F$93:$F$94, MATCH(AF2, 'Baseline Weights'!$D$93:$D$94, 0))+1), (INDEX('Baseline Weights'!$F$91:$F$92, MATCH(AG2, 'Baseline Weights'!$D$91:$D$92, 0))+1), (INDEX('Baseline Weights'!$F$95:$F$96, MATCH(AH2, 'Baseline Weights'!$D$95:$D$96, 0))+1), SQRT(D2), 'Baseline Weights'!$E$3)
AO2:AO10	AO2	=AN2/AI2

 

[offthelip]

I have written some code that I hope will solve your problem. This might not be the final solution but just a step on the way. This code basically does all the lookups for you and it loadds the values into an array (datout) which I am writing out to a workhseet called "test" in order to check that the lookup are working correctly. I have run it on the dat ayou have posted and it seems to works. Soem of the items come back "notFound" . I checked the values for "BeachNo" and that is correct the isn't weight given for BeachNo. Obviously these can be change from "not found" to zero very easily.
What i am thinking is the time consuming part of your update is undoubtedly the index match stuff. this basically does than so you could buidl the equations up from the values written to the test sheet,
(multiplies and summation is very fast) See what you think:

Sub test()
 Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   
   Set Dic = CreateObject("Scripting.dictionary")
   Dim outr()
barray = Array("A2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", "O2", "P2", "Q2", "S2", "T2", "U2", "V2", "W2", "X2", "Y2", "Z2", "AA2", "AB2", "AC2", "AD2", "AE2", "AF2", "AG2", "AH2", "AI2")
' first convert the column letters in barray to column numbers
ReDim outr(1 To 10, 1 To UBound(barray))
For i = 1 To UBound(barray)
 tt = Len(barray(i))
  If tt = 2 Then
   outr(2, i) = Asc(Left(barray(i), 1)) - 64 ' column Number
   outr(1, i) = barray(i)                    ' Column Letter
  Else
     outr(2, i) = Asc(Mid(barray(i), 2, 1)) - 64 + 26
     outr(1, i) = barray(i)
  End If
Next i
''''''''''''''''''''''''''''
   With Sheets("Baseline_Weights")
      Ary = .Range("b5:E161")   ' load all the bae line weights into a variant array
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 4)  ' add thenm all to the dictionary
   Next i
   End With
''''''''''''''''''''''''''''''
   With Sheets("Data")
      datr = .Range("A1:Am10")  ' load all the data into a variant array
   End With
   With Sheets("Test")
      .Range("A1:Am10") = ""
      datout = .Range("A1:Am10")  ' load all the data into a variant array
    
    For j = 1 To UBound(barray) ' loop through all the columns
        head = datr(1, outr(2, j)) ' pick up header from row 1
        datout(1, outr(2, j)) = head
      For k = 2 To UBound(datr, 1) ' loop through all the rows
        ditem = head & datr(k, outr(2, j))
        If Dic.Exists(ditem) Then
         datout(k, outr(2, j)) = Dic(ditem)
        Else
        datout(k, outr(2, j)) = "Not Found"
        End If
      Next k
    Next j
      .Range("A1:Am10") = datout
      
   End With
'Range(Cells(1, 1), Cells(10, UBound(barray))) = outr
End Sub

Note this code will be very fast!!!

 

[spanishnick]

Hi @offthelip ,

Thank you so much, this looks great. After slightly tweeking it, I was nearly able to produce the desired result. The only thing that is missing is to export the weights for column A (ward) in the "Test" sheet. I tried to go through the code but can't figure out why it's not exporting as the column has been added to the dictionary ("A2").

Thank you again for all of your help

 

[spanishnick]

Hi @offthelip ,

I was able to produce the following table (couldn't find an elegant way to multiply all columns in one cell). The computation with 1'226 rows takes about 50 seconds, unsure if there is anything else I could do to speed up the task as I would like to run the similar code on 100'000+ rows. Also, I've noticed that the quotient formula is not yielding the correct result but don't understand why this is the case.

Thank you again!

Sub test()
 Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
  
   Set Dic = CreateObject("Scripting.dictionary")
   Dim outr()
barray = Array("A2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", "O2", "P2", "Q2", "R2", "S2", "T2", "U2", "V2", "W2", "X2", "Y2", "Z2", "AA2", "AB2", "AC2", "AD2", "AE2", "AF2", "AG2", "AH2")
' first convert the column letters in barray to column numbers
ReDim outr(1 To 1226, 1 To UBound(barray))
For i = 1 To UBound(barray)
 tt = Len(barray(i))
  If tt = 2 Then
   outr(2, i) = Asc(Left(barray(i), 1)) - 64 ' column Number
   outr(1, i) = barray(i)                    ' Column Letter
  Else
     outr(2, i) = Asc(Mid(barray(i), 2, 1)) - 64 + 26
     outr(1, i) = barray(i)
  End If
Next i
''''''''''''''''''''''''''''
   With Sheets("Baseline Weights")
      Ary = .Range("b5:F167")   ' load all the bae line weights into a variant array
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 5)  ' add them all to the dictionary
   Next i
   End With
''''''''''''''''''''''''''''''
   With Sheets("Data")
      datr = .Range("A1:Am1226")  ' load all the data into a variant array
   End With
   With Sheets("Data")
      .Range("Aq1:bx1226") = ""
      datout = .Range("Aq1:bx1226")  ' load all the data into a variant array
    
    For j = 1 To UBound(barray) ' loop through all the columns
        head = datr(1, outr(2, j)) ' pick up header from row 1
        datout(1, outr(2, j)) = head
      For k = 2 To UBound(datr, 1) ' loop through all the rows
        ditem = head & datr(k, outr(2, j))
        If Dic.Exists(ditem) Then
         datout(k, outr(2, j)) = Dic(ditem) + 1
        Else
        datout(k, outr(2, j)) = "Not Found"
        End If
      Next k
    Next j
      .Range("Aq1:bx1226") = datout
      

    For r = 2 To 1226
        Cells(r, 79).Value = Application.WorksheetFunction.Product(Cells(r, 48).Value, Cells(r, 49).Value, Cells(r, 50).Value, Cells(r, 51).Value, Cells(r, 52).Value, Cells(r, 53).Value, Cells(r, 54).Value, Cells(r, 55).Value, Cells(r, 56).Value, Cells(r, 57).Value, Cells(r, 58).Value, Cells(r, 59).Value, Cells(r, 60).Value, Cells(r, 61).Value, Cells(r, 62).Value, Cells(r, 63).Value, Cells(r, 64).Value, Cells(r, 65).Value, Cells(r, 66).Value, Cells(r, 67).Value, Cells(r, 68).Value, Cells(r, 69).Value, Cells(r, 70).Value, Cells(r, 71).Value, Cells(r, 72).Value, Cells(r, 73).Value, Cells(r, 74).Value, Cells(r, 75).Value, Cells(r, 76).Value)
    Next r
    
    For s = 2 To 1226
        Cells(s, 40).Value = Application.WorksheetFunction.Product(231859.13, Cells(s, 78).Value, Cells(s, 79).Value, Cells(s, 80).Value)
    Next s
    
    For t = 2 To 1226
        Cells(t, 41).Value = WorksheetFunction.Quotient(Cells(t, 40).Value, Cells(t, 35).Value)
    Next t

    
   End With
'Range(Cells(1, 1), Cells(10, UBound(barray))) = outr
End Sub

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB
1	ward	zone_clean	marker2	surface_area	original_use	domestic_use	road_status	street_lighting	number_lanes	street_access	has_drainage	beach	hazard	road_visib	informal_settlement	commercial_corridor	high_valued_area	has_potential_to_build	has_water	grade_of_design	wall_category	state_of_repair	wall_finish	fence_category	fence_condition	roof_category	roof_repair	windows_category	airconditioning	security	owns_garage	pool	owns_outbuilding	veranda	annual_rent	ln_annual_rent	model2_exp	original_exp	model2_error	test_exp	test_error							domestic_use	road_status	street_lighting	number_lanes	street_access	has_drainage	beach	hazard	road_visib	informal_settlement	commercial_corridor	high_valued_area	has_potential_to_build	has_water	grade_of_design	wall_category	state_of_repair	wall_finish	fence_category	fence_condition	roof_category	roof_repair	windows_category	airconditioning	security	owns_garage	pool	owns_outbuilding	veranda		Ward weight		square root of surface
2	399	1	1	6866	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	Yes	Modern/Architectural	High-value material	Good	Painted or Whitewashed	No fence	No fence	Low-value material	Good	High-value material	Yes	No	Yes	No	No	Yes	32500000	17.297	3.2E+07	39,267,161.39	1.22	39,267,161.67	1.00							1	0.9	1	1	1	1	1	1	1	1	1	1	1.03	1.02	1	1	1.25	1	1	1	0.79	1.2	1.15	1.54	1	1	1	1	1.03	231859.1	1.00	2.04387	82.86120914
3	399	1	2	1628.5	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	No finish	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	No	Yes	4500000	15.32	6838488	7,057,747.79	1.66	7,057,747.84	1.00							1	0.9	1	1	1	1	1	1	1	1	1	1	1.03	1	1	1	1	1	1	1	0.79	1	1	1	1	1	1	1	1.03	231859.1	1.00	0.7543	40.35504961
4	399	1	3	1764.7	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	No finish	No fence	No fence	Low-value material	Average	Medium-value material	No	No	No	No	Yes	Yes	3250000	14.994	6871430	8,081,650.52	2.25	8,081,650.58	2.00							1	0.9	1	1	1	1	1	1	1	1	1	1	1.03	1	1	1	1	1	1	1	0.79	1	1	1	1	1	1	1.1	1.03	231859.1	1.00	0.82973	42.00868934
5	399	1	4	1702.3	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	High-value material	Average	Painted or Whitewashed	High-value material	Average	Low-value material	Good	High-value material	No	No	No	No	No	Yes	7250000	15.797	1E+07	9,957,874.95	1.39	9,957,875.02	1.00							1	0.9	1	1	1	1	1	1	1	1	1	1	1.03	1	1	1	1	1	1	1	0.79	1.2	1.15	1	1	1	1	1	1.03	231859.1	1.00	1.04093	41.25906032
6	399	1	5	380.42	House or dwelling form	Yes	Bad	No	One	Easy	No	No	No	No	No	No	No	Yes	No	Traditional	Low-value material	Average	No finish	No fence	No fence	Low-value material	Average	Low-value material	No	No	No	No	No	No	400000	12.899	877426	912,066.78	2.36	912,066.79	2.00							1	0.9	1	1	1	1	1	1	1	1	1	1	1.03	1	1	0.51	1	1	1	1	0.79	1	0.54	1	1	1	1	1	1	231859.1	1.00	0.20168	19.50435883
Data

Cell Formulas
Range	Formula
CB2:CB6	CB2	=SQRT(D2)

 

[offthelip]

Hi @offthelip ,

Thank you so much, this looks great. After slightly tweeking it, I was nearly able to produce the desired result. The only thing that is missing is to export the weights for column A (ward) in the "Test" sheet. I tried to go through the code but can't figure out why it's not exporting as the column has been added to the dictionary ("A2").

Thank you again for all of your help

I found the problem as to why the Ward match wasn't working I had got the indexing in to the barray wrong it should ahve started a zero not at one. I have fixed in the latest code see below