Vlook up & concatenate

D

dreschkov

New member
Joined
Feb 26, 2014
Messages
10
Reaction score
0
Points
0
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.

View attachment Example.xlsx
 
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
 
WOW! That helps a lot. Thank you very much. Really appriciate you reply and this great webpage!
 
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
 



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: 
[COLOR=darkblue]Function[/COLOR] MyConcat(rSource [COLOR=darkblue]As[/COLOR] Range, rMatch [COLOR=darkblue]As[/COLOR] Range, vMatCri [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], _
        rRes [COLOR=darkblue]As[/COLOR] Range, [COLOR=darkblue]Optional[/COLOR] opSep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = " ") [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], sTemp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], sMatCri [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]

sMatCri = UCase(vMatCri)

[COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] rMatch.Cells.Count
    [COLOR=darkblue]If[/COLOR] UCase(rMatch.Cells(i).Value) = sMatCri [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] rSource.Cells(i).Value <> 0 [COLOR=darkblue]Then[/COLOR] sTemp = sTemp & rRes.Cells(i).Value & opSep
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]Next[/COLOR] i

MyConcat = sTemp
[COLOR=darkblue]If[/COLOR] Len(sTemp) [COLOR=darkblue]Then[/COLOR] MyConcat = Left(sTemp, Len(sTemp) - Len(opSep))

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

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...
 
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. View attachment Example2.xlsm

Really appriciate your effort and help.
 
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....
 
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: View attachment 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.
 
Try this...

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

Drag it down...
 
The calculation process is not happening completely... In the status bar its showing the Calculate button which is not even making excel to complete the calculation and keep on showing the calculate button after hitting the F9 and Shift+F9 also...

May be that is the root cause of this issue I believe :(
 
Is there any possibility to solve the problem? I checked the data, but cannot find/eliminate the cause
 
It is working now. Great stuff --> Thank you for the support!
 
Actually, there was just a problem with the format of the cell or sth. else.
After I dragged down your first formula
=MyConcat(INDEX($D$8:$I$21,,MATCH(L10,$D$7:$I$7,0)),$C$8:$C$21,M10,$B$8:$B$21) and changed it to
=SUMIFS(INDEX($D$8:$I$21,,MATCH(L10,$D$7:$I$7,0)),$C$8:$C$21,M10)
it all worked out. :)
 
Hi; I have to come back once more on the formula:
=SUMIFS(INDEX($D$8:$I$21,,MATCH(L10,$D$7:$I$7,0)),$C$8:$C$21,M10)

How can I include in the above formula a third IF: $D$8:$D$21 is not equal to "Other"?

Thank you for your help in advance?
 
Try:

=SUMIFS(INDEX($D$8:$I$21,,MATCH(L10,$D$7:$I$7,0)),$C$8:$C$21,M10,$D$8:$D$21,"<>Other")
 
You must log in or register to reply here.
Back
Top