0

Say, I got a points index like that:

sheet1

1
2
3
4
5
6
...

And a connection data, represents the relation between these points, like, the first 2 rows mean that point_1 are connected with point_75 and point_78 :

sheet2

1   75
1   78
2   44
3   45
3   65
4   39
4   63
5   49
5   50
6   59
...

And I wish I could use some expression to represent that in sheet1 like:

1   75,78
2   44
3   45,65
...

Is there any way to do that?

  • What is the maximum number of other points that a point is connected to? – Bandersnatch Aug 10 '17 at 15:23
  • @Bandersnatch there's 582 points... so I don't know how to count the max connection... According to my raw observation, the max number of points one point could connect is about 3 – Amarth Gûl Aug 10 '17 at 15:33
1

First enter the following User Defined Function in a standard module:

Public Function Konnect(r1 As Range, r2 As Range) As String
    Dim ary, i As Long
    Dim v As Variant

    ary = r2
    v = r1.Value
    Konnect = ""

    For i = LBound(ary, 1) To UBound(ary, 1)
        If ary(i, 1) = v Then Konnect = Konnect & "," & ary(i, 2)
    Next i

    If Len(Konnect) <> 0 Then Konnect = Mid(Konnect, 2)
End Function

Then in Sheet1 cell B1 enter:

=Konnect(A1,Sheet2!A$1:B$10)

and copy down:

enter image description here

Naturally, you would change the 10 to match the actual table size on Sheet2.

EDIT#1:

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=Konnect(A1,Sheet2!A$1:B$10)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

| improve this answer | |
  • Could you please tell me where can I access User Defined Function please... – Amarth Gûl Aug 10 '17 at 15:55
  • @AmarthGûl See my EDIT#1 – Gary's Student Aug 10 '17 at 15:59
  • @AmarthGûl You are quite welcome! – Gary's Student Aug 10 '17 at 16:05
  • Nice! I'll remember this. And "Bound(ary" too. :-D – Bandersnatch Aug 10 '17 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.