Selasa, 23 Februari 2016

[belajar-excel] Digest Number 3768

12 Messages

Digest #3768
2a
Ranking by "sh.Ronald" sh.ronald
2b
Re: Ranking by "Nang Agus"
6a
Re: Filtering 2 kriteria dengan vba [1 Attachment] by "hendrik karnadi" hendrikkarnadi
8

Messages

Mon Feb 22, 2016 11:10 pm (PST) . Posted by:

"Nang Agus"

2016-02-23 13:46 GMT+07:00 'Subroto, Matzen' matzen.subroto@akzonobel.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Dear Master
>
>
>
> Semoga Allah memberkahi semuanya
>
>
>
> Saya mendapati kendala untuk memecah order secara Auto dengan menggunakan
> formula, mohon pencerahannye dari para master
>
>
>
> Terima kasih atas perhatian dan kerjasamanya, dan saya mohon maaf apabila
> ada kata-kata yang kurang berkenan
>
>
>
>
>
> *Terima Kasih*
>
>
>
> *Matzen*
>
>
>
>
>

Tue Feb 23, 2016 12:54 am (PST) . Posted by:

"odong nando"

dua bagian formula, formula pada kolom SO1, berbeda dengan formula pada SO lainny, beda dikit sih dari punya pak guru, moga bisa jadi bahan belajar... :) br,

odong nando


Pada Selasa, 23 Februari 2016 14:10, "Nang Agus nangagus@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


 

2016-02-23 13:46 GMT+07:00 'Subroto, Matzen' matzen.subroto@akzonobel.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  Dear Master Semoga Allah memberkahi semuanya Saya mendapati kendala untuk memecah order secara Auto dengan menggunakan formula, mohon pencerahannye dari para master Terima kasih atas perhatian dan kerjasamanya, dan saya mohon maaf apabila ada kata-kata yang kurang berkenan  Terima Kasih Matzen 

#yiv3326918289 #yiv3326918289 -- #yiv3326918289ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3326918289 #yiv3326918289ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3326918289 #yiv3326918289ygrp-mkp #yiv3326918289hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3326918289 #yiv3326918289ygrp-mkp #yiv3326918289ads {margin-bottom:10px;}#yiv3326918289 #yiv3326918289ygrp-mkp .yiv3326918289ad {padding:0 0;}#yiv3326918289 #yiv3326918289ygrp-mkp .yiv3326918289ad p {margin:0;}#yiv3326918289 #yiv3326918289ygrp-mkp .yiv3326918289ad a {color:#0000ff;text-decoration:none;}#yiv3326918289 #yiv3326918289ygrp-sponsor #yiv3326918289ygrp-lc {font-family:Arial;}#yiv3326918289 #yiv3326918289ygrp-sponsor #yiv3326918289ygrp-lc #yiv3326918289hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3326918289 #yiv3326918289ygrp-sponsor #yiv3326918289ygrp-lc .yiv3326918289ad {margin-bottom:10px;padding:0 0;}#yiv3326918289 #yiv3326918289actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3326918289 #yiv3326918289activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3326918289 #yiv3326918289activity span {font-weight:700;}#yiv3326918289 #yiv3326918289activity span:first-child {text-transform:uppercase;}#yiv3326918289 #yiv3326918289activity span a {color:#5085b6;text-decoration:none;}#yiv3326918289 #yiv3326918289activity span span {color:#ff7900;}#yiv3326918289 #yiv3326918289activity span .yiv3326918289underline {text-decoration:underline;}#yiv3326918289 .yiv3326918289attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3326918289 .yiv3326918289attach div a {text-decoration:none;}#yiv3326918289 .yiv3326918289attach img {border:none;padding-right:5px;}#yiv3326918289 .yiv3326918289attach label {display:block;margin-bottom:5px;}#yiv3326918289 .yiv3326918289attach label a {text-decoration:none;}#yiv3326918289 blockquote {margin:0 0 0 4px;}#yiv3326918289 .yiv3326918289bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3326918289 .yiv3326918289bold a {text-decoration:none;}#yiv3326918289 dd.yiv3326918289last p a {font-family:Verdana;font-weight:700;}#yiv3326918289 dd.yiv3326918289last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3326918289 dd.yiv3326918289last p span.yiv3326918289yshortcuts {margin-right:0;}#yiv3326918289 div.yiv3326918289attach-table div div a {text-decoration:none;}#yiv3326918289 div.yiv3326918289attach-table {width:400px;}#yiv3326918289 div.yiv3326918289file-title a, #yiv3326918289 div.yiv3326918289file-title a:active, #yiv3326918289 div.yiv3326918289file-title a:hover, #yiv3326918289 div.yiv3326918289file-title a:visited {text-decoration:none;}#yiv3326918289 div.yiv3326918289photo-title a, #yiv3326918289 div.yiv3326918289photo-title a:active, #yiv3326918289 div.yiv3326918289photo-title a:hover, #yiv3326918289 div.yiv3326918289photo-title a:visited {text-decoration:none;}#yiv3326918289 div#yiv3326918289ygrp-mlmsg #yiv3326918289ygrp-msg p a span.yiv3326918289yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3326918289 .yiv3326918289green {color:#628c2a;}#yiv3326918289 .yiv3326918289MsoNormal {margin:0 0 0 0;}#yiv3326918289 o {font-size:0;}#yiv3326918289 #yiv3326918289photos div {float:left;width:72px;}#yiv3326918289 #yiv3326918289photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3326918289 #yiv3326918289photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3326918289 #yiv3326918289reco-category {font-size:77%;}#yiv3326918289 #yiv3326918289reco-desc {font-size:77%;}#yiv3326918289 .yiv3326918289replbq {margin:4px;}#yiv3326918289 #yiv3326918289ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3326918289 #yiv3326918289ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3326918289 #yiv3326918289ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3326918289 #yiv3326918289ygrp-mlmsg select, #yiv3326918289 input, #yiv3326918289 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3326918289 #yiv3326918289ygrp-mlmsg pre, #yiv3326918289 code {font:115% monospace;}#yiv3326918289 #yiv3326918289ygrp-mlmsg * {line-height:1.22em;}#yiv3326918289 #yiv3326918289ygrp-mlmsg #yiv3326918289logo {padding-bottom:10px;}#yiv3326918289 #yiv3326918289ygrp-msg p a {font-family:Verdana;}#yiv3326918289 #yiv3326918289ygrp-msg p#yiv3326918289attach-count span {color:#1E66AE;font-weight:700;}#yiv3326918289 #yiv3326918289ygrp-reco #yiv3326918289reco-head {color:#ff7900;font-weight:700;}#yiv3326918289 #yiv3326918289ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3326918289 #yiv3326918289ygrp-sponsor #yiv3326918289ov li a {font-size:130%;text-decoration:none;}#yiv3326918289 #yiv3326918289ygrp-sponsor #yiv3326918289ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3326918289 #yiv3326918289ygrp-sponsor #yiv3326918289ov ul {margin:0;padding:0 0 0 8px;}#yiv3326918289 #yiv3326918289ygrp-text {font-family:Georgia;}#yiv3326918289 #yiv3326918289ygrp-text p {margin:0 0 1em 0;}#yiv3326918289 #yiv3326918289ygrp-text tt {font-size:120%;}#yiv3326918289 #yiv3326918289ygrp-vital ul li:last-child {border-right:none !important;}#yiv3326918289

Tue Feb 23, 2016 1:16 am (PST) . Posted by:

"Subroto, Matzen"

Dear Kang Odong n kang Agus.

Terima kasih atas pencerahannye, semoga jadi elmu yang manfaat buat saya

Wassalam

Matzen

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: 23 Februari 2016 15:52
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Memecah order dengan kriteria kemasan [1 Attachment]


dua bagian formula, formula pada kolom SO1, berbeda dengan formula pada SO lainny, beda dikit sih dari punya pak guru, moga bisa jadi bahan belajar... :)

br,

odong nando

Pada Selasa, 23 Februari 2016 14:10, "Nang Agus nangagus@gmail.com<mailto:nangagus@gmail.com> [belajar-excel]" <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>> menulis:




2016-02-23 13:46 GMT+07:00 'Subroto, Matzen' matzen.subroto@akzonobel.com<mailto:matzen.subroto@akzonobel.com> [belajar-excel] <belajar-excel@yahoogroups.com<mailto:belajar-excel@yahoogroups.com>>:

Dear Master

Semoga Allah memberkahi semuanya

Saya mendapati kendala untuk memecah order secara Auto dengan menggunakan formula, mohon pencerahannye dari para master

Terima kasih atas perhatian dan kerjasamanya, dan saya mohon maaf apabila ada kata-kata yang kurang berkenan


Terima Kasih

Matzen





__________________________________________________________
This email has been scanned by AkzoNobel for Viruses and Spam.
For more information please contact your local IT Service Desk.
__________________________________________________________

Mon Feb 22, 2016 11:16 pm (PST) . Posted by:

"sh.Ronald" sh.ronald

Selamat siang belajar excel,Mohon dibantu mengatasi hal dibawah ini :

|  Value  |  Opt.  |  BUYER  |  SELLER  | | |
|   22,072,862,500 |  BUYER  | 1 | | | |
|    (2,892,702,500) |  SELLER  | | 1 | | |
|         130,387,500 |  BUYER  | 6 | | | |
|      1,734,010,000 |  BUYER  | 4 | | | |
|      2,419,115,000 |  BUYER  | 3 | | | |
|         306,750,000 |  BUYER  | 5 | | | |
|           29,570,000 |  BUYER  | 7 | | | |
|       (662,612,500) |  SELLER  | | 3 | | |
|    (2,797,850,000) |  SELLER  | | 2 | | |
|      3,167,632,500 |  BUYER  | 2 | | | |
|              2,800,000 |  BUYER  | 8 | | | |
| | | | | | |
| | | | | | |
| Bagaimana membuat rumus rangking  otomatis pada kolom "Buyer" dan "Seller" |
| berdasarkan "Value" dari angka terbesar ke terkecil.

Terima kasih.

Ronald
|
| | | | | | |

Mon Feb 22, 2016 11:38 pm (PST) . Posted by:

"Nang Agus"

=IF(B2="buyer";COUNTIFS($B$2:$B$12;"buyer";$A$2:$A$12;">="&A2);"")
=IF(B2="seller";COUNTIFS($B$2:$B$12;"seller";$A$2:$A$12;"<="&A2);"")

2016-02-23 14:14 GMT+07:00 'sh.Ronald' sh.ronald@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Selamat siang belajar excel,
> Mohon dibantu mengatasi hal dibawah ini :
> Value Opt. BUYER SELLER
> 22,072,862,500 BUYER 1
> (2,892,702,500) SELLER 1
> 130,387,500 BUYER 6
> 1,734,010,000 BUYER 4
> 2,419,115,000 BUYER 3
> 306,750,000 BUYER 5
> 29,570,000 BUYER 7
> (662,612,500) SELLER 3
> (2,797,850,000) SELLER 2
> 3,167,632,500 BUYER 2
> 2,800,000 BUYER 8
> Bagaimana membuat rumus rangking otomatis pada kolom "Buyer" dan "Seller"
> berdasarkan "Value" dari angka terbesar ke terkecil.
>
> Terima kasih.
>
>
> Ronald
>
>
>

Mon Feb 22, 2016 11:27 pm (PST) . Posted by:

"Toby kenzo" tobykenzo

Hai Lukman,

Gunakan VBA dengan Loop method untuk membuatnya.
Coba file ini, semoga sesuai.
Tinggal tekan tombol Hasil, maka nilainya akan keluar.

TerimaKasih.

Pada Sel, 23/2/16, mr.lukman81@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

Judul: Re: [belajar-excel] Copy data berulang dengan Macro [1 Attachment]
Kepada: belajar-excel@yahoogroups.com
Tanggal: Selasa, 23 Februari, 2016, 9:21 AM


 









Dear Mr. Toby,


Terimakasih atas responnya,
jadi saya ingin mengcopy data dalam bentuk
baris ke-pinggir, menjadi baris ke-bawah. yang mana hal ini
baris ke pinggirnya tidak beraturan. jadi saya ingin urut
data semuanya ke-bawah. kira-kira bahasa indonesianya
seperti ini,


1. pilih cell
C10,  tekan enter atau panah kanan terus sampai ketemu
baris yang kosong, COPY
2. Pilih cell N3,
PASTE special tranpose
3. pilih cell C11, 
tekan enter atau panah kanan terus sampai ketemu baris yang
kosong, COPY
4. Pilih cell N3, panah BAWAH
terus sampai ketemu baris yang kosong, PASTE special
tranpose
5. Pilih C12,C13 dst sampai data
habis. Pilih cell N3, panah BAWAH terus sampai ketemu baris
yang kosong, PASTE special tranpose
6.
Begitu berulang-ulang (looping) sampai semua data ter-COPY
(C157 / atau lebih)
Contoh file saya
lampirkan.




Terimakasih,


Salam

Lukmanul H














#yiv2137145507 #yiv2137145507 --
#yiv2137145507ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv2137145507 #yiv2137145507ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv2137145507 #yiv2137145507ygrp-mkp #yiv2137145507hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv2137145507 #yiv2137145507ygrp-mkp #yiv2137145507ads {
margin-bottom:10px;}

#yiv2137145507 #yiv2137145507ygrp-mkp .yiv2137145507ad {
padding:0 0;}

#yiv2137145507 #yiv2137145507ygrp-mkp .yiv2137145507ad p {
margin:0;}

#yiv2137145507 #yiv2137145507ygrp-mkp .yiv2137145507ad a {
color:#0000ff;text-decoration:none;}
#yiv2137145507 #yiv2137145507ygrp-sponsor
#yiv2137145507ygrp-lc {
font-family:Arial;}

#yiv2137145507 #yiv2137145507ygrp-sponsor
#yiv2137145507ygrp-lc #yiv2137145507hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv2137145507 #yiv2137145507ygrp-sponsor
#yiv2137145507ygrp-lc .yiv2137145507ad {
margin-bottom:10px;padding:0 0;}

#yiv2137145507 #yiv2137145507actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv2137145507 #yiv2137145507activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv2137145507 #yiv2137145507activity span {
font-weight:700;}

#yiv2137145507 #yiv2137145507activity span:first-child {
text-transform:uppercase;}

#yiv2137145507 #yiv2137145507activity span a {
color:#5085b6;text-decoration:none;}

#yiv2137145507 #yiv2137145507activity span span {
color:#ff7900;}

#yiv2137145507 #yiv2137145507activity span
.yiv2137145507underline {
text-decoration:underline;}

#yiv2137145507 .yiv2137145507attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv2137145507 .yiv2137145507attach div a {
text-decoration:none;}

#yiv2137145507 .yiv2137145507attach img {
border:none;padding-right:5px;}

#yiv2137145507 .yiv2137145507attach label {
display:block;margin-bottom:5px;}

#yiv2137145507 .yiv2137145507attach label a {
text-decoration:none;}

#yiv2137145507 blockquote {
margin:0 0 0 4px;}

#yiv2137145507 .yiv2137145507bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv2137145507 .yiv2137145507bold a {
text-decoration:none;}

#yiv2137145507 dd.yiv2137145507last p a {
font-family:Verdana;font-weight:700;}

#yiv2137145507 dd.yiv2137145507last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv2137145507 dd.yiv2137145507last p
span.yiv2137145507yshortcuts {
margin-right:0;}

#yiv2137145507 div.yiv2137145507attach-table div div a {
text-decoration:none;}

#yiv2137145507 div.yiv2137145507attach-table {
width:400px;}

#yiv2137145507 div.yiv2137145507file-title a, #yiv2137145507
div.yiv2137145507file-title a:active, #yiv2137145507
div.yiv2137145507file-title a:hover, #yiv2137145507
div.yiv2137145507file-title a:visited {
text-decoration:none;}

#yiv2137145507 div.yiv2137145507photo-title a,
#yiv2137145507 div.yiv2137145507photo-title a:active,
#yiv2137145507 div.yiv2137145507photo-title a:hover,
#yiv2137145507 div.yiv2137145507photo-title a:visited {
text-decoration:none;}

#yiv2137145507 div#yiv2137145507ygrp-mlmsg
#yiv2137145507ygrp-msg p a span.yiv2137145507yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv2137145507 .yiv2137145507green {
color:#628c2a;}

#yiv2137145507 .yiv2137145507MsoNormal {
margin:0 0 0 0;}

#yiv2137145507 o {
font-size:0;}

#yiv2137145507 #yiv2137145507photos div {
float:left;width:72px;}

#yiv2137145507 #yiv2137145507photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv2137145507 #yiv2137145507photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv2137145507 #yiv2137145507reco-category {
font-size:77%;}

#yiv2137145507 #yiv2137145507reco-desc {
font-size:77%;}

#yiv2137145507 .yiv2137145507replbq {
margin:4px;}

#yiv2137145507 #yiv2137145507ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv2137145507 #yiv2137145507ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv2137145507 #yiv2137145507ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv2137145507 #yiv2137145507ygrp-mlmsg select,
#yiv2137145507 input, #yiv2137145507 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv2137145507 #yiv2137145507ygrp-mlmsg pre, #yiv2137145507
code {
font:115% monospace;}

#yiv2137145507 #yiv2137145507ygrp-mlmsg * {
line-height:1.22em;}

#yiv2137145507 #yiv2137145507ygrp-mlmsg #yiv2137145507logo {
padding-bottom:10px;}


#yiv2137145507 #yiv2137145507ygrp-msg p a {
font-family:Verdana;}

#yiv2137145507 #yiv2137145507ygrp-msg
p#yiv2137145507attach-count span {
color:#1E66AE;font-weight:700;}

#yiv2137145507 #yiv2137145507ygrp-reco
#yiv2137145507reco-head {
color:#ff7900;font-weight:700;}

#yiv2137145507 #yiv2137145507ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv2137145507 #yiv2137145507ygrp-sponsor #yiv2137145507ov
li a {
font-size:130%;text-decoration:none;}

#yiv2137145507 #yiv2137145507ygrp-sponsor #yiv2137145507ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv2137145507 #yiv2137145507ygrp-sponsor #yiv2137145507ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv2137145507 #yiv2137145507ygrp-text {
font-family:Georgia;}

#yiv2137145507 #yiv2137145507ygrp-text p {
margin:0 0 1em 0;}

#yiv2137145507 #yiv2137145507ygrp-text tt {
font-size:120%;}

#yiv2137145507 #yiv2137145507ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv2137145507

Tue Feb 23, 2016 1:17 am (PST) . Posted by:

mr.lukman81

Dear Mr. Toby,



Terimakasish atas bantuannya, case close.
















Salam,


Lukman

Mon Feb 22, 2016 11:42 pm (PST) . Posted by:

pujo_winoko

Assalamualaikum,Teman-teman para master excel, mohon bantuannya. saya ada sedikit kendala dalam menghitung jumlah kehadiran karyawan yang hadir pada hari sabtu dengan type jabatan Kanvas PP (sheet warna merah) yang hasil nya masuk ke E47.

Dan bagaimana cara copy paste otomatis agar data yang berada di sheet data asli pro-int, masuk ke masing-masing sheet nama karyawan. Karena selama ini masih copy paste secara manual.Jika teman-teman memiliki formula yang lebih praktis untuk kasus ini, mohon dapat dikoreksi.

Sebelumnya saya ucapkan terima kasih

Best Regards

Pujo

Mon Feb 22, 2016 11:53 pm (PST) . Posted by:

"odong nando"

klo pake formula, nanti hasilnya bisa di filter lg, bunyi formulanya
=IF(COUNTIF(C11;"??.*");C12;"")

dengan asumsi awal pencarian itu di cell D11.
note : bila regional setting komputer setempat english maka ubah titik koma ";" menjadi koma "," br,

odong nando


Pada Selasa, 23 Februari 2016 13:46, "Stefanus Adrianus stefan_andry@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Dear teman-teman, saya mau nanya bagiamana memindahkan uraian dari kode rekening ke kolom di samping baris kode rekening. saya lampirkan juga filenya. saya mohon bantuan. terima kasih. #yiv2896072087 #yiv2896072087 -- #yiv2896072087ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2896072087 #yiv2896072087ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2896072087 #yiv2896072087ygrp-mkp #yiv2896072087hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2896072087 #yiv2896072087ygrp-mkp #yiv2896072087ads {margin-bottom:10px;}#yiv2896072087 #yiv2896072087ygrp-mkp .yiv2896072087ad {padding:0 0;}#yiv2896072087 #yiv2896072087ygrp-mkp .yiv2896072087ad p {margin:0;}#yiv2896072087 #yiv2896072087ygrp-mkp .yiv2896072087ad a {color:#0000ff;text-decoration:none;}#yiv2896072087 #yiv2896072087ygrp-sponsor #yiv2896072087ygrp-lc {font-family:Arial;}#yiv2896072087 #yiv2896072087ygrp-sponsor #yiv2896072087ygrp-lc #yiv2896072087hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2896072087 #yiv2896072087ygrp-sponsor #yiv2896072087ygrp-lc .yiv2896072087ad {margin-bottom:10px;padding:0 0;}#yiv2896072087 #yiv2896072087actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2896072087 #yiv2896072087activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2896072087 #yiv2896072087activity span {font-weight:700;}#yiv2896072087 #yiv2896072087activity span:first-child {text-transform:uppercase;}#yiv2896072087 #yiv2896072087activity span a {color:#5085b6;text-decoration:none;}#yiv2896072087 #yiv2896072087activity span span {color:#ff7900;}#yiv2896072087 #yiv2896072087activity span .yiv2896072087underline {text-decoration:underline;}#yiv2896072087 .yiv2896072087attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2896072087 .yiv2896072087attach div a {text-decoration:none;}#yiv2896072087 .yiv2896072087attach img {border:none;padding-right:5px;}#yiv2896072087 .yiv2896072087attach label {display:block;margin-bottom:5px;}#yiv2896072087 .yiv2896072087attach label a {text-decoration:none;}#yiv2896072087 blockquote {margin:0 0 0 4px;}#yiv2896072087 .yiv2896072087bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2896072087 .yiv2896072087bold a {text-decoration:none;}#yiv2896072087 dd.yiv2896072087last p a {font-family:Verdana;font-weight:700;}#yiv2896072087 dd.yiv2896072087last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2896072087 dd.yiv2896072087last p span.yiv2896072087yshortcuts {margin-right:0;}#yiv2896072087 div.yiv2896072087attach-table div div a {text-decoration:none;}#yiv2896072087 div.yiv2896072087attach-table {width:400px;}#yiv2896072087 div.yiv2896072087file-title a, #yiv2896072087 div.yiv2896072087file-title a:active, #yiv2896072087 div.yiv2896072087file-title a:hover, #yiv2896072087 div.yiv2896072087file-title a:visited {text-decoration:none;}#yiv2896072087 div.yiv2896072087photo-title a, #yiv2896072087 div.yiv2896072087photo-title a:active, #yiv2896072087 div.yiv2896072087photo-title a:hover, #yiv2896072087 div.yiv2896072087photo-title a:visited {text-decoration:none;}#yiv2896072087 div#yiv2896072087ygrp-mlmsg #yiv2896072087ygrp-msg p a span.yiv2896072087yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2896072087 .yiv2896072087green {color:#628c2a;}#yiv2896072087 .yiv2896072087MsoNormal {margin:0 0 0 0;}#yiv2896072087 o {font-size:0;}#yiv2896072087 #yiv2896072087photos div {float:left;width:72px;}#yiv2896072087 #yiv2896072087photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv2896072087 #yiv2896072087photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2896072087 #yiv2896072087reco-category {font-size:77%;}#yiv2896072087 #yiv2896072087reco-desc {font-size:77%;}#yiv2896072087 .yiv2896072087replbq {margin:4px;}#yiv2896072087 #yiv2896072087ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2896072087 #yiv2896072087ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2896072087 #yiv2896072087ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2896072087 #yiv2896072087ygrp-mlmsg select, #yiv2896072087 input, #yiv2896072087 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2896072087 #yiv2896072087ygrp-mlmsg pre, #yiv2896072087 code {font:115% monospace;}#yiv2896072087 #yiv2896072087ygrp-mlmsg * {line-height:1.22em;}#yiv2896072087 #yiv2896072087ygrp-mlmsg #yiv2896072087logo {padding-bottom:10px;}#yiv2896072087 #yiv2896072087ygrp-msg p a {font-family:Verdana;}#yiv2896072087 #yiv2896072087ygrp-msg p#yiv2896072087attach-count span {color:#1E66AE;font-weight:700;}#yiv2896072087 #yiv2896072087ygrp-reco #yiv2896072087reco-head {color:#ff7900;font-weight:700;}#yiv2896072087 #yiv2896072087ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2896072087 #yiv2896072087ygrp-sponsor #yiv2896072087ov li a {font-size:130%;text-decoration:none;}#yiv2896072087 #yiv2896072087ygrp-sponsor #yiv2896072087ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2896072087 #yiv2896072087ygrp-sponsor #yiv2896072087ov ul {margin:0;padding:0 0 0 8px;}#yiv2896072087 #yiv2896072087ygrp-text {font-family:Georgia;}#yiv2896072087 #yiv2896072087ygrp-text p {margin:0 0 1em 0;}#yiv2896072087 #yiv2896072087ygrp-text tt {font-size:120%;}#yiv2896072087 #yiv2896072087ygrp-vital ul li:last-child {border-right:none !important;}#yiv2896072087

Tue Feb 23, 2016 12:10 am (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Coba copas VBA ini pada VBE Sheet1.....
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)   Dim dTabel As Range, Tampil As Range   Dim r As Long, n As Long   ' siti Vi / 30 May 2011 / beExcel case   ' FILTER DGN MENGOPY YG COCOK DI RANGE LAIN   '-------------------------------------   Application.Calculation = xlCalculationManual   Application.EnableEvents = False   On Error GoTo AKHIR   If Target.Count = 1 Then      If Target.Address = "$J$5" Or Target.Address = "$K$5" Then         Set dTabel = Cells(1, 1).CurrentRegion         Set Tampil = Cells(7, 9).CurrentRegion         Tampil.ClearContents         For r = 2 To dTabel.Rows.Count            If dTabel(r, 3) = [J5] And dTabel(r, 6) = [K5] Then               n = n + 1               Tampil(n, 1) = dTabel(r, 1)               Tampil(n, 2) = dTabel(r, 2)               Tampil(n, 3) = dTabel(r, 3)               Tampil(n, 4) = dTabel(r, 4)               Tampil(n, 5) = dTabel(r, 5)               Tampil(n, 6) = dTabel(r, 6)            End If         Next      End If   End IfAKHIR:   Application.Calculation = xlCalculationAutomatic   Application.EnableEvents = TrueEnd Sub
Perubahan terjadi pada warna biru.
Salam,HK
From: "dinsosnakertrans_09@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
Sent: Tuesday, 23 February 2016, 10:47
Subject: [belajar-excel] Filtering 2 kriteria dengan vba [1 Attachment]

  Yth. Para Master Excel....Saya mengucapkan banyak terima kasih apabila para master excel berkenan merevisi script vba filering 1 kriteria warisan siti Vi (alm) tgl. 30 May 2011 menjadi 2 kriteria.Masalahnya saya sudah mencoba merevisi sampai berbulan-bulan namun belum berhasil.
#yiv2951625245 #yiv2951625245 -- #yiv2951625245ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2951625245 #yiv2951625245ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2951625245 #yiv2951625245ygrp-mkp #yiv2951625245hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2951625245 #yiv2951625245ygrp-mkp #yiv2951625245ads {margin-bottom:10px;}#yiv2951625245 #yiv2951625245ygrp-mkp .yiv2951625245ad {padding:0 0;}#yiv2951625245 #yiv2951625245ygrp-mkp .yiv2951625245ad p {margin:0;}#yiv2951625245 #yiv2951625245ygrp-mkp .yiv2951625245ad a {color:#0000ff;text-decoration:none;}#yiv2951625245 #yiv2951625245ygrp-sponsor #yiv2951625245ygrp-lc {font-family:Arial;}#yiv2951625245 #yiv2951625245ygrp-sponsor #yiv2951625245ygrp-lc #yiv2951625245hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2951625245 #yiv2951625245ygrp-sponsor #yiv2951625245ygrp-lc .yiv2951625245ad {margin-bottom:10px;padding:0 0;}#yiv2951625245 #yiv2951625245actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2951625245 #yiv2951625245activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2951625245 #yiv2951625245activity span {font-weight:700;}#yiv2951625245 #yiv2951625245activity span:first-child {text-transform:uppercase;}#yiv2951625245 #yiv2951625245activity span a {color:#5085b6;text-decoration:none;}#yiv2951625245 #yiv2951625245activity span span {color:#ff7900;}#yiv2951625245 #yiv2951625245activity span .yiv2951625245underline {text-decoration:underline;}#yiv2951625245 .yiv2951625245attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2951625245 .yiv2951625245attach div a {text-decoration:none;}#yiv2951625245 .yiv2951625245attach img {border:none;padding-right:5px;}#yiv2951625245 .yiv2951625245attach label {display:block;margin-bottom:5px;}#yiv2951625245 .yiv2951625245attach label a {text-decoration:none;}#yiv2951625245 blockquote {margin:0 0 0 4px;}#yiv2951625245 .yiv2951625245bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2951625245 .yiv2951625245bold a {text-decoration:none;}#yiv2951625245 dd.yiv2951625245last p a {font-family:Verdana;font-weight:700;}#yiv2951625245 dd.yiv2951625245last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2951625245 dd.yiv2951625245last p span.yiv2951625245yshortcuts {margin-right:0;}#yiv2951625245 div.yiv2951625245attach-table div div a {text-decoration:none;}#yiv2951625245 div.yiv2951625245attach-table {width:400px;}#yiv2951625245 div.yiv2951625245file-title a, #yiv2951625245 div.yiv2951625245file-title a:active, #yiv2951625245 div.yiv2951625245file-title a:hover, #yiv2951625245 div.yiv2951625245file-title a:visited {text-decoration:none;}#yiv2951625245 div.yiv2951625245photo-title a, #yiv2951625245 div.yiv2951625245photo-title a:active, #yiv2951625245 div.yiv2951625245photo-title a:hover, #yiv2951625245 div.yiv2951625245photo-title a:visited {text-decoration:none;}#yiv2951625245 div#yiv2951625245ygrp-mlmsg #yiv2951625245ygrp-msg p a span.yiv2951625245yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2951625245 .yiv2951625245green {color:#628c2a;}#yiv2951625245 .yiv2951625245MsoNormal {margin:0 0 0 0;}#yiv2951625245 o {font-size:0;}#yiv2951625245 #yiv2951625245photos div {float:left;width:72px;}#yiv2951625245 #yiv2951625245photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv2951625245 #yiv2951625245photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2951625245 #yiv2951625245reco-category {font-size:77%;}#yiv2951625245 #yiv2951625245reco-desc {font-size:77%;}#yiv2951625245 .yiv2951625245replbq {margin:4px;}#yiv2951625245 #yiv2951625245ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2951625245 #yiv2951625245ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2951625245 #yiv2951625245ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2951625245 #yiv2951625245ygrp-mlmsg select, #yiv2951625245 input, #yiv2951625245 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2951625245 #yiv2951625245ygrp-mlmsg pre, #yiv2951625245 code {font:115% monospace;}#yiv2951625245 #yiv2951625245ygrp-mlmsg * {line-height:1.22em;}#yiv2951625245 #yiv2951625245ygrp-mlmsg #yiv2951625245logo {padding-bottom:10px;}#yiv2951625245 #yiv2951625245ygrp-msg p a {font-family:Verdana;}#yiv2951625245 #yiv2951625245ygrp-msg p#yiv2951625245attach-count span {color:#1E66AE;font-weight:700;}#yiv2951625245 #yiv2951625245ygrp-reco #yiv2951625245reco-head {color:#ff7900;font-weight:700;}#yiv2951625245 #yiv2951625245ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2951625245 #yiv2951625245ygrp-sponsor #yiv2951625245ov li a {font-size:130%;text-decoration:none;}#yiv2951625245 #yiv2951625245ygrp-sponsor #yiv2951625245ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2951625245 #yiv2951625245ygrp-sponsor #yiv2951625245ov ul {margin:0;padding:0 0 0 8px;}#yiv2951625245 #yiv2951625245ygrp-text {font-family:Georgia;}#yiv2951625245 #yiv2951625245ygrp-text p {margin:0 0 1em 0;}#yiv2951625245 #yiv2951625245ygrp-text tt {font-size:120%;}#yiv2951625245 #yiv2951625245ygrp-vital ul li:last-child {border-right:none !important;}#yiv2951625245

Tue Feb 23, 2016 12:40 am (PST) . Posted by:

"Toby kenzo" tobykenzo

Dear,

Coba file ini.

TerimaKasih.

Pada Sel, 23/2/16, dinsosnakertrans_09@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

Judul: [belajar-excel] Filtering 2 kriteria dengan vba [1 Attachment]
Kepada: belajar-excel@yahoogroups.com
Tanggal: Selasa, 23 Februari, 2016, 10:47 AM


 









Yth. Para Master Excel....Saya
mengucapkan banyak terima kasih apabila para master excel
berkenan merevisi script vba filering 1 kriteria warisan
siti Vi (alm) tgl. 30 May 2011 menjadi 2
kriteria.Masalahnya saya sudah
mencoba merevisi sampai berbulan-bulan namun belum
berhasil.










#yiv4144811244 #yiv4144811244 --
#yiv4144811244ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv4144811244 #yiv4144811244ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv4144811244 #yiv4144811244ygrp-mkp #yiv4144811244hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv4144811244 #yiv4144811244ygrp-mkp #yiv4144811244ads {
margin-bottom:10px;}

#yiv4144811244 #yiv4144811244ygrp-mkp .yiv4144811244ad {
padding:0 0;}

#yiv4144811244 #yiv4144811244ygrp-mkp .yiv4144811244ad p {
margin:0;}

#yiv4144811244 #yiv4144811244ygrp-mkp .yiv4144811244ad a {
color:#0000ff;text-decoration:none;}
#yiv4144811244 #yiv4144811244ygrp-sponsor
#yiv4144811244ygrp-lc {
font-family:Arial;}

#yiv4144811244 #yiv4144811244ygrp-sponsor
#yiv4144811244ygrp-lc #yiv4144811244hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv4144811244 #yiv4144811244ygrp-sponsor
#yiv4144811244ygrp-lc .yiv4144811244ad {
margin-bottom:10px;padding:0 0;}

#yiv4144811244 #yiv4144811244actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv4144811244 #yiv4144811244activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv4144811244 #yiv4144811244activity span {
font-weight:700;}

#yiv4144811244 #yiv4144811244activity span:first-child {
text-transform:uppercase;}

#yiv4144811244 #yiv4144811244activity span a {
color:#5085b6;text-decoration:none;}

#yiv4144811244 #yiv4144811244activity span span {
color:#ff7900;}

#yiv4144811244 #yiv4144811244activity span
.yiv4144811244underline {
text-decoration:underline;}

#yiv4144811244 .yiv4144811244attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv4144811244 .yiv4144811244attach div a {
text-decoration:none;}

#yiv4144811244 .yiv4144811244attach img {
border:none;padding-right:5px;}

#yiv4144811244 .yiv4144811244attach label {
display:block;margin-bottom:5px;}

#yiv4144811244 .yiv4144811244attach label a {
text-decoration:none;}

#yiv4144811244 blockquote {
margin:0 0 0 4px;}

#yiv4144811244 .yiv4144811244bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv4144811244 .yiv4144811244bold a {
text-decoration:none;}

#yiv4144811244 dd.yiv4144811244last p a {
font-family:Verdana;font-weight:700;}

#yiv4144811244 dd.yiv4144811244last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv4144811244 dd.yiv4144811244last p
span.yiv4144811244yshortcuts {
margin-right:0;}

#yiv4144811244 div.yiv4144811244attach-table div div a {
text-decoration:none;}

#yiv4144811244 div.yiv4144811244attach-table {
width:400px;}

#yiv4144811244 div.yiv4144811244file-title a, #yiv4144811244
div.yiv4144811244file-title a:active, #yiv4144811244
div.yiv4144811244file-title a:hover, #yiv4144811244
div.yiv4144811244file-title a:visited {
text-decoration:none;}

#yiv4144811244 div.yiv4144811244photo-title a,
#yiv4144811244 div.yiv4144811244photo-title a:active,
#yiv4144811244 div.yiv4144811244photo-title a:hover,
#yiv4144811244 div.yiv4144811244photo-title a:visited {
text-decoration:none;}

#yiv4144811244 div#yiv4144811244ygrp-mlmsg
#yiv4144811244ygrp-msg p a span.yiv4144811244yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv4144811244 .yiv4144811244green {
color:#628c2a;}

#yiv4144811244 .yiv4144811244MsoNormal {
margin:0 0 0 0;}

#yiv4144811244 o {
font-size:0;}

#yiv4144811244 #yiv4144811244photos div {
float:left;width:72px;}

#yiv4144811244 #yiv4144811244photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv4144811244 #yiv4144811244photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv4144811244 #yiv4144811244reco-category {
font-size:77%;}

#yiv4144811244 #yiv4144811244reco-desc {
font-size:77%;}

#yiv4144811244 .yiv4144811244replbq {
margin:4px;}

#yiv4144811244 #yiv4144811244ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv4144811244 #yiv4144811244ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv4144811244 #yiv4144811244ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv4144811244 #yiv4144811244ygrp-mlmsg select,
#yiv4144811244 input, #yiv4144811244 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv4144811244 #yiv4144811244ygrp-mlmsg pre, #yiv4144811244
code {
font:115% monospace;}

#yiv4144811244 #yiv4144811244ygrp-mlmsg * {
line-height:1.22em;}

#yiv4144811244 #yiv4144811244ygrp-mlmsg #yiv4144811244logo {
padding-bottom:10px;}


#yiv4144811244 #yiv4144811244ygrp-msg p a {
font-family:Verdana;}

#yiv4144811244 #yiv4144811244ygrp-msg
p#yiv4144811244attach-count span {
color:#1E66AE;font-weight:700;}

#yiv4144811244 #yiv4144811244ygrp-reco
#yiv4144811244reco-head {
color:#ff7900;font-weight:700;}

#yiv4144811244 #yiv4144811244ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv4144811244 #yiv4144811244ygrp-sponsor #yiv4144811244ov
li a {
font-size:130%;text-decoration:none;}

#yiv4144811244 #yiv4144811244ygrp-sponsor #yiv4144811244ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv4144811244 #yiv4144811244ygrp-sponsor #yiv4144811244ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv4144811244 #yiv4144811244ygrp-text {
font-family:Georgia;}

#yiv4144811244 #yiv4144811244ygrp-text p {
margin:0 0 1em 0;}

#yiv4144811244 #yiv4144811244ygrp-text tt {
font-size:120%;}

#yiv4144811244 #yiv4144811244ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv4144811244

Tue Feb 23, 2016 1:17 am (PST) . Posted by:

"Ruswanto Anto" rwanto78

Assalammualaikum, wr.wb
Selamat sore, teman-teman master excel, mohon bantuannya bagaimana mencari rumus untuk pembagian bunga SHU per anggota?berikut saya lampirkan datanya.Terimakasih atas bantuan dan perhatiannya.

Salam hormatRuswanto
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Poskan Komentar