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 

   

Kirim email ke