View Full Version : Formula stop Searching
Pajoooo
Oct 28, 2009, 03:53 PM
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!
JBeaucaire
Oct 30, 2009, 04:19 PM
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.
JBeaucaire
Oct 30, 2009, 04:32 PM
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:
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.
JBeaucaire
Oct 30, 2009, 04:32 PM
Duplicate, see above.
Pajoooo
Oct 31, 2009, 03:29 AM
What to say? You are simply THE BEST.
THANKS! THANKS! THANKS! THANKS! THANKS!
Pajoooo
Oct 31, 2009, 03:31 AM
What to say? You are simply THE BEST.
THANKS! THANKS! THANKS! THANKS! THANKS!
JBeaucaire
Oct 31, 2009, 02:46 PM
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.
Pajoooo
Nov 1, 2009, 07:41 AM
I was attempt "RATE ANSWER", but I get message "You must spread some Reputation around before giving it to JBeaucaire again.". How solve that?
JBeaucaire
Nov 1, 2009, 08:31 AM
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.