Conditional Data Replacement

economics

New Member
Joined
Dec 12, 2012
Messages
9
[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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
The following are not clear to me:
  1. what data is "transactional" (i.e. key data only known and entered once car hire (?) is arranged.)
  2. what data is static and can be populated once the transactional data is known (though I'm guessing this includes C, D, and E which all hold data describing the hub. However, are F & G not also directly related to the From/To hubs?)
  3. what exactly is the problem for which you need assistance.

Static data (e.g. hub info) can be placed in separate reference tables, and then formulae using lookup functions (e.g. VLOOKUP) can be constructed to return the relevant value from the table to the database, without the need for code.
 
Upvote 0
The following are not clear to me:
  1. what data is "transactional" (i.e. key data only known and entered once car hire (?) is arranged.)
  2. what data is static and can be populated once the transactional data is known (though I'm guessing this includes C, D, and E which all hold data describing the hub. However, are F & G not also directly related to the From/To hubs?)
  3. what exactly is the problem for which you need assistance.

Static data (e.g. hub info) can be placed in separate reference tables, and then formulae using lookup functions (e.g. VLOOKUP) can be constructed to return the relevant value from the table to the database, without the need for code.

Alright let me try and answer this to see if you can help

To clarify the data isn't actually representative of cars it's airport data, and it has nearly 60,000 entries and 6,000 hubs, but to make it easier I tried using the car analogy. Yet I think the plane example works better, the data in A and B represents that a plane is flying from a certain hub to another. Let's call hub 1 New York, hub 2 LA, and hub 3 Toronto. That means in row 3, a plane is flying from New York to Toronto. But, I'm only interested in what country their flying too, which is why I have the data in C, D, & E, that data tells me what country the hub is in (I'm thinking this is where we could use VLOOKUP). Now, the data in F & G isn't actually data, this is the output that I want, it tells me what country the plane is leaving from and what country the plane is leaving to. For example, it changes row three from representing New York to Toronto, to representing US to Canada. Essentially I just need to go through all the data in A & B, and be able to convert it to the data in F & G using the data in C, D, and E, although in reality I would only be using the data in C and E.

Thank You,

Economics
 
Upvote 0
I'm still not fully on the same page - sorry.

This is my current understanding:
Col A = Hub of departure - represented by a code No.
Col B = Hub of destination - represented by a code No.
Col C = Hub No. [but see query below]
Col D = Hub location (country - as string) [but see query below]
Col E = a numerical representation of the hub [but see query below]
Col F = Country of [hub] departure - represented by a code No.
Col G = Country of [hub] destination - represented by a code No.

This bit confuses me:
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.
  1. Which hub does this info relate to - departure or destination?
  2. What is the difference between:
    1. A or B, and C
    2. C & E?

All of the data seems to relate to hubs (which would therefore be static), so if ALL the info about each hub (irrespective of whether used as a departure or destination hub) was placed in a separate 'master' reference table, viz:
  1. Hub ID [numeric]
  2. Hub location (City) [string]
  3. Hub location (Country) [string]
  4. Hub location (Country) [numeric]
  5. ??
then after entering A & B into your "transaction/flight" database, ALL the remaining values could be populated by VLOOKUPs by using A and B as the lookup value.
Is it correct to draw this conclusion?
If so, the formulae in C:G would be =VLOOKUP(lookup value[=hub ID in A or B],table-array[=hub ref table],col ref([=column offset in hub ref table depending on field req'd],FALSE[to ensure exact match]).
 
Upvote 0
Alright so I'm currently writing from my phone but I think I know what's confusing you, the values in C represent all the hubs: this is basically an index of the countries of the hubs. That means that a 1 in column a or b is always going to represent new York, and in C the hub '1' will always be in the US. Now the idea is to replace the hub transfers from a city level to a country level. So to answer your two questions, the hub description data is representing all the hubs wether departing or arriving. That means essentially column a and b are the routes and c through are airport descriptors which allow the conversion from city to country. The difference between c and e is that the numbers in c hold the value for the city, and the values in e are the codes for the countries. Im guessing then, that the data that needs to be the reference is the c through e data which gives reference to which hubs are in which country, and then then data in A:B is transformed to the data in F:G.
 
Upvote 0
.... that the data that needs to be the reference is the c through e data which gives reference to which hubs are in which country, and then then data in A:B is transformed to the data in F:G.

Given that hubs are physical locations that don't move (so everything about them is known in advance), my understanding - though perhaps incorrect - is that the fields in both C:E and F:G are all directly related to either of the two hubs on any route.
Is this correct?

If so,
you should be able to build a separate master reference/index/lookup table of all 6,000 hubs by recording ALL known details and in all forms (i.e. number or string) about each hub in a single row, as per my previous post. (The order of fields across the table does not matter but preferably whatever goes in A or B of the table you're trying to populate (e.g. whatever uniquely identifies each hub - whether a code number or text string/name) is in the left-most column as VLOOKUP can only return a value from a table that is to the right of the lookup value)

such that

this will give you the data to populate all fields C:G for any combination of hubs in A:B. (i.e. finding the value of A in the lookup table will give the corresponding values for some of the fields, and finding the value of B will give the values for the remaining fields)


If this is not the answer then I must be missing something fundamental.:(
 
Upvote 0
Given that hubs are physical locations that don't move (so everything about them is known in advance), my understanding - though perhaps incorrect - is that the fields in both C:E and F:G are all directly related to either of the two hubs on any route.
Is this correct?

If so,
you should be able to build a separate master reference/index/lookup table of all 6,000 hubs by recording ALL known details and in all forms (i.e. number or string) about each hub in a single row, as per my previous post. (The order of fields across the table does not matter but preferably whatever goes in A or B of the table you're trying to populate (e.g. whatever uniquely identifies each hub - whether a code number or text string/name) is in the left-most column as VLOOKUP can only return a value from a table that is to the right of the lookup value)

such that

this will give you the data to populate all fields C:G for any combination of hubs in A:B. (i.e. finding the value of A in the lookup table will give the corresponding values for some of the fields, and finding the value of B will give the values for the remaining fields)


If this is not the answer then I must be missing something fundamental.:(


Yes! Thank you, I got it!
 
Upvote 0

Forum statistics

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

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