Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Loop to two columns and Concatenate values

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    9
    Rep Power
    0
    Last edited by DocAElstein; 09-22-2023 at 05:37 PM. Reason: Add Attachment

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Try this:
    In my case data started from cell A1 and output data started from H1 so please change code accordingly if you need some changes




    Code:
    Option Explicit
    
    Sub LMP_Test()
    
        Dim varArrData()                As Variant
        Dim varArrTemp()                As Variant
        Dim varArrTemp1()               As Variant
        Dim lngLoop                     As Long
        Dim lngIndex                    As Long
        Dim varVal1                     As Variant
        Dim varVal2                     As Variant
        Dim strOutput                   As String
    
        Const lngIDACol                 As Long = 2 'Change accordingly
        Const lngIDBCol                 As Long = 3 'Change accordingly
        Const lngStorCol                As Long = 4 'Change accordingly
        Const lngAvailCol               As Long = 5 'Change accordingly
        Const strDataStartCell          As String = "A1" 'Change accordingly
        Const strOutPutCell             As String = "H1" 'Change accordingly
        Const strSheetName              As String = "Sheet2" 'Change accordingly
        Const strConcatDelima           As String = " / " 'Change accordingly
        
        varArrData = ThisWorkbook.Worksheets(strSheetName).Range(strDataStartCell).CurrentRegion.Value
        varArrTemp = varArrData
        varArrTemp1 = varArrTemp
        For lngLoop = LBound(varArrTemp) + 1 To UBound(varArrTemp)
            'varVal1 = varArrTemp1(lngLoop, lngIDACol)
            varVal2 = varArrTemp1(lngLoop, lngIDBCol)
            strOutput = vbNullString
            strOutput = varArrTemp1(lngLoop, lngStorCol) & IIf(strOutput <> "", strConcatDelima, "") & strOutput
    DoLoop:
            If varVal2 = "" Then GoTo ContinueForLoop
            lngIndex = GetArrayIndex(varVal2, varArrTemp1, lngIDACol)
            If lngIndex > 0 Then
                strOutput = varArrTemp1(lngIndex, lngStorCol) & IIf(strOutput <> "", strConcatDelima, "") & strOutput
                'varVal1 = varArrTemp1(lngIndex, lngIDACol)
                varVal2 = varArrTemp1(lngIndex, lngIDBCol)
            Else
                lngIndex = 0
                varVal1 = vbNullString
                varVal2 = vbNullString
            End If
            lngIndex = 0
            GoTo DoLoop
    ContinueForLoop:
            varArrTemp1(lngLoop, lngAvailCol) = strOutput
            strOutput = vbNullString
        Next lngLoop
        With ThisWorkbook.Worksheets(strSheetName).Range(strOutPutCell)
            .Resize(, UBound(varArrTemp1, 2)).EntireColumn.Clear
            .Resize(UBound(varArrTemp1), UBound(varArrTemp1, 2)).Value = varArrTemp1
        End With
        
        Erase varArrData
        Erase varArrTemp
        Erase varArrTemp1
        lngLoop = Empty
        lngIndex = Empty
        varVal1 = Empty
        varVal2 = Empty
        strOutput = vbNullString
    
    End Sub
    
    Function GetArrayIndex(ByVal Val As Variant, ByVal varArr As Variant, Optional lngColNo As Long = 1) As Long
    
        Dim varDataArr              As Variant
        
        GetArrayIndex = 0
        On Error Resume Next
        With WorksheetFunction
            varDataArr = .Index(Application.Transpose(varArr), lngColNo)
            GetArrayIndex = .Match(Val, varDataArr, 0)
        End With
        On Error GoTo -1: On Error GoTo 0: Err.Clear
        
        varDataArr = Empty
    
    End Function



    Last edited by LalitPandey87; 04-13-2013 at 11:24 AM.

  3. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    9
    Rep Power
    0
    unable to see the codes..

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I see your code does what the OP showed for his small sample data set, but I am not convinced it is an all-encompassing solution although I cannot tell for sure because, after repeated efforts in the original forum, I cannot seem to get him to post a larger data sample. Another reason I am unsure if your code is the "ultimate" solution has to deal with the question I raised back in the other forum...

    Loop To Last EndPt

    Once the OP answers that question and provides a larger data set to see how it all applies, I think then we will know if your code "does it all" or not and, if not, we should be able to figure out how to modify it so that it does.

  5. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    9
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    I see your code does what the OP showed for his small sample data set, but I am not convinced it is an all-encompassing solution although I cannot tell for sure because, after repeated efforts in the original forum, I cannot seem to get him to post a larger data sample. Another reason I am unsure if your code is the "ultimate" solution has to deal with the question I raised back in the other forum...

    Loop To Last EndPt

    Once the OP answers that question and provides a larger data set to see how it all applies, I think then we will know if your code "does it all" or not and, if not, we should be able to figure out how to modify it so that it does.

    Hi Rick,

    I just add much larger data for this forum with expected output. Please have a look.

    br,
    ivan

  6. #6
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Last edited by DocAElstein; 03-01-2024 at 02:55 PM.

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by LalitPandey87 View Post
    where is the data. i am not able to see it or you missed it
    I think the OP is answering questions across forums. The data he posted in his attachment in this forum is more than the sample data he posted in the MrExcel forum and I believe his last message is just telling me that as a response to my message (#4) in this thread. Unfortunately, the OP still did not provide an explanation of why the methods of moving upward from the blanks is handled differently for the two spaces he showed in the sample data he posted to the MrExcel forum (unfortunately, his attached file in this forum does not show the problem he exposed with his sample data in the MrExcel forum); hence, I am still confused as to what the underlying rule is for filling out the Avail column; so, unless he posts what that underlying rule is, I know I will not be able to help him further... hopefully you are understanding it sufficiently to carry on. I'll jump back in if the OP eventually posts a clear description explaining the problem I wrote to him about for his MrExcel sample data.

  8. #8
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    My code is giving exact the same output as he shown in the attachment.

  9. #9
    Junior Member
    Join Date
    Apr 2013
    Posts
    9
    Rep Power
    0
    Quote Originally Posted by LalitPandey87 View Post
    My code is giving exact the same output as he shown in the attachment.
    @ LalitPandey87, thanks it works fine.

    What should i edit to only have the results (col Avail), instead of duplicating the whole table.

    br,
    ivan

  10. #10
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Change this line



    Code:
        Const strOutPutCell             As String = "H1" 'Change accordingly



    with this one



    Code:
        Const strOutPutCell             As String = "A1" 'Change accordingly



Similar Threads

  1. Vlookup Multiple Values By Adding Formula With Loop In VBA
    By Safal Shrestha in forum Excel Help
    Replies: 15
    Last Post: 04-22-2013, 04:49 PM
  2. Function to Concatenate Values
    By Admin in forum Download Center
    Replies: 1
    Last Post: 12-13-2012, 11:38 AM
  3. Concatenate multiple values
    By mcpizzle in forum Excel Help
    Replies: 3
    Last Post: 08-30-2012, 04:03 PM
  4. Loop and Check For Values In Entire Column in Excel
    By Jeff5019 in forum Excel Help
    Replies: 3
    Last Post: 05-01-2012, 10:34 PM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 PM

Posting Permissions

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