Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I suspect because of what appears to be a looping requirement, I need VBA (with which I'm not overly familiar) to do this.
    Strange request:

    Starting in row 2, column A contains a single number that corresponds to a name in column B.

    Column C could be empty or contain a string of numbers separated by commas.

    Result: Column D

    Column D contains the name of the person in column B, concatentated with some text (e.g., "Bill's friends: ")
    If column C is empty, the result in D is "Bill's friends: N/A"

    If column C contains one or more numbers in a string, the result is something like:

    "Bill's friends:
    Mary
    Fred
    Joe "

    (with line feeds after each name). The names (Mary, Fred, & Joe) come from column B based on the numbers in column A.

    Is this clear as mud and, if so, do-able with some VBA?

    I don't know if it can be done without VBA, but, if so, that would be really nice.

    Thanks in advance.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Any chance of an example file.
    Trying here to work out the way that the numbers in Col C work with the
    Data in A and B to generate the result in D

    Also is the data in A,B,C Manually entered or imported?
    Andrew

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    The data is manually entered.

    I tried to attach a small sample, but got a server error. Even got an error when trying to post an image. SIGH.

    Col A contains:

    1
    2
    3
    ...

    Col B contains:

    Bill
    Fred
    Mary
    ...

    Col C contains:

    {blank}
    1, 3
    2
    ...

    Corresponding cells in Col D should contain:

    Friend List: {line feed} N/A
    Friend List: {line feed} Bill is a friend {line feed} Mary is a friend
    Friend List: {line feed} Fred is a friend

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The Function below is SPECIFICALLY FOR THIS.

    It might be possible to do in a worksheet formula, but I will leave that for someone with greater knowledge.
    I have done it with a Custom Function written in a Module
    In the example it is in basFunctions

    The Function Requires TWO Cells as Input
    The Cell with the Numbers and the TOP Left Cell where the List Starts (Absolute).
    so here it would be entered in D1 as

    =GetNames(C1,$A$1)

    I have attached an example showing this

    Code:
    Function GetNames(rngNums As Range, rngStart As Range) As String
    
    'Note, this function has NO Error trapping added and is specific to the
    'example in the layout ONLY
    'Also it assumes that the names are in the order 1,2,3,4,5,6,7
    'Otherwise instead of a simple Offset, a Find would be needed
    
    'Also you need to have Text Wrap Turned ON to see Multiple Lines
    
    Dim intC As Integer, intNum As Integer, strWho As String, strName As String, strNames As String, strNums As String
    
    'Make sure it also Recalcs
    Application.Volatile
    'set Initial name String
    strNames = ""
    
    'Get basic Data
    strWho = rngNums.Offset(0, -1)
    strNames = strWho & "'s Friends:"
    strNums = rngNums
    
    'Test Data for Blank
    If strNums = "" Then
        strNames = strNames & vbCrLf & "N/A"
    Else
        'If NOT blank look for Commas in Loop and extract Numbers from list
        intC = InStr(rngNums, ",")
        Do Until intC = 0
            intNum = Trim(Left(strNums, intC - 1))
            strNums = Mid(strNums, intC + 1)
            strName = rngStart.Offset(intNum - 1, 1)
            strNames = strNames & vbCrLf & strName
            intC = InStr(strNums, ",")
        Loop
        'Deal with Last Number in the List
        intNum = Trim(strNums)
        strName = rngStart.Offset(intNum - 1, 1)
        strNames = strNames & vbCrLf & strName
    End If
    
    'Assign Value to Function
    GetNames = strNames
    
    End Function

    [attachment=87874:GetNames.jpg]



    [attachment=87873:GetNamesExample.xls]


    This also works in 2007
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the number of friends is AT MOST 7 (currently there are 6), this mega formula will work:
    =B1&"'s Friends:"&CHAR(10)&IF(C1="","N/A","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,$A $1,$B$1),$A$2,$B$2),$A$3,$B$3),$A$4,$B$4),$A$5,$B$ 5),$A$6,$B$6),$A$7,$B$7),",",CHAR(10))

    If there are more than 7 it will take some intermediate columns due to the limitations of nesting in formulas.

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    1) I get a non-printable character from the vbCrLf rather than a just a line feed.
    Is there a way to get my results to look like what you apparently got without the non-printable character (a small square) ?

    2) I also get an error when trying to download your attached Excel file.
    But, I got the function to work.

    Thanks for your function.



    Quote Originally Posted by AndrewKKWalker View Post
    The Function below is SPECIFICALLY FOR THIS.

    It might be possible to do in a worksheet formula, but I will leave that for someone with greater knowledge.
    I have done it with a Custom Function written in a Module
    In the example it is in basFunctions

    The Function Requires TWO Cells as Input
    The Cell with the Numbers and the TOP Left Cell where the List Starts (Absolute).
    so here it would be entered in D1 as

    =GetNames(C1,$A$1)

    I have attached an example showing this

    Code:
    Function GetNames(rngNums As Range, rngStart As Range) As String
    
    'Note, this function has NO Error trapping added and is specific to the
    'example in the layout ONLY
    'Also it assumes that the names are in the order 1,2,3,4,5,6,7
    'Otherwise instead of a simple Offset, a Find would be needed
    
    'Also you need to have Text Wrap Turned ON to see Multiple Lines
    
    Dim intC As Integer, intNum As Integer, strWho As String, strName As String, strNames As String, strNums As String
    
    'Make sure it also Recalcs
    Application.Volatile
    'set Initial name String
    strNames = ""
    
    'Get basic Data
    strWho = rngNums.Offset(0, -1)
    strNames = strWho & "'s Friends:"
    strNums = rngNums
    
    'Test Data for Blank
    If strNums = "" Then
        strNames = strNames & vbCrLf & "N/A"
    Else
        'If NOT blank look for Commas in Loop and extract Numbers from list
        intC = InStr(rngNums, ",")
        Do Until intC = 0
            intNum = Trim(Left(strNums, intC - 1))
            strNums = Mid(strNums, intC + 1)
            strName = rngStart.Offset(intNum - 1, 1)
            strNames = strNames & vbCrLf & strName
            intC = InStr(strNums, ",")
        Loop
        'Deal with Last Number in the List
        intNum = Trim(strNums)
        strName = rngStart.Offset(intNum - 1, 1)
        strNames = strNames & vbCrLf & strName
    End If
    
    'Assign Value to Function
    GetNames = strNames
    
    End Function

    [attachment=87874:GetNames.jpg]



    [attachment=87873:GetNamesExample.xls]


    This also works in 2007

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Use vbLF instead of vbCRLF

    Steve

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Also make sure that you have TEXT WRAP turned on on the cells, or you will see the SQUARE character.
    Andrew

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    PERFECT. Thanks to both of you!!

    Quote Originally Posted by sdckapr View Post
    Use vbLF instead of vbCRLF

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •