Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Vlook up & concatenate

  1. #1

    Vlook up & concatenate



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I read many posts but could not find any help for my problem. See attachment of an example.

    I want to Concatenate many expressions/words from the column B in one cell (N10), but only if the date in row 7 matches the date in cell M10 AND if the values in the column D are greater than zero.

    For N11 the date should match the date in the cell M11 and values >0 in column E

    and so on...

    Hos should the formula should look like to manage my problem.

    I tried a lot: lookupconcat, If(Concatenate(Vloopup... but could not find a solution)

    Thank you a lot for your help in advance.

    Example.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    To do that you will need a VBA user defined function. Excel doesn't have an efficient way to concatenate multiple cells based on conditions...

    hit CTRL+F11 and then Insert|New Module.

    Paste this code in the VB Editor:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    Then in the sheet, use formula:

    =SUBSTITUTE(TRIM(aconcat(IF(INDEX($D$8:$I$21,0,MATCH($M10,$D$7:$I$7,0))>0,$B$8:$B$21,"")," "))," ",",")

    confirm it with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around the formula.

    Then copy down


  3. #3
    WOW! That helps a lot. Thank you very much. Really appriciate you reply and this great webpage!

  4. #4
    NBVC, please help me out once more:

    In addition to the your reply, I need to include a second condition into the following formula:

    =SUBSTITUTE(TRIM(aconcat(IF(INDEX(Sheet1!$NH$5:$ABH$184;0;(MATCH(Sheet1!$NH$4:$ABH$4;Sheet2!$B8;0));(MATCH(Sheet1!$B$5:$B$184;Sheet2!$C8;0)))>0;Sheet1!$D$5:$D$184;"");" "));")";"), ")

    This is how the formula looked before and it worked:
    =SUBSTITUTE(TRIM(aconcat(IF(INDEX(Sheet1!$NH$5:$ABH$184;0;MATCH(Sheet1!$NH$4:$ABH$4;Sheet2!$B8;0))>0;Sheet1!$D$5:$D$184;"");" "));")";"), ")

    Now Sheet2!$C8 --> a name, e.g. Georg should match with a List of names among them Georg: Sheet1!$B$5:$B$184
    How can the second condition could be included in the formula in a way, that it work.

    Thank you very much again.

    Best regards

  5. #5
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0



    How to install your new code



    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)



    Code:
    Function MyConcat(rSource As Range, rMatch As Range, vMatCri As Variant, _
            rRes As Range, Optional opSep As String = " ") As String
    Dim i As Long, sTemp As String, sMatCri As String
    
    sMatCri = UCase(vMatCri)
    
    For i = 1 To rMatch.Cells.Count
        If UCase(rMatch.Cells(i).Value) = sMatCri Then
            If rSource.Cells(i).Value <> 0 Then sTemp = sTemp & rRes.Cells(i).Value & opSep
        End If
    Next i
    
    MyConcat = sTemp
    If Len(sTemp) Then MyConcat = Left(sTemp, Len(sTemp) - Len(opSep))
    
    End Function
    In N10 Cell

    =MyConcat(INDEX($D$8:$I$21,,MATCH(M10,$D$7:$I$7,0)),$C$8:$C$21,"Georg",$B$8:$B$21)

    Drag it down...

  6. #6
    Hi,

    unfortunately the code and the formula did not work out --> "VALUE"

    So once again:

    I want to Concatenate many expressions/words from the column B in one cell (N10), but only if the date in D7:I7 matches the date in cell L10 AND if the name in M10 matches with C8:C21 AND if the values in the column D8:I21 are greater than zero.

    For N11 the date should match the date in the cell L11, the name in M11 and values >0 in column E

    and so on...

    I attached an adjusted example. Example2.xlsm

    Really appriciate your effort and help.

  7. #7
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    Use the below formula in your Post #6 attachment file

    In N10 Cell

    =MyConcat(INDEX($D$8:$I$21,,MATCH(L10,$D$7:$I$7,0)),$C$8:$C$21,M10,$B$8:$B$21)

    Drag it down....

  8. #8
    Awesome, works very well.

    I have one final challenge, I am strugglin with.

    I want to sum up the hours in column D, if C8:C21 matches M10 AND D7:I7 matches L10

    See Example 3: Example3.xlsm

    I tried again a lot (SUM OFFSET, INDEX, but could not get a result and now it is a bit urgent )

    Thanks a lot for your help again.

  9. #9
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    Try this...

    =SUMIFS(INDEX($D$8:$I$21,,MATCH(L10,$D$7:$I$7,0)),$C$8:$C$21,M10)

    Drag it down...

  10. #10
    Unfortunately it always returns 0.
    I attached the original doc --> Maybe there is a problem because during the drag down the sum of the date-column across has to be counted?
    Aramis_Time tracking_Georg_ADU.xlsm

    Thanks a lot for further input

Page 1 of 2 1 2 LastLast

Posting Permissions

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