Lagi2 ini warisan si mbah,Copas Macro ini di module1...
Function LOUV(D As Range)
'--------------------------------------------------------------------- '
membuat daftar unique value berdasarkan range data ' coded by siti Vi /
feb2006, di bluewater /recoded: 1jul2007, di jkt
'--------------------------------------------------------------------- Dim
A(), x(), v, n As Integer, p As Integer, q As Integer, r As Integer ReDim A(1
To D.Cells.Count) For n = 1 To UBound(A): A(n) = D.Cells(n): Next n For n =
1 To UBound(A) - 1 For q = UBound(A) To n + 1 Step -1 p = q - 1
If A(p) > A(q) Then v = A(p): A(p) = A(q): A(q) = v
Else If A(p) = A(q) Then A(q) = "" End If Next q Next
n For n = 1 To UBound(A) If Not A(n) = "" Then r = r + 1: ReDim
Preserve x(1 To r): x(r) = A(n) End If Next n LOUV =
WorksheetFunction.Transpose(x)End Function
Blok/sorot sel D1 s/d D175Pada formula bar ketik : = Louv(B1:B176) dan akhiri
dengan menekan toambol Ctrl Shift Enter (array formula--> sehingga pada formula
bar akan tertulis tanda { } pada awal dan akhir formula.
tanda #N/A menunjukkan data sdh tidak ada
Selamat mencoba.
Salam,HK
NB : agar tidak harus berulang2 menghapus/menulis formula (UDF) di atas maka
sebaiknya rangenya disorot sekaligus, mis s/d 1000 baris sekaligus.
From: "It Dpm [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
Sent: Monday, 25 May 2015, 13:33
Subject: [belajar-excel] unique data [1 Attachment]
dear para master Excel,
Mohon bantuan atas pertanyaan bagaimana caranya supaya data di kolom D hanya
menampilkan data unique (tidak duplikat dan juga tidak kosong) dari kolom B
secara dinamis, jadi ketika kita mengubah data di kolom B maka otomatis di
kolom D menyesuaikan. terima kasih(contoh file terlampir)
#yiv9871869353 #yiv9871869353 -- #yiv9871869353ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9871869353
#yiv9871869353ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9871869353
#yiv9871869353ygrp-mkp #yiv9871869353hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv9871869353 #yiv9871869353ygrp-mkp #yiv9871869353ads
{margin-bottom:10px;}#yiv9871869353 #yiv9871869353ygrp-mkp .yiv9871869353ad
{padding:0 0;}#yiv9871869353 #yiv9871869353ygrp-mkp .yiv9871869353ad p
{margin:0;}#yiv9871869353 #yiv9871869353ygrp-mkp .yiv9871869353ad a
{color:#0000ff;text-decoration:none;}#yiv9871869353 #yiv9871869353ygrp-sponsor
#yiv9871869353ygrp-lc {font-family:Arial;}#yiv9871869353
#yiv9871869353ygrp-sponsor #yiv9871869353ygrp-lc #yiv9871869353hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9871869353
#yiv9871869353ygrp-sponsor #yiv9871869353ygrp-lc .yiv9871869353ad
{margin-bottom:10px;padding:0 0;}#yiv9871869353 #yiv9871869353actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9871869353
#yiv9871869353activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9871869353
#yiv9871869353activity span {font-weight:700;}#yiv9871869353
#yiv9871869353activity span:first-child
{text-transform:uppercase;}#yiv9871869353 #yiv9871869353activity span a
{color:#5085b6;text-decoration:none;}#yiv9871869353 #yiv9871869353activity span
span {color:#ff7900;}#yiv9871869353 #yiv9871869353activity span
.yiv9871869353underline {text-decoration:underline;}#yiv9871869353
.yiv9871869353attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv9871869353 .yiv9871869353attach div a
{text-decoration:none;}#yiv9871869353 .yiv9871869353attach img
{border:none;padding-right:5px;}#yiv9871869353 .yiv9871869353attach label
{display:block;margin-bottom:5px;}#yiv9871869353 .yiv9871869353attach label a
{text-decoration:none;}#yiv9871869353 blockquote {margin:0 0 0
4px;}#yiv9871869353 .yiv9871869353bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9871869353
.yiv9871869353bold a {text-decoration:none;}#yiv9871869353 dd.yiv9871869353last
p a {font-family:Verdana;font-weight:700;}#yiv9871869353 dd.yiv9871869353last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9871869353
dd.yiv9871869353last p span.yiv9871869353yshortcuts
{margin-right:0;}#yiv9871869353 div.yiv9871869353attach-table div div a
{text-decoration:none;}#yiv9871869353 div.yiv9871869353attach-table
{width:400px;}#yiv9871869353 div.yiv9871869353file-title a, #yiv9871869353
div.yiv9871869353file-title a:active, #yiv9871869353
div.yiv9871869353file-title a:hover, #yiv9871869353 div.yiv9871869353file-title
a:visited {text-decoration:none;}#yiv9871869353 div.yiv9871869353photo-title a,
#yiv9871869353 div.yiv9871869353photo-title a:active, #yiv9871869353
div.yiv9871869353photo-title a:hover, #yiv9871869353
div.yiv9871869353photo-title a:visited {text-decoration:none;}#yiv9871869353
div#yiv9871869353ygrp-mlmsg #yiv9871869353ygrp-msg p a
span.yiv9871869353yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9871869353
.yiv9871869353green {color:#628c2a;}#yiv9871869353 .yiv9871869353MsoNormal
{margin:0 0 0 0;}#yiv9871869353 o {font-size:0;}#yiv9871869353
#yiv9871869353photos div {float:left;width:72px;}#yiv9871869353
#yiv9871869353photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv9871869353
#yiv9871869353photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9871869353
#yiv9871869353reco-category {font-size:77%;}#yiv9871869353
#yiv9871869353reco-desc {font-size:77%;}#yiv9871869353 .yiv9871869353replbq
{margin:4px;}#yiv9871869353 #yiv9871869353ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv9871869353 #yiv9871869353ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9871869353
#yiv9871869353ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9871869353
#yiv9871869353ygrp-mlmsg select, #yiv9871869353 input, #yiv9871869353 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv9871869353
#yiv9871869353ygrp-mlmsg pre, #yiv9871869353 code {font:115%
monospace;}#yiv9871869353 #yiv9871869353ygrp-mlmsg *
{line-height:1.22em;}#yiv9871869353 #yiv9871869353ygrp-mlmsg #yiv9871869353logo
{padding-bottom:10px;}#yiv9871869353 #yiv9871869353ygrp-msg p a
{font-family:Verdana;}#yiv9871869353 #yiv9871869353ygrp-msg
p#yiv9871869353attach-count span {color:#1E66AE;font-weight:700;}#yiv9871869353
#yiv9871869353ygrp-reco #yiv9871869353reco-head
{color:#ff7900;font-weight:700;}#yiv9871869353 #yiv9871869353ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv9871869353 #yiv9871869353ygrp-sponsor
#yiv9871869353ov li a {font-size:130%;text-decoration:none;}#yiv9871869353
#yiv9871869353ygrp-sponsor #yiv9871869353ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv9871869353
#yiv9871869353ygrp-sponsor #yiv9871869353ov ul {margin:0;padding:0 0 0
8px;}#yiv9871869353 #yiv9871869353ygrp-text
{font-family:Georgia;}#yiv9871869353 #yiv9871869353ygrp-text p {margin:0 0 1em
0;}#yiv9871869353 #yiv9871869353ygrp-text tt {font-size:120%;}#yiv9871869353
#yiv9871869353ygrp-vital ul li:last-child {border-right:none
!important;}#yiv9871869353
