Simplification of IF statement

[shantraj.antin@gmail.com]

Dear All,

In "summary" sheet of the attached template I have consolidated the availability of TL in Column E from "TL Details" sheet info using IF statement.

Here anyways I am getting the expected result by putting lengthy If statements. Is there a way that we can simplify function here? May be using ranges in function.

Kindly help me to learn something new!!

 

[Faseeh]

Hi shantraj,

Please see this UDF from Rick Rothstein's page for conditional concatenation:

http://www.excelfox.com/forum/f22/lookup-value-and-concatenate-all-found-results-345/

Here is the code:

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
                      Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
               
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String

  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
    (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
 
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If

End Function

 

[shantraj.antin@gmail.com]

Thanks a lot Faseeh, I will try with the above code.

 

[shantraj.antin@gmail.com]

Hi Faseeh,

I added the code to vba module and tried to use the UDF in my attached file.

But I am confused while selecting the input details for this UDF.

If you dont mind can you please help me with one example on how to use this UDF in my attached file.

Thanks in advance.

 

[NARAYANK991]

Hi Shantraj ,

See your file ; I have done the first three.

Narayan

 

[shantraj.antin@gmail.com]

Hi Shantraj ,

See your file ; I have done the first three.

Narayan

Thanks Narayan.

It works. Excellent!!