Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Formula stop Searching (https://www.askmehelpdesk.com/showthread.php?t=410663)

  • Oct 28, 2009, 03:53 PM
    Pajoooo
    1 Attachment(s)
    Formula stop Searching
    When I type (Sheet "Pretraga") e.g. "Popovi" in cell A2, in cell F2 must be 76300 BIJELJINA, 76218 RAZLJEVO, 75436 SKELANI (see Sheet "Mesta", red color).

    I get only first result of search: 76300 BIJELJINA.

    Thanks!
  • Oct 30, 2009, 04:19 PM
    JBeaucaire

    Can you mock up an example (without formulas, of course) of what you're trying to get as you results? I'd like to see your end goal.
  • Oct 30, 2009, 04:32 PM
    JBeaucaire
    1 Attachment(s)
    Ah, never mind, I understand.

    What you're after is called STRING CONCATENATION, merging multiple cells into one based on a criterion... and it is not a built in function to Excel.

    Fortunately, 1000s of people have faced and solved this with many User Defined Function. The one I use for your situation is called CONCATIF(). I've installed it into your sheet for you.

    Here's the code:
    Code:

    Option Explicit

    Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
        ' code base by Mike Rickson, MrExcel MVP
        ' used as exactly like SUMIF() with two additional parameters
        ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
        ' might include duplicates  ex. =ConcatIf($A$1:$A$10, C1, $B$1:$B$10, ",", True)

    Dim i As Long, j As Long

    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With

    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                        stringsRange.Column - compareRange.Column)
       
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
    End Function

    ===========
    How to install the User Defined Function:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet

    The function is installed and ready to use.

    ==========

    This function is like a SUMIF() formula with a couple of extra parameters.

    The formula in F2 that gives us that answer is:
    =CONCATIF(Mesta!$A$1:$A$100,$A2,Mesta!$E$1:$E$100, CHAR(10),FALSE)

    First parameter: Range to evaluate
    Second param: comparison string.
    Third param: Range to return values from
    Fourth param: The delimiting string for the concatenation, I'm using a CHAR(10) in this example which is Excel's way of saying "Line Feed"
    Fifth param: (optional) TRUE means eliminate duplicated values, FALSE (or omitted) means string all values even if duplicates appear

    NOTE: You do have to format those cells in column F to WRAP TEXT if you want this to display properly.
  • Oct 30, 2009, 04:32 PM
    JBeaucaire
    Duplicate, see above.
  • Oct 31, 2009, 03:29 AM
    Pajoooo

    What to say? You are simply THE BEST.

    THANKS! THANKS! THANKS! THANKS! THANKS!
  • Oct 31, 2009, 03:31 AM
    Pajoooo

    What to say? You are simply THE BEST.

    THANKS! THANKS! THANKS! THANKS! THANKS!
  • Oct 31, 2009, 02:46 PM
    JBeaucaire

    Thanks, but if you click on the RATE ANSWER button in the solution offered, it lets you add to my rep on the forum directly. Much appreciated.
  • Nov 1, 2009, 07:41 AM
    Pajoooo

    I was attempt "RATE ANSWER", but I get message "You must spread some Reputation around before giving it to JBeaucaire again.". How solve that?
  • Nov 1, 2009, 08:31 AM
    JBeaucaire

    Ah, you'll need to vote (good or bad) for someone else's message in another thread before the forum will let you add to my rep again. It's a failsafe to make sure friends don't just sit there and add rep to each other all day.

    No worries.

  • All times are GMT -7. The time now is 01:34 AM.