excel connection

[Jsam]

Hello,

I have two column.

Column A contains Site 1 ID and Column 2 contains Site 2 ID . These two sites and connected with Link with ID - Site1_ID -Site2_ID

I am trying have one single row , Start site to end site , For example


1308XYZ - 256XYZ - 1306XYZ - 812XYZ

This connection makes one complete ring.

Is there any easy way to this in MS excel ?? Any suggestions ?


Thanks
jsam

[Bob Phillips]

Is it not just

=A2&" - "&B2&" - "&C2

[Jsam]

Thanks for your response

Sorry, May be I am not clear here.

It is not matter of concatenating two column with "-"

Here is what I meant to say.-

There is link between two column. Column A is always going to Corresponding Column B, ie A2 is linked to B2
I need all Links in single row. for example.

A2 has 1308XYZ which is connecting to B2 256XYZ
AND B2 is connected to B3 and B3 is connected
to B4 and so on

NOw I need A2-B2-B3-B4 in single row.

Value of A2, B2, B3, B4 are not in squence, It can be in any cells
within A2:B32

Please let me know if you need any further info.

Appreciated

Thanks

[Jsam]

any help please.........

[Jsam]

Sorry, May be I am not clear here. It is not matter of concatenating two column with "-"
Here is what I meant to say.-

Column 1 represents 1 side of Transmission Link and Column2 represent other side of transmission link.

IF we follow excel data , we can see, Site on column A is connected to site in column B.
I need all sites in single row. for example. A2 has 1308XYZ which is connecting to B2 256XYZ AND B2 is connected to B3 and B3 is connected
to B4 and so on Now I need A2-B2-B3-B4 in single row. I have attached excel sheet for you which has expected results. Value of A2, B2, B3, B4 are not in squence, It can be in any cells within A2:B32

thanks for any help pls .....

[mdmackillop]

I don't know if this is what you are after, but give it a try. I see that B6 links to A2, but as A2 is previously used, this is ignored.
[vba]
Option Explicit
Sub Links()
Dim Lnk As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim cel As Range
Dim c As Range
Dim i As Long
Dim col As Long
Cells.Interior.ColorIndex = xlNone
col = 2
Set Rng1 = Range("A2:A32")
Set Rng2 = Rng1.Offset(, 1)
For Each cel In Rng1
col = col + 1
If cel.Interior.ColorIndex = xlNone Then
cel.Select
cel.Offset(, 4) = cel
cel.Offset(, 4).Interior.ColorIndex = col
Set Lnk = cel.Offset(, 1)
cel.Offset(, 5) = Lnk
cel.Offset(, 5).Interior.ColorIndex = col
cel.Resize(, 2).Interior.ColorIndex = col
i = 0
Do
Set c = Rng1.Find(Lnk, cel)
If Not c Is Nothing Then
If c.Interior.ColorIndex = xlNone Then
c.Select
i = i + 1
Set Lnk = c.Offset(, 1)
cel.Offset(, 5 + i) = Lnk
cel.Offset(, 5 + i).Interior.ColorIndex = col
c.Resize(, 2).Interior.ColorIndex = col
Else
Exit Do
End If

Else
Exit Do
End If
Loop
End If
Next
End Sub

[/vba]

[Jsam]

Thank you very much mdmackillop...

I am checking...

thank you
jsam

[Jsam]

mdmackillop,

Thank you so much for your help.

Since column A is not always starting site , I am getting some error with you code. For example
(Column A2 - Column B2) Can be (Column B2 - Column A2).

Let us say, A is connected to B and B is connected to C and C is connected to D and D is finally connected to A. This is one complete Ring.

Now, I have another ring starting from A. for example A - F -G - H and back to A.

For some reason Connection between D-A and H-A gets mixed up and makes one whole loop, A-B-C-D-A-F-G-H-A.
In fact this should be two separate Ring.

One more questions, Since I am not that much familiar with VBA ,how do I auto increase Range value instead of Range("A2:A32") . I need to ran around 6000 rows at the same time.

I have attached, new version of data with some sample diagrams. I have also included some sample results .

Once again thank you so much... appreciated

-Jsam

[Aussiebear]

As this line sets the range as per your initial request
[VBA]Set Rng1 = Range("A2:A32")[/VBA]

You will need to simply adjust the ("A2:A6000")

[mdmackillop]

I'm afraid this is beyond the scope of the free assistance I'm prepared to offer. Please refer to our FAQ.

[Jsam]

hello mdmackillop

any help pls .... i am really in trouble .....