Yth Pak KID
Terimakasih, penjelasannya, saya pelajari lagi
SalamHairul Alam
Pada Kamis, 5 November 2015 15:55, "'Mr. Kid' mr.nm...@gmail.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Wa'alakimussalam wr wb
Hai Hairul,
array formula filtering unique :
=Index( data , Match( Sum( Countif( data , all_hasil_sebelumnya ) ) , CountIF(
data , "<" & data ) , 0 ) )
*** operator < untuk Asc, > untuk Desc.
Data ada yang blank (entah blank beneran atau nullstring) :
=Index( data , Match( Sum( Countif( data , all_hasil_sebelumnya ) ) , (
data="" ) + CountIF( data , "<" & data ) , 0 ) )
*** operator < untuk Asc, > untuk Desc.
Sepertinya berbunyi demikian...
Catatan :
Array formula yang mengolah banyak data dan dipasang di banyak cells akan
membuat proses kalkulasi Excel menjadi terkesan lambat.
Wassalamu'alaikum wr wb
Kid
Pada Rabu, 4 November 2015 6:39, HAIRUL ALAM <ha_m...@yahoo.co.id>
menulis:
AssalamualaikumPak Kid
saya sudah coba seperti contoh terlampir, bagaimana caranya agar data unit bisa
berurut bila di data inputnya terdapat cell yang kosong seperti lampiran,
dimana nilai pada outputnya "0"
TerimakasihWassalamualaikum
Hairul Alam
Pada Rabu, 4 November 2015 6:08, HAIRUL ALAM <ha_m...@yahoo.co.id> menulis:
Assalamualaikum Pak Kid
Saya sudah lihat di Array formula filtering unique, di baris paling bawah
muncul (#N/A) gimana cara menghilangkan kata tersebut bila dengan rumus yang
tertera
TerimakasihWassalamualaikum
Pada Selasa, 3 November 2015 23:14, "'Mr. Kid' mr.nm...@gmail.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Hai Hairul,
Array formula filtering unique ?
Coba jalan-jalan kesini dan terapkan konsepnya (bukan bunyi formula persisnya)
pada kasus Anda.
Wassalam,
Kid
On Mon, Nov 2, 2015 at 10:10 PM, HAIRUL ALAM ha_m...@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
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
#yiv8570679664 #yiv8570679664 -- #yiv8570679664ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8570679664
#yiv8570679664ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8570679664
#yiv8570679664ygrp-mkp #yiv8570679664hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8570679664 #yiv8570679664ygrp-mkp #yiv8570679664ads
{margin-bottom:10px;}#yiv8570679664 #yiv8570679664ygrp-mkp .yiv8570679664ad
{padding:0 0;}#yiv8570679664 #yiv8570679664ygrp-mkp .yiv8570679664ad p
{margin:0;}#yiv8570679664 #yiv8570679664ygrp-mkp .yiv8570679664ad a
{color:#0000ff;text-decoration:none;}#yiv8570679664 #yiv8570679664ygrp-sponsor
#yiv8570679664ygrp-lc {font-family:Arial;}#yiv8570679664
#yiv8570679664ygrp-sponsor #yiv8570679664ygrp-lc #yiv8570679664hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8570679664
#yiv8570679664ygrp-sponsor #yiv8570679664ygrp-lc .yiv8570679664ad
{margin-bottom:10px;padding:0 0;}#yiv8570679664 #yiv8570679664actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8570679664
#yiv8570679664activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8570679664
#yiv8570679664activity span {font-weight:700;}#yiv8570679664
#yiv8570679664activity span:first-child
{text-transform:uppercase;}#yiv8570679664 #yiv8570679664activity span a
{color:#5085b6;text-decoration:none;}#yiv8570679664 #yiv8570679664activity span
span {color:#ff7900;}#yiv8570679664 #yiv8570679664activity span
.yiv8570679664underline {text-decoration:underline;}#yiv8570679664
.yiv8570679664attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8570679664 .yiv8570679664attach div a
{text-decoration:none;}#yiv8570679664 .yiv8570679664attach img
{border:none;padding-right:5px;}#yiv8570679664 .yiv8570679664attach label
{display:block;margin-bottom:5px;}#yiv8570679664 .yiv8570679664attach label a
{text-decoration:none;}#yiv8570679664 blockquote {margin:0 0 0
4px;}#yiv8570679664 .yiv8570679664bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8570679664
.yiv8570679664bold a {text-decoration:none;}#yiv8570679664 dd.yiv8570679664last
p a {font-family:Verdana;font-weight:700;}#yiv8570679664 dd.yiv8570679664last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8570679664
dd.yiv8570679664last p span.yiv8570679664yshortcuts
{margin-right:0;}#yiv8570679664 div.yiv8570679664attach-table div div a
{text-decoration:none;}#yiv8570679664 div.yiv8570679664attach-table
{width:400px;}#yiv8570679664 div.yiv8570679664file-title a, #yiv8570679664
div.yiv8570679664file-title a:active, #yiv8570679664
div.yiv8570679664file-title a:hover, #yiv8570679664 div.yiv8570679664file-title
a:visited {text-decoration:none;}#yiv8570679664 div.yiv8570679664photo-title a,
#yiv8570679664 div.yiv8570679664photo-title a:active, #yiv8570679664
div.yiv8570679664photo-title a:hover, #yiv8570679664
div.yiv8570679664photo-title a:visited {text-decoration:none;}#yiv8570679664
div#yiv8570679664ygrp-mlmsg #yiv8570679664ygrp-msg p a
span.yiv8570679664yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8570679664
.yiv8570679664green {color:#628c2a;}#yiv8570679664 .yiv8570679664MsoNormal
{margin:0 0 0 0;}#yiv8570679664 o {font-size:0;}#yiv8570679664
#yiv8570679664photos div {float:left;width:72px;}#yiv8570679664
#yiv8570679664photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv8570679664
#yiv8570679664photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8570679664
#yiv8570679664reco-category {font-size:77%;}#yiv8570679664
#yiv8570679664reco-desc {font-size:77%;}#yiv8570679664 .yiv8570679664replbq
{margin:4px;}#yiv8570679664 #yiv8570679664ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8570679664 #yiv8570679664ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8570679664
#yiv8570679664ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8570679664
#yiv8570679664ygrp-mlmsg select, #yiv8570679664 input, #yiv8570679664 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8570679664
#yiv8570679664ygrp-mlmsg pre, #yiv8570679664 code {font:115%
monospace;}#yiv8570679664 #yiv8570679664ygrp-mlmsg *
{line-height:1.22em;}#yiv8570679664 #yiv8570679664ygrp-mlmsg #yiv8570679664logo
{padding-bottom:10px;}#yiv8570679664 #yiv8570679664ygrp-msg p a
{font-family:Verdana;}#yiv8570679664 #yiv8570679664ygrp-msg
p#yiv8570679664attach-count span {color:#1E66AE;font-weight:700;}#yiv8570679664
#yiv8570679664ygrp-reco #yiv8570679664reco-head
{color:#ff7900;font-weight:700;}#yiv8570679664 #yiv8570679664ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8570679664 #yiv8570679664ygrp-sponsor
#yiv8570679664ov li a {font-size:130%;text-decoration:none;}#yiv8570679664
#yiv8570679664ygrp-sponsor #yiv8570679664ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8570679664
#yiv8570679664ygrp-sponsor #yiv8570679664ov ul {margin:0;padding:0 0 0
8px;}#yiv8570679664 #yiv8570679664ygrp-text
{font-family:Georgia;}#yiv8570679664 #yiv8570679664ygrp-text p {margin:0 0 1em
0;}#yiv8570679664 #yiv8570679664ygrp-text tt {font-size:120%;}#yiv8570679664
#yiv8570679664ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8570679664
