Two Disparate yet Overlapping Matrices (Proportional Equivalencies)?

A

agagag

New Member
Joined
Mar 23, 2017
Messages
2
Hi All

Struggling with an issue that I am sure someone must have faced before, but I can't find an answer when I searched the forum, so hoping someone can help me.
Essentially I am trying to convert an OD matrix into another OD matrix, by which I mean I have OD matrices from two disparate zonal systems, and I have managed to get spatial proportions from each zone system (and each OD matrix) to the other where they overlap, and I want to apply these proportions so that I can make a comparison between the two disparate OD matrices. I might not be explaining this clearly but say I have an OD matrix A to F and another OD matrix Q to Z, whereby I have the proportions between the two sets of matrices, surely there must be a way to convert the OD matrix (( Q to Z)) to the A to F, but I'm not clever enough to think of a way! I can extract the matrices in 3 column list format (From, To, Value) but still not sure what the easiest way to aggregate and disaggregate an OD matrix into another is... any help, tips, formulae or examples would be very much appreciated

Francesca
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
To clarify as a simplified example

[TABLE="width: 703"]
<colgroup><col><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OD Matrix One[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OD Matrix Two[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]R[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]798[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]758[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]U[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Spatial Percentages to Apply[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]R[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]S[/TD]
[TD="align: right"]99%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]1%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]U[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
J
  • Question Question
VBA Code for C&P from two workbooks into a Master Workbook
Replies
4
Views
952
JTS25
J
A
  • Question Question
the intersection of two matrices
Replies
4
Views
414
achaus
A
H
  • Question Question
Calendar userform-text selection
Replies
1
Views
7K
hissonrr
H
M
  • Question Question
Help with Dictionary Class to arrange frequencies in the correct row.
Replies
2
Views
616
shg
shg

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top