Hi,
I have two columns showing the link between a downstream station and an upstream station along a sewer.</SPAN>
So my columns consist of A (downstream station) and B (1st</SPAN> Tier upstream station);</SPAN>
For example...</SPAN>
A ----B</SPAN>
X ----Y</SPAN>
Y ----Z</SPAN>
Y ----W</SPAN>
W ----V</SPAN>
W ----Q</SPAN>
Q ----K</SPAN>
(don't know how to make it look like columns, sorry, had to use "-----" to mean space between columns)
So, Y flows into X. Both Z and W flow into Y. Both V and Q flow into W and K flows into Q. So essentially we have a branch system of stations, but here they are represented as links between upstream and downstream.</SPAN>
Now, if there is only ONE station upstream, I can easily list that in the next column by =VLOOKUP(B2,$A$2:$B$2189,2,FALSE). However this fails if there are more than two stations flowing into the downstream station (for instance above where we have Z and W both flowing into Y) – since the Vlookup only displays the first instance it comes across. </SPAN>
The end result I am after, is a mechanism to show each tier of upstream stations in a next column, but where there are two stations going into one, at a single tier, for them to be listed as Concatenate. A sort of Concatinate A:A if B1 = B:B? For each column.</SPAN>
So the end result would be...</SPAN>
A---- B ----Tier2 ----Tier 3 ----etc... (for about 10 Tiers) </SPAN>
X ----Y ----Z,W -----V </SPAN>
Y ----Z </SPAN>
Y ----W ----V,Q -----K</SPAN>
W----V </SPAN>
W ---Q -----K</SPAN>
Q ----K</SPAN>
So, in the first tier upstream if Y is station Z and W, and in the next tier is station V...</SPAN>
Note: there will never be multiple DOWNSTREAM stations to ONE UPSTREAM station</SPAN>
Any help would be much appreciated – I am assuming this has to be a visual basic job.</SPAN>
I have two columns showing the link between a downstream station and an upstream station along a sewer.</SPAN>
So my columns consist of A (downstream station) and B (1st</SPAN> Tier upstream station);</SPAN>
For example...</SPAN>
A ----B</SPAN>
X ----Y</SPAN>
Y ----Z</SPAN>
Y ----W</SPAN>
W ----V</SPAN>
W ----Q</SPAN>
Q ----K</SPAN>
(don't know how to make it look like columns, sorry, had to use "-----" to mean space between columns)
So, Y flows into X. Both Z and W flow into Y. Both V and Q flow into W and K flows into Q. So essentially we have a branch system of stations, but here they are represented as links between upstream and downstream.</SPAN>
Now, if there is only ONE station upstream, I can easily list that in the next column by =VLOOKUP(B2,$A$2:$B$2189,2,FALSE). However this fails if there are more than two stations flowing into the downstream station (for instance above where we have Z and W both flowing into Y) – since the Vlookup only displays the first instance it comes across. </SPAN>
The end result I am after, is a mechanism to show each tier of upstream stations in a next column, but where there are two stations going into one, at a single tier, for them to be listed as Concatenate. A sort of Concatinate A:A if B1 = B:B? For each column.</SPAN>
So the end result would be...</SPAN>
A---- B ----Tier2 ----Tier 3 ----etc... (for about 10 Tiers) </SPAN>
X ----Y ----Z,W -----V </SPAN>
Y ----Z </SPAN>
Y ----W ----V,Q -----K</SPAN>
W----V </SPAN>
W ---Q -----K</SPAN>
Q ----K</SPAN>
So, in the first tier upstream if Y is station Z and W, and in the next tier is station V...</SPAN>
Note: there will never be multiple DOWNSTREAM stations to ONE UPSTREAM station</SPAN>
Any help would be much appreciated – I am assuming this has to be a visual basic job.</SPAN>
Last edited: