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 (1

For example...</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>

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 (1

^{st</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: