Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
adee pe Simpatie
 | Femeie
24 ani
Mures
cauta Barbat
24 - 59 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
You can't do this natively, with default excel functions, but you can do it with VBA created functions.
Source:
You can create this in a module or in the sheet's code, but if you want it available in all workbooks without having to add it manually to each, you can add it to a module in personal.xlsb, BUT you will have to call it:
=PERSONAL.XLSB!ConcatenateIfs($F$3:$F$12;$F$3:$F$12;F3;" ")
Function is called ConcatenateIfs (plural Ifs) because you can add multiple conditions. Comment from the source above:
' Source: EileensLounge.com, August 2014
'
'Syntax: _
=ConcatenateIfs(ConcatenateRange, CriteriaRange1, Operator1, Condition1, CriteriaRange2, Operator2, Condition2, ..., Separator) _
ConcatenateRange: required; it is the range containing the values you want to concatenate. _
CriteriaRange1: required; the first range to check _
Operator1: required, the comparison operator "=", "<>", "<", "<=", ">" or ">=" _
Condition1: required, the value to match with the first criteria range _
CriteriaRange2: optional; the second range to check _
Operator2: optional, the second comparison operator _
Condition2: optional, but required if CriteriaRange2 has been specified; the value to match with the second criteria range _
... _
Separator: optional; the string that separates the values in the result. If you omit it, "," is used _
Example: _
=ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b") _
or _
=ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b", " - ") |
Function ConcatenateIfsBetter(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim strResult As String
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 3 Then
' Too few arguments
GoTo ErrHandler
End If
If n Mod 3 = 0 Then
' Separator specified explicitly
Separator = Criteria(n)
Else
' Use default separator
Separator = ","
End If
' Loop through the cells of the concatenate range
For i = 1 To ConcatenateRange.Count
' Start by assuming that we have a match
f = True
' Loop through the conditions
For c = 0 To n - 1 Step 3
' Does cell in criteria range match the condition?
Select Case Criteria(c + 1)
Case "<="
If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
f = False
Exit For
End If
Case "<"
If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
f = False
Exit For
End If
Case ">="
If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
f = False
Exit For
End If
Case ">"
If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
f = False
Exit For
End If
Case "<>"
If Criteria(c).Cells(i).Value = Criteria(c + 2) Then
f = False
Exit For
End If
Case Else
If Criteria(c).Cells(i).Value <> Criteria(c + 2) Then
f = False
Exit For
End If
End Select
Next c
' Were all criteria satisfied?
If f Then
' If so, add separator and value to result
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
' Remove first separator
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfsBetter = strResult
Exit Function
ErrHandler:
ConcatenateIfsBetter = CVErr(xlErrValue)
End Function |
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Alternatively you can use an add-in called Morefunc:
See more functions on this topic:
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Concatenate with 1 condition:
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function |
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Sort for the function in post 1 and also NO DUPLICATES
Source:
Syntax: =ConcatenateIfsSort(ConcatenateRange, CriteriaRange1, Operator1, Condition1, CriteriaRange2, Operator2, Condition2, ..., Separator)
The example I made with 2 conditions is:
=PERSONAL.XLSB!ConcatenateIfsSort($C$2:$C$1309;$A$2:$A$1309;"=";Sheet1!A2;$G$2:$G$1309;"=";$G$2;"; ")
Function ConcatenateIfsSort(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
' Source: EileensLounge.com, August 2014
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim strResult As String
Dim col As Collection
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 3 Then
' Too few arguments
GoTo ErrHandler
End If
If n Mod 3 = 0 Then
' Separator specified explicitly
Separator = Criteria(n)
Else
' Use default separator
Separator = ","
End If
' Initialize collection of unique items
Set col = New Collection
' Loop through the cells of the concatenate range
For i = 1 To ConcatenateRange.Count
' Start by assuming that we have a match
f = True
' Loop through the conditions
For c = 0 To n - 1 Step 3
' Does cell in criteria range match the condition?
Select Case Criteria(c + 1)
Case "<="
If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
f = False
Exit For
End If
Case "<"
If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
f = False
Exit For
End If
Case ">="
If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
f = False
Exit For
End If
Case ">"
If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
f = False
Exit For
End If
Case "<>"
If Criteria(c).Cells(i).Value = Criteria(c + 2) Then
f = False
Exit For
End If
Case Else
If Criteria(c).Cells(i).Value <> Criteria(c + 2) Then
f = False
Exit For
End If
End Select
Next c
' Were all criteria satisfied?
If f Then
' If so, add value to collection, if it has not been added yet
On Error Resume Next
col.Add Item:=ConcatenateRange.Cells(i).Value, _
Key:=CStr(ConcatenateRange.Cells(i).Value)
On Error GoTo ErrHandler
End If
Next i
If col.Count > 0 Then
' Sort the results
SortCollection col
' Concatenate them
For i = 1 To col.Count
strResult = strResult & Separator & col(i)
Next i
' Remove first separator
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfsSort = strResult
Exit Function
ErrHandler:
ConcatenateIfsSort = CVErr(xlErrValue)
End Function
Sub SortCollection(col As Collection)
Dim i As Long
Dim j As Long
Dim tmp As Variant
For i = 1 To col.Count - 1
For j = i + 1 To col.Count
If col(j) < col(i) Then
tmp = col(j)
col.Remove Index:=j
col.Add Item:=tmp, Key:=CStr(tmp), Before:=i
End If
Next j
Next i
End Sub |
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Concatenate with no condition required:
Use formula like: =PERSONAL.xlsb!ConcatenateCells(A1:A40)
Function ConcatenateCells(ConcatArea As Range) As String
' this one adds a delimiter ; after the last cell string
' =PERSONAL.xlsb!ConcatenateCells(A1:A40)
For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "; "): Next
ConcatenateCells = Left(nn, Len(nn) - 1)
End Function |
|
|
| pus acum 4 ani |
|