Formulanya: =GabungIf($C$4:$C$21,F4,$D$4:$D$21) [image: GabungBersyarat.PNG]
Function GabungIf(Rang1 As Range, Krite As Range, _
Optional Rang2 As Range = Nothing, _
Optional Dlmtr As String = ",") As String
'---------------------------------------
' indrihapsari / courtesy of mbak siti
' UDF menggabung bersyarat
' XL-mania case # 19651
'---------------------------------------
Dim Cel As Range, Arunik As String, k As String, n As Long
If Rang2 Is Nothing Then Set Rang2 = Rang1
Arunik = "|"
For n = 1 To Rang1.Rows.Count
If Rang1(n, 1) = Krite Then
k = Left(Rang2(n, 1), 2)
If InStr(1, Arunik, "|" & k & "|", vbTextCompare) = 0 Then
Arunik = Arunik & k & "|"
End If
End If
Next n
GabungIf = Replace(Arunik, "|", Dlmtr)
If Len(GabungIf) > 0 Then _
GabungIf = Mid(GabungIf, 2, Len(GabungIf) - (2 * Len(Dlmtr)))
End Function
2011/12/6 ari <[email protected]>
> **
>
> Salam para pakar Excel,
> saya ari baru bergabung per tanggal 06-12-2011, langsung saja saya mau
> bertanya tentang formula untuk mencari 2 karakter angka di depan terus
> ditambah koma lalu mecari data berikutnya.. sudah coba pake "=left" tapi ga
> sesuai yang diinginkan, mohon bantuannya. "Xl-Mania OK"....
> Terimakasih XL - Mania, sangat ditunggu Formulanya.. :)
>
<<GabungBersyarat.PNG>>
Perkenalkan Fungsi GabungIf (khusus).xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
