Spreadsheet: 51101d1250675191-more-efficient-consolidation-formula-sampleconsolidation
Sheet: Output

Real 'Data' range = +35,000 rows and 138 Columns
Current solution
 | Yellow cells have current solution sumproduct formula
VisitLocation | [NAME] | VLSD | VLSE | VLSF | VLSG | VLSH | VLSI | VLSJ | VLSK | VLSL | VLSM | VLSN | VLSO | VLSP | VLSQ
[NAME] | 174 | 60 | 37 | 71 | 54 | 80 | 32 | 42 | 65 | 56 | 37 | 34 | 25 | 63 | 24
VLSD | 60 | 122 | 25 | 50 | 35 | 45 | 18 | 23 | 46 | 41 | 29 | 23 | 18 | 39 | 19
VLSE
VLSF
VLSG
VLSH
VLSI
VLSJ
VLSK
VLSL
VLSM
VLSN
VLSO
VLSP
VLSQ
Sample Output
VisitLocation | [NAME] | VLSD | VLSE | VLSF | VLSG | VLSH | VLSI | VLSJ | VLSK | VLSL | VLSM | VLSN | VLSO | VLSP | VLSQ
[NAME] | 174 | 60 | 37 | 71 | 54 | 80 | 32 | 42 | 65 | 56 | 37 | 34 | 25 | 63 | 24
VLSD | 60 | 122 | 25 | 50 | 35 | 45 | 18 | 23 | 46 | 41 | 29 | 23 | 18 | 39 | 19
VLSE | 37 | 25 | 113 | 39 | 37 | 53 | 43 | 13 | 52 | 52 | 21 | 26 | 31 | 62 | 26
VLSF | 71 | 50 | 39 | 157 | 58 | 72 | 34 | 28 | 65 | 64 | 33 | 24 | 30 | 65 | 33
VLSG | 54 | 35 | 37 | 58 | 118 | 62 | 31 | 18 | 53 | 51 | 24 | 21 | 24 | 59 | 31
VLSH | 80 | 45 | 53 | 72 | 62 | 168 | 40 | 30 | 72 | 69 | 39 | 47 | 33 | 84 | 37
VLSI | 32 | 18 | 43 | 34 | 31 | 40 | 88 | 15 | 49 | 52 | 17 | 16 | 34 | 50 | 25
VLSJ | 42 | 23 | 13 | 28 | 18 | 30 | 15 | 98 | 26 | 28 | 14 | 11 | 15 | 21 | 10
VLSK | 65 | 46 | 52 | 65 | 53 | 72 | 49 | 26 | 164 | 77 | 30 | 34 | 48 | 81 | 38
VLSL | 56 | 41 | 52 | 64 | 51 | 69 | 52 | 28 | 77 | 153 | 36 | 33 | 40 | 86 | 42
VLSM | 37 | 29 | 21 | 33 | 24 | 39 | 17 | 14 | 30 | 36 | 78 | 29 | 16 | 37 | 19
VLSN | 34 | 23 | 26 | 24 | 21 | 47 | 16 | 11 | 34 | 33 | 29 | 89 | 18 | 39 | 27
VLSO | 25 | 18 | 31 | 30 | 24 | 33 | 34 | 15 | 48 | 40 | 16 | 18 | 69 | 35 | 22
VLSP | 63 | 39 | 62 | 65 | 59 | 84 | 50 | 21 | 81 | 86 | 37 | 39 | 35 | 165 | 41
VLSQ | 24 | 19 | 26 | 33 | 31 | 37 | 25 | 10 | 38 | 42 | 19 | 27 | 22 | 41 | 79
VisitLocation |  | [NAME] | VLSD | VLSE | VLSF | VLSG | VLSH | VLSI | VLSJ | VLSK | VLSL | VLSM | VLSN | VLSO | VLSP | VLSQ
[NAME] | 174 | 1 | 0.3448275862068966 | 0.2126436781609195 | 0.4080459770114943 | 0.3103448275862069 | 0.4597701149425287 | 0.1839080459770115 | 0.2413793103448276 | 0.3735632183908046 | 0.3218390804597701 | 0.2126436781609195 | 0.1954022988505747 | 0.1436781609195402 | 0.3620689655172414 | 0.1379310344827586
VLSD | 122 | 0.4918032786885246 | 1 | 0.2049180327868853 | 0.4098360655737705 | 0.2868852459016393 | 0.3688524590163935 | 0.1475409836065574 | 0.1885245901639344 | 0.3770491803278688 | 0.3360655737704918 | 0.2377049180327869 | 0.1885245901639344 | 0.1475409836065574 | 0.319672131147541 | 0.1557377049180328
VLSE | 113 | 0.3274336283185841 | 0.2212389380530974 | 1 | 0.3451327433628318 | 0.3274336283185841 | 0.4690265486725664 | 0.3805309734513274 | 0.1150442477876106 | 0.4601769911504425 | 0.4601769911504425 | 0.1858407079646018 | 0.2300884955752212 | 0.2743362831858407 | 0.5486725663716814 | 0.2300884955752212
VLSF | 157 | 0.4522292993630573 | 0.3184713375796178 | 0.2484076433121019 | 1 | 0.3694267515923567 | 0.4585987261146497 | 0.2165605095541401 | 0.178343949044586 | 0.4140127388535032 | 0.4076433121019108 | 0.2101910828025478 | 0.1528662420382166 | 0.1910828025477707 | 0.4140127388535032 | 0.2101910828025478
VLSG | 118 | 0.4576271186440678 | 0.2966101694915254 | 0.3135593220338983 | 0.4915254237288136 | 1 | 0.5254237288135594 | 0.2627118644067797 | 0.1525423728813559 | 0.4491525423728814 | 0.4322033898305085 | 0.2033898305084746 | 0.1779661016949153 | 0.2033898305084746 | 0.5 | 0.2627118644067797
VLSH | 168 | 0.4761904761904762 | 0.2678571428571428 | 0.3154761904761905 | 0.4285714285714285 | 0.3690476190476191 | 1 | 0.2380952380952381 | 0.1785714285714286 | 0.4285714285714285 | 0.4107142857142857 | 0.2321428571428572 | 0.2797619047619048 | 0.1964285714285714 | 0.5 | 0.2202380952380952
VLSI | 88 | 0.3636363636363636 | 0.2045454545454546 | 0.4886363636363636 | 0.3863636363636364 | 0.3522727272727273 | 0.4545454545454545 | 1 | 0.1704545454545454 | 0.5568181818181818 | 0.5909090909090909 | 0.1931818181818182 | 0.1818181818181818 | 0.3863636363636364 | 0.5681818181818182 | 0.2840909090909091
VLSJ | 98 | 0.4285714285714285 | 0.2346938775510204 | 0.1326530612244898 | 0.2857142857142857 | 0.1836734693877551 | 0.3061224489795918 | 0.1530612244897959 | 1 | 0.2653061224489796 | 0.2857142857142857 | 0.1428571428571428 | 0.1122448979591837 | 0.1530612244897959 | 0.2142857142857143 | 0.1020408163265306
VLSK | 164 | 0.3963414634146342 | 0.2804878048780488 | 0.3170731707317073 | 0.3963414634146342 | 0.3231707317073171 | 0.4390243902439024 | 0.298780487804878 | 0.1585365853658537 | 1 | 0.4695121951219512 | 0.1829268292682927 | 0.2073170731707317 | 0.2926829268292683 | 0.4939024390243902 | 0.2317073170731707
VLSL | 153 | 0.3660130718954248 | 0.2679738562091503 | 0.3398692810457516 | 0.4183006535947713 | 0.3333333333333333 | 0.4509803921568628 | 0.3398692810457516 | 0.1830065359477124 | 0.5032679738562091 | 1 | 0.2352941176470588 | 0.2156862745098039 | 0.261437908496732 | 0.5620915032679739 | 0.2745098039215687
VLSM | 78 | 0.4743589743589743 | 0.3717948717948718 | 0.2692307692307692 | 0.4230769230769231 | 0.3076923076923077 | 0.5 | 0.217948717948718 | 0.1794871794871795 | 0.3846153846153846 | 0.4615384615384616 | 1 | 0.3717948717948718 | 0.2051282051282051 | 0.4743589743589743 | 0.2435897435897436
VLSN | 89 | 0.3820224719101123 | 0.2584269662921349 | 0.2921348314606741 | 0.2696629213483146 | 0.2359550561797753 | 0.5280898876404494 | 0.1797752808988764 | 0.1235955056179775 | 0.3820224719101123 | 0.3707865168539326 | 0.3258426966292135 | 1 | 0.2022471910112359 | 0.4382022471910113 | 0.303370786516854
VLSO | 69 | 0.3623188405797101 | 0.2608695652173913 | 0.4492753623188406 | 0.4347826086956522 | 0.3478260869565217 | 0.4782608695652174 | 0.4927536231884058 | 0.2173913043478261 | 0.6956521739130435 | 0.5797101449275363 | 0.2318840579710145 | 0.2608695652173913 | 1 | 0.5072463768115942 | 0.3188405797101449
VLSP | 165 | 0.3818181818181818 | 0.2363636363636364 | 0.3757575757575757 | 0.3939393939393939 | 0.3575757575757576 | 0.509090909090909 | 0.303030303030303 | 0.1272727272727273 | 0.4909090909090909 | 0.5212121212121212 | 0.2242424242424242 | 0.2363636363636364 | 0.2121212121212121 | 1 | 0.2484848484848485
VLSQ | 79 | 0.3037974683544304 | 0.2405063291139241 | 0.3291139240506329 | 0.4177215189873418 | 0.3924050632911392 | 0.4683544303797468 | 0.3164556962025317 | 0.1265822784810127 | 0.4810126582278481 | 0.5316455696202531 | 0.2405063291139241 | 0.3417721518987342 | 0.2784810126582278 | 0.5189873417721519 | 1
Interpretation:
Of the 174 Customers who visited location [[NAME]] 60 (34%) also visited Location [VLSD]
Of the 174 Customers who visited location [[NAME]] 80 (46%) also visited Location [VLSH] - Review products for possible consolidation.