[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]US[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]US[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]
In a very simplified world, this is my data, where 'A' represents the hub from which a car is leaving, and 'B' represents the hub to which the car is going. C, D, and E all hold data describing the hub: C holds the number of the hub (which coincides with the row number - 1), D holds the location of the hub, and E is a numerical representation of the hub. Finally, F represents the country from which the car is coming, and G represents the country to which the car is going. Although I'm not good with visual basic, and I can't do much programming, I do have background in Java, and thus came up with a way of representing the logic that I see to solve my problem in hopes of finding an excel specific answer.
The way I see it, I'm just sorting through the data in a For loop, essentially going through every cell in A, and making a corresponding entry in F, and doing the same in B and G respectively. So to do this I propose the following logic. (In the following logic, Q represents a counter for the for loop which starts at 2 (first row) and end at 7 (last row), then, the counter gives the number of the hub for a certain row in A, thus A(Q). Next, the country that A(Q) represents is found by using the row numbers (which correspond with the hub numbers in hub-country correspondence minus one) and setting that equal to a variable (country) which is then put into the cell F(Q).)
For (2,7,Q,1)
hub = A(Q);
country = E(hub-1);
F(Q) = country;
End
Although this illustrates the thinking behind the concept, it is far from the shortest way to write it, thus, we arrive at the following loop.
For (2,7,Q,1)
F(Q) = E(A(Q)-1);
End
With the simplified formula, I hope that it will make it easier to find a solution, thank you in advance for any answers.
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]US[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]US[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]
In a very simplified world, this is my data, where 'A' represents the hub from which a car is leaving, and 'B' represents the hub to which the car is going. C, D, and E all hold data describing the hub: C holds the number of the hub (which coincides with the row number - 1), D holds the location of the hub, and E is a numerical representation of the hub. Finally, F represents the country from which the car is coming, and G represents the country to which the car is going. Although I'm not good with visual basic, and I can't do much programming, I do have background in Java, and thus came up with a way of representing the logic that I see to solve my problem in hopes of finding an excel specific answer.
The way I see it, I'm just sorting through the data in a For loop, essentially going through every cell in A, and making a corresponding entry in F, and doing the same in B and G respectively. So to do this I propose the following logic. (In the following logic, Q represents a counter for the for loop which starts at 2 (first row) and end at 7 (last row), then, the counter gives the number of the hub for a certain row in A, thus A(Q). Next, the country that A(Q) represents is found by using the row numbers (which correspond with the hub numbers in hub-country correspondence minus one) and setting that equal to a variable (country) which is then put into the cell F(Q).)
For (2,7,Q,1)
hub = A(Q);
country = E(hub-1);
F(Q) = country;
End
Although this illustrates the thinking behind the concept, it is far from the shortest way to write it, thus, we arrive at the following loop.
For (2,7,Q,1)
F(Q) = E(A(Q)-1);
End
With the simplified formula, I hope that it will make it easier to find a solution, thank you in advance for any answers.