Setelah diutak-atik, penggunaan Formula yg disajikan Mas Dede dan Mas Kenang
hasilnya sama2 bisa digunakan "Mantap Mas"....It's Work.
Namun benar apa yang disampaikan Mas Kenang, berhubung datanya banyak (hingga
saat ini data sudah mendekati 20-an ribu) maka kerja excel jadi lamban .Tapi
kerja excel tidak terlalu lamban ketika menggunakan define name untuk range
cell (rumus dari Mas Kid) hehe....
Sehingga formula :
{=TEXT(LARGE(SUBSTITUTE(IF((Data!$A2:$A45=$A$2),(Data!$B2:$B45),0),".","")*1,RIGHT(C1,2)),"#####-##-###-###")}
Menjadi
:{=TEXT(LARGE(SUBSTITUTE(IF((Id=$A$2),(File),0),".","")*1,RIGHT(C1,2)),"#####.##.###.###")}
Id =Data!$A$2:INDEX(Data!$A:$A;MAX(2;COUNTA(Data!$A:$A)))File
=Data!$B$2:INDEX(Data!$B:$B;MAX(2;COUNTA(Data!$B:$B)))
Salam,Leo
On Friday, November 7, 2014 7:25 AM, "Nang Agus [email protected]
[belajar-excel]" <[email protected]> wrote:
Bisa tanpa kolom bantu, sayangnya formula array, jika datanya banyak
serasa kerja excel jadi lamban.
{=TEXT(LARGE(SUBSTITUTE(IF((Data!$A2:$A45=$A$2),(Data!$B2:$B45),0),".","")*1,RIGHT(C1,2)),"#####-##-###-###")}
Gunakan tombol CSE untuk memasukkannya
2014-11-06 14:29 GMT+07:00 leo pcs [email protected] [belajar-excel]
<[email protected]>:
Slmt Siang Be-Excel,
Bagaimana caranya menyajikan semua informasi yg diinginkan...file terlampir.
salam,Leo
#yiv9879028082 -- #yiv9879028082ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9879028082
#yiv9879028082ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9879028082
#yiv9879028082ygrp-mkp #yiv9879028082hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv9879028082 #yiv9879028082ygrp-mkp #yiv9879028082ads
{margin-bottom:10px;}#yiv9879028082 #yiv9879028082ygrp-mkp .yiv9879028082ad
{padding:0 0;}#yiv9879028082 #yiv9879028082ygrp-mkp .yiv9879028082ad p
{margin:0;}#yiv9879028082 #yiv9879028082ygrp-mkp .yiv9879028082ad a
{color:#0000ff;text-decoration:none;}#yiv9879028082 #yiv9879028082ygrp-sponsor
#yiv9879028082ygrp-lc {font-family:Arial;}#yiv9879028082
#yiv9879028082ygrp-sponsor #yiv9879028082ygrp-lc #yiv9879028082hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9879028082
#yiv9879028082ygrp-sponsor #yiv9879028082ygrp-lc .yiv9879028082ad
{margin-bottom:10px;padding:0 0;}#yiv9879028082 #yiv9879028082actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9879028082
#yiv9879028082activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9879028082
#yiv9879028082activity span {font-weight:700;}#yiv9879028082
#yiv9879028082activity span:first-child
{text-transform:uppercase;}#yiv9879028082 #yiv9879028082activity span a
{color:#5085b6;text-decoration:none;}#yiv9879028082 #yiv9879028082activity span
span {color:#ff7900;}#yiv9879028082 #yiv9879028082activity span
.yiv9879028082underline {text-decoration:underline;}#yiv9879028082
.yiv9879028082attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv9879028082 .yiv9879028082attach div a
{text-decoration:none;}#yiv9879028082 .yiv9879028082attach img
{border:none;padding-right:5px;}#yiv9879028082 .yiv9879028082attach label
{display:block;margin-bottom:5px;}#yiv9879028082 .yiv9879028082attach label a
{text-decoration:none;}#yiv9879028082 blockquote {margin:0 0 0
4px;}#yiv9879028082 .yiv9879028082bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9879028082
.yiv9879028082bold a {text-decoration:none;}#yiv9879028082 dd.yiv9879028082last
p a {font-family:Verdana;font-weight:700;}#yiv9879028082 dd.yiv9879028082last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9879028082
dd.yiv9879028082last p span.yiv9879028082yshortcuts
{margin-right:0;}#yiv9879028082 div.yiv9879028082attach-table div div a
{text-decoration:none;}#yiv9879028082 div.yiv9879028082attach-table
{width:400px;}#yiv9879028082 div.yiv9879028082file-title a, #yiv9879028082
div.yiv9879028082file-title a:active, #yiv9879028082
div.yiv9879028082file-title a:hover, #yiv9879028082 div.yiv9879028082file-title
a:visited {text-decoration:none;}#yiv9879028082 div.yiv9879028082photo-title a,
#yiv9879028082 div.yiv9879028082photo-title a:active, #yiv9879028082
div.yiv9879028082photo-title a:hover, #yiv9879028082
div.yiv9879028082photo-title a:visited {text-decoration:none;}#yiv9879028082
div#yiv9879028082ygrp-mlmsg #yiv9879028082ygrp-msg p a
span.yiv9879028082yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9879028082
.yiv9879028082green {color:#628c2a;}#yiv9879028082 .yiv9879028082MsoNormal
{margin:0 0 0 0;}#yiv9879028082 o {font-size:0;}#yiv9879028082
#yiv9879028082photos div {float:left;width:72px;}#yiv9879028082
#yiv9879028082photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv9879028082
#yiv9879028082photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9879028082
#yiv9879028082reco-category {font-size:77%;}#yiv9879028082
#yiv9879028082reco-desc {font-size:77%;}#yiv9879028082 .yiv9879028082replbq
{margin:4px;}#yiv9879028082 #yiv9879028082ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv9879028082 #yiv9879028082ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9879028082
#yiv9879028082ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9879028082
#yiv9879028082ygrp-mlmsg select, #yiv9879028082 input, #yiv9879028082 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv9879028082
#yiv9879028082ygrp-mlmsg pre, #yiv9879028082 code {font:115%
monospace;}#yiv9879028082 #yiv9879028082ygrp-mlmsg *
{line-height:1.22em;}#yiv9879028082 #yiv9879028082ygrp-mlmsg #yiv9879028082logo
{padding-bottom:10px;}#yiv9879028082 #yiv9879028082ygrp-msg p a
{font-family:Verdana;}#yiv9879028082 #yiv9879028082ygrp-msg
p#yiv9879028082attach-count span {color:#1E66AE;font-weight:700;}#yiv9879028082
#yiv9879028082ygrp-reco #yiv9879028082reco-head
{color:#ff7900;font-weight:700;}#yiv9879028082 #yiv9879028082ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv9879028082 #yiv9879028082ygrp-sponsor
#yiv9879028082ov li a {font-size:130%;text-decoration:none;}#yiv9879028082
#yiv9879028082ygrp-sponsor #yiv9879028082ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv9879028082
#yiv9879028082ygrp-sponsor #yiv9879028082ov ul {margin:0;padding:0 0 0
8px;}#yiv9879028082 #yiv9879028082ygrp-text
{font-family:Georgia;}#yiv9879028082 #yiv9879028082ygrp-text p {margin:0 0 1em
0;}#yiv9879028082 #yiv9879028082ygrp-text tt {font-size:120%;}#yiv9879028082
#yiv9879028082ygrp-vital ul li:last-child {border-right:none
!important;}#yiv9879028082