View Full Version : excel connection

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

Is it not just

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

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

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

03-23-2011, 06:16 AM

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.

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

Thank you very much mdmackillop...

I am checking...

thank you

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

03-24-2011, 01:43 AM

As this line sets the range as per your initial request

Set Rng1 = Range("A2:A32")

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

mdmackillop

03-24-2011, 02:03 AM

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

hello mdmackillop

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.