Maaf Pak Odong
Bila misalkan dengan pada kolom C3 sd C2532 ada data yang sama 2 data atau
lebih, bagaimana cara mengambil salah satu dari masing2 data yang sama dan
memisahkannya dengan criteria kolom/row F20, Mohon Formulanya Pak
TerimakasihSalam
Pada Rabu, 28 Oktober 2015 17:56, "odong nando odongna...@yahoo.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
yoo...hairul,bisa dicoba di cell
:G3=IF($F$20="";MID(INDIRECT("C"&COLUMNS($F$2:F2)+2);9;10);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2));MID(INDEX($C$3:$C$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)-1);9;10);""))
G4=IF($F$20="";IF(INDIRECT("D"&COLUMNS($F$2:F2)+2)=0;"";1);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2));IF(INDEX($D$3:$D$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)-1);1;"");""))
G6=IF($F$20="";MID(INDIRECT("C"&COLUMNS($F$2:F2)+64);9;10);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2)+62);MID(INDEX($C$3:$C$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)+61);9;10);""))
G7=IF($F$20="";IF(INDIRECT("D"&COLUMNS($F$2:F2)+64)=0;"";1);IF(($F$20<>"")*(COUNTIF($B$3:$B$2528;$F$20)>=COLUMNS($F$2:F2)+62);IF(INDEX($D$3:$D$2528;MATCH($F$20;$B$3:$B$2528;0)+COLUMNS($F$2:F2)+61);1;"");""))
perhatikan daerah yang di arsir kuning dan ungu, G3 menjadi G6 dan G4 menjadi
G7untuk G9,G10,G12,G13,G15 & G16, pada daerah terarsir ungu dapat di tambah
masing2 62, sesuai dengan jumlah kolom dari G s.d BP (62 kolom)
bila regional setting anda english maka ubah titikkoma ";" menjadi koma ",".
br,
odong nando
Pada Rabu, 28 Oktober 2015 15:01, "HAIRUL ALAM ha_m...@yahoo.co.id
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Yth. Para Master Excel
Assalamualaikum Wr. Wb.
Saya mengalami problem seperti data terlampir, Mohon Pencerahannya, untuk
bekerja secara formula dan otomatis agar Excel bekerja tidak berat karena
kebanyakan formula yang memakan memory
Wassalamualaikum Wr. Wb.
TerimakasihHairul Alam
#yiv6175729496 #yiv6175729496 -- #yiv6175729496ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6175729496
#yiv6175729496ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6175729496
#yiv6175729496ygrp-mkp #yiv6175729496hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv6175729496 #yiv6175729496ygrp-mkp #yiv6175729496ads
{margin-bottom:10px;}#yiv6175729496 #yiv6175729496ygrp-mkp .yiv6175729496ad
{padding:0 0;}#yiv6175729496 #yiv6175729496ygrp-mkp .yiv6175729496ad p
{margin:0;}#yiv6175729496 #yiv6175729496ygrp-mkp .yiv6175729496ad a
{color:#0000ff;text-decoration:none;}#yiv6175729496 #yiv6175729496ygrp-sponsor
#yiv6175729496ygrp-lc {font-family:Arial;}#yiv6175729496
#yiv6175729496ygrp-sponsor #yiv6175729496ygrp-lc #yiv6175729496hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6175729496
#yiv6175729496ygrp-sponsor #yiv6175729496ygrp-lc .yiv6175729496ad
{margin-bottom:10px;padding:0 0;}#yiv6175729496 #yiv6175729496actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6175729496
#yiv6175729496activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6175729496
#yiv6175729496activity span {font-weight:700;}#yiv6175729496
#yiv6175729496activity span:first-child
{text-transform:uppercase;}#yiv6175729496 #yiv6175729496activity span a
{color:#5085b6;text-decoration:none;}#yiv6175729496 #yiv6175729496activity span
span {color:#ff7900;}#yiv6175729496 #yiv6175729496activity span
.yiv6175729496underline {text-decoration:underline;}#yiv6175729496
.yiv6175729496attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv6175729496 .yiv6175729496attach div a
{text-decoration:none;}#yiv6175729496 .yiv6175729496attach img
{border:none;padding-right:5px;}#yiv6175729496 .yiv6175729496attach label
{display:block;margin-bottom:5px;}#yiv6175729496 .yiv6175729496attach label a
{text-decoration:none;}#yiv6175729496 blockquote {margin:0 0 0
4px;}#yiv6175729496 .yiv6175729496bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6175729496
.yiv6175729496bold a {text-decoration:none;}#yiv6175729496 dd.yiv6175729496last
p a {font-family:Verdana;font-weight:700;}#yiv6175729496 dd.yiv6175729496last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6175729496
dd.yiv6175729496last p span.yiv6175729496yshortcuts
{margin-right:0;}#yiv6175729496 div.yiv6175729496attach-table div div a
{text-decoration:none;}#yiv6175729496 div.yiv6175729496attach-table
{width:400px;}#yiv6175729496 div.yiv6175729496file-title a, #yiv6175729496
div.yiv6175729496file-title a:active, #yiv6175729496
div.yiv6175729496file-title a:hover, #yiv6175729496 div.yiv6175729496file-title
a:visited {text-decoration:none;}#yiv6175729496 div.yiv6175729496photo-title a,
#yiv6175729496 div.yiv6175729496photo-title a:active, #yiv6175729496
div.yiv6175729496photo-title a:hover, #yiv6175729496
div.yiv6175729496photo-title a:visited {text-decoration:none;}#yiv6175729496
div#yiv6175729496ygrp-mlmsg #yiv6175729496ygrp-msg p a
span.yiv6175729496yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6175729496
.yiv6175729496green {color:#628c2a;}#yiv6175729496 .yiv6175729496MsoNormal
{margin:0 0 0 0;}#yiv6175729496 o {font-size:0;}#yiv6175729496
#yiv6175729496photos div {float:left;width:72px;}#yiv6175729496
#yiv6175729496photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv6175729496
#yiv6175729496photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6175729496
#yiv6175729496reco-category {font-size:77%;}#yiv6175729496
#yiv6175729496reco-desc {font-size:77%;}#yiv6175729496 .yiv6175729496replbq
{margin:4px;}#yiv6175729496 #yiv6175729496ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv6175729496 #yiv6175729496ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6175729496
#yiv6175729496ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6175729496
#yiv6175729496ygrp-mlmsg select, #yiv6175729496 input, #yiv6175729496 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv6175729496
#yiv6175729496ygrp-mlmsg pre, #yiv6175729496 code {font:115%
monospace;}#yiv6175729496 #yiv6175729496ygrp-mlmsg *
{line-height:1.22em;}#yiv6175729496 #yiv6175729496ygrp-mlmsg #yiv6175729496logo
{padding-bottom:10px;}#yiv6175729496 #yiv6175729496ygrp-msg p a
{font-family:Verdana;}#yiv6175729496 #yiv6175729496ygrp-msg
p#yiv6175729496attach-count span {color:#1E66AE;font-weight:700;}#yiv6175729496
#yiv6175729496ygrp-reco #yiv6175729496reco-head
{color:#ff7900;font-weight:700;}#yiv6175729496 #yiv6175729496ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv6175729496 #yiv6175729496ygrp-sponsor
#yiv6175729496ov li a {font-size:130%;text-decoration:none;}#yiv6175729496
#yiv6175729496ygrp-sponsor #yiv6175729496ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv6175729496
#yiv6175729496ygrp-sponsor #yiv6175729496ov ul {margin:0;padding:0 0 0
8px;}#yiv6175729496 #yiv6175729496ygrp-text
{font-family:Georgia;}#yiv6175729496 #yiv6175729496ygrp-text p {margin:0 0 1em
0;}#yiv6175729496 #yiv6175729496ygrp-text tt {font-size:120%;}#yiv6175729496
#yiv6175729496ygrp-vital ul li:last-child {border-right:none
!important;}#yiv6175729496
