Selasa, 29 Oktober 2019

[belajar-excel] Digest Number 4891

3 Messages

Digest #4891

Messages

Tue Oct 29, 2019 11:51 am (PDT) . Posted by:

"Kamirin Faqoth" kamirin_2

Maaf Pa Ghazi, rumusnya tidak berjalan sesuai dengan yang hasil yang diingkan, mungkin bisa diperiksa kembali.

Terima Kasih,Kamirin

Pada Minggu, 27 Oktober 2019 12.54.51 WIB, ghozi alkatiri ghozialkatiri@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

tulis di G4 (array formula)
=IFERROR(IF(INDEX(A$4:A$24,SMALL(IF(LEN($D$4:$D$24),ROW($D$4:$D$24)),ROW(1:1))-ROW($D$3))=0,G3,INDEX(A$4:A$24,SMALL(IF(LEN($D$4:$D$24),ROW($D$4:$D$24)),ROW(1:1))-ROW($D$3))),"")

copy ke kanan dan ke awah
wassalam
Ghozi Alkatiri
Pada Sabtu, 26 Oktober 2019 19.42.19 WIB, Kamirin Faqoth kamirin_2@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 



Dear All Member Be-Exceller
Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.Berikut saya kirimkan Contoh Filenya

Terima KasihKamirin

Tue Oct 29, 2019 12:04 pm (PDT) . Posted by:

"Kamirin Faqoth" kamirin_2

Dear Pa Zainul,

Terima Kasih atas bantuannya, walaupun belum 100% mendekati hasil yang diinginkan, tapi ini sudah sangat membantu sekali, tapi mohon dijelaskan Pak, maksud dari formula dibawah ini, biar saya & anggota grup bisa memahami maksudnya.

=REPT(LOOKUP("ZZZzz";$A$4:$A4);D4>0)

Terima Kasih,Kamirin
Pada Minggu, 27 Oktober 2019 13.37.44 WIB, Zainul Ulum zainul_ulum@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

File terlampir menggunakan 2 cara:

- Dengan formula excel
- Deangan power query.

Semoga sesuai dengan yang diinginkan.

Wassalam

-zainul

 

Sent from Mail for Windows 10

 

From: Kamirin Faqoth kamirin_2@yahoo.co.id [belajar-excel]
Sent: Saturday, October 26, 2019 7:42 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu [1 Attachment]

 

 

Dear All Member Be-Exceller

 

Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.

Berikut saya kirimkan Contoh Filenya

 

 

 

Terima Kasih

Kamirin

 
#yiv0924233682 #yiv0924233682 -- #yiv0924233682ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0924233682 #yiv0924233682ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0924233682 #yiv0924233682ygrp-mkp #yiv0924233682hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0924233682 #yiv0924233682ygrp-mkp #yiv0924233682ads {margin-bottom:10px;}#yiv0924233682 #yiv0924233682ygrp-mkp .yiv0924233682ad {padding:0 0;}#yiv0924233682 #yiv0924233682ygrp-mkp .yiv0924233682ad p {margin:0;}#yiv0924233682 #yiv0924233682ygrp-mkp .yiv0924233682ad a {color:#0000ff;text-decoration:none;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ygrp-lc {font-family:Arial;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ygrp-lc #yiv0924233682hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ygrp-lc .yiv0924233682ad {margin-bottom:10px;padding:0 0;}#yiv0924233682 #yiv0924233682actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0924233682 #yiv0924233682activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0924233682 #yiv0924233682activity span {font-weight:700;}#yiv0924233682 #yiv0924233682activity span:first-child {text-transform:uppercase;}#yiv0924233682 #yiv0924233682activity span a {color:#5085b6;text-decoration:none;}#yiv0924233682 #yiv0924233682activity span span {color:#ff7900;}#yiv0924233682 #yiv0924233682activity span .yiv0924233682underline {text-decoration:underline;}#yiv0924233682 .yiv0924233682attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0924233682 .yiv0924233682attach div a {text-decoration:none;}#yiv0924233682 .yiv0924233682attach img {border:none;padding-right:5px;}#yiv0924233682 .yiv0924233682attach label {display:block;margin-bottom:5px;}#yiv0924233682 .yiv0924233682attach label a {text-decoration:none;}#yiv0924233682 blockquote {margin:0 0 0 4px;}#yiv0924233682 .yiv0924233682bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0924233682 .yiv0924233682bold a {text-decoration:none;}#yiv0924233682 dd.yiv0924233682last p a {font-family:Verdana;font-weight:700;}#yiv0924233682 dd.yiv0924233682last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0924233682 dd.yiv0924233682last p span.yiv0924233682yshortcuts {margin-right:0;}#yiv0924233682 div.yiv0924233682attach-table div div a {text-decoration:none;}#yiv0924233682 div.yiv0924233682attach-table {width:400px;}#yiv0924233682 div.yiv0924233682file-title a, #yiv0924233682 div.yiv0924233682file-title a:active, #yiv0924233682 div.yiv0924233682file-title a:hover, #yiv0924233682 div.yiv0924233682file-title a:visited {text-decoration:none;}#yiv0924233682 div.yiv0924233682photo-title a, #yiv0924233682 div.yiv0924233682photo-title a:active, #yiv0924233682 div.yiv0924233682photo-title a:hover, #yiv0924233682 div.yiv0924233682photo-title a:visited {text-decoration:none;}#yiv0924233682 div#yiv0924233682ygrp-mlmsg #yiv0924233682ygrp-msg p a span.yiv0924233682yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0924233682 .yiv0924233682green {color:#628c2a;}#yiv0924233682 .yiv0924233682MsoNormal {margin:0 0 0 0;}#yiv0924233682 o {font-size:0;}#yiv0924233682 #yiv0924233682photos div {float:left;width:72px;}#yiv0924233682 #yiv0924233682photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv0924233682 #yiv0924233682photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0924233682 #yiv0924233682reco-category {font-size:77%;}#yiv0924233682 #yiv0924233682reco-desc {font-size:77%;}#yiv0924233682 .yiv0924233682replbq {margin:4px;}#yiv0924233682 #yiv0924233682ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0924233682 #yiv0924233682ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0924233682 #yiv0924233682ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0924233682 #yiv0924233682ygrp-mlmsg select, #yiv0924233682 input, #yiv0924233682 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0924233682 #yiv0924233682ygrp-mlmsg pre, #yiv0924233682 code {font:115% monospace;}#yiv0924233682 #yiv0924233682ygrp-mlmsg * {line-height:1..22em;}#yiv0924233682 #yiv0924233682ygrp-mlmsg #yiv0924233682logo {padding-bottom:10px;}#yiv0924233682 #yiv0924233682ygrp-msg p a {font-family:Verdana;}#yiv0924233682 #yiv0924233682ygrp-msg p#yiv0924233682attach-count span {color:#1E66AE;font-weight:700;}#yiv0924233682 #yiv0924233682ygrp-reco #yiv0924233682reco-head {color:#ff7900;font-weight:700;}#yiv0924233682 #yiv0924233682ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ov li a {font-size:130%;text-decoration:none;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ov ul {margin:0;padding:0 0 0 8px;}#yiv0924233682 #yiv0924233682ygrp-text {font-family:Georgia;}#yiv0924233682 #yiv0924233682ygrp-text p {margin:0 0 1em 0;}#yiv0924233682 #yiv0924233682ygrp-text tt {font-size:120%;}#yiv0924233682 #yiv0924233682ygrp-vital ul li:last-child {border-right:none !important;}#yiv0924233682

Tue Oct 29, 2019 1:06 pm (PDT) . Posted by:

"Kamirin Faqoth" kamirin_2


Terima Kasih Pa Hendrik, di file aslinya tidak ada warna, warna disitu hanya penjelasan saya saja untuk memudahkan penjelasan permasalahan saya, dan datanya hanya nambah ke bawah aja, tidak ke kanan.
Terima Kasih
Pada Selasa, 22 Oktober 2019 20.40.47 WIB, hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

Jika datanya banyak (tidak tahu posisi kanan bawahnya) makaRange("A3:E23")
diganti denganRange("A3").CurrentRegion

Salam,HK

----- Pesan yang Diteruskan ----- Dari: hendrik karnadi <hendrikkarnadi@yahoo.com>Kepada: Kamirin Faqoth kamirin_2@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups..com>Terkirim: Selasa, 22 Oktober 2019 17.21.55 WIBJudul: Re: [belajar-excel] Mencari Nilai dengan Kriteria Khusus [1 Attachment]
Dear Kamirin,
Coba copas Macro ini di VBE Sheet (Module1)Sub Rapihkan()    'Mengisi blank cell dengan sel diatasnya    Set Rng = Range("A3:E23")    Rng.Select    Selection.SpecialCells(xlCellTypeBlanks).Select    Selection.FormulaR1C1 = "=R[-1]C"        'Menghapus sel yang interior colornya berearna merah    For Each rg In Rng        If rg.Interior.Color = vbRed Then            rg.EntireRow.Delete        End If    Next rgEnd SubLalu jalankan.
Salam,HK

Pada Selasa, 22 Oktober 2019 16.27.07 GMT+7, Kamirin Faqoth kamirin_2@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

Dear All Member,
Mohon bantuannya, saya agak kesulitan mencari nilai dengan kriteria khusus pada tabel data yang sifatnya statis, dengan acuan tabel yang saya tandain warna kuning dan
dengan mengabaikan tabel yang tandain warna merah, Berikut Contoh Potongan File terlampir, terdiri dari 20 baris, file aslinya lebih dari 1000 baris.
Terim Kasih,Kamirin
#yiv9159249886 #yiv9159249886 -- #yiv9159249886ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9159249886 #yiv9159249886ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9159249886 #yiv9159249886ygrp-mkp #yiv9159249886hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9159249886 #yiv9159249886ygrp-mkp #yiv9159249886ads {margin-bottom:10px;}#yiv9159249886 #yiv9159249886ygrp-mkp .yiv9159249886ad {padding:0 0;}#yiv9159249886 #yiv9159249886ygrp-mkp .yiv9159249886ad p {margin:0;}#yiv9159249886 #yiv9159249886ygrp-mkp .yiv9159249886ad a {color:#0000ff;text-decoration:none;}#yiv9159249886 #yiv9159249886ygrp-sponsor #yiv9159249886ygrp-lc {font-family:Arial;}#yiv9159249886 #yiv9159249886ygrp-sponsor #yiv9159249886ygrp-lc #yiv9159249886hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9159249886 #yiv9159249886ygrp-sponsor #yiv9159249886ygrp-lc .yiv9159249886ad {margin-bottom:10px;padding:0 0;}#yiv9159249886 #yiv9159249886actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9159249886 #yiv9159249886activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9159249886 #yiv9159249886activity span {font-weight:700;}#yiv9159249886 #yiv9159249886activity span:first-child {text-transform:uppercase;}#yiv9159249886 #yiv9159249886activity span a {color:#5085b6;text-decoration:none;}#yiv9159249886 #yiv9159249886activity span span {color:#ff7900;}#yiv9159249886 #yiv9159249886activity span .yiv9159249886underline {text-decoration:underline;}#yiv9159249886 .yiv9159249886attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9159249886 .yiv9159249886attach div a {text-decoration:none;}#yiv9159249886 .yiv9159249886attach img {border:none;padding-right:5px;}#yiv9159249886 .yiv9159249886attach label {display:block;margin-bottom:5px;}#yiv9159249886 .yiv9159249886attach label a {text-decoration:none;}#yiv9159249886 blockquote {margin:0 0 0 4px;}#yiv9159249886 .yiv9159249886bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9159249886 .yiv9159249886bold a {text-decoration:none;}#yiv9159249886 dd.yiv9159249886last p a {font-family:Verdana;font-weight:700;}#yiv9159249886 dd.yiv9159249886last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9159249886 dd.yiv9159249886last p span.yiv9159249886yshortcuts {margin-right:0;}#yiv9159249886 div.yiv9159249886attach-table div div a {text-decoration:none;}#yiv9159249886 div.yiv9159249886attach-table {width:400px;}#yiv9159249886 div.yiv9159249886file-title a, #yiv9159249886 div.yiv9159249886file-title a:active, #yiv9159249886 div.yiv9159249886file-title a:hover, #yiv9159249886 div.yiv9159249886file-title a:visited {text-decoration:none;}#yiv9159249886 div.yiv9159249886photo-title a, #yiv9159249886 div.yiv9159249886photo-title a:active, #yiv9159249886 div.yiv9159249886photo-title a:hover, #yiv9159249886 div.yiv9159249886photo-title a:visited {text-decoration:none;}#yiv9159249886 div#yiv9159249886ygrp-mlmsg #yiv9159249886ygrp-msg p a span.yiv9159249886yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9159249886 .yiv9159249886green {color:#628c2a;}#yiv9159249886 .yiv9159249886MsoNormal {margin:0 0 0 0;}#yiv9159249886 o {font-size:0;}#yiv9159249886 #yiv9159249886photos div {float:left;width:72px;}#yiv9159249886 #yiv9159249886photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv9159249886 #yiv9159249886photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9159249886 #yiv9159249886reco-category {font-size:77%;}#yiv9159249886 #yiv9159249886reco-desc {font-size:77%;}#yiv9159249886 .yiv9159249886replbq {margin:4px;}#yiv9159249886 #yiv9159249886ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9159249886 #yiv9159249886ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9159249886 #yiv9159249886ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9159249886 #yiv9159249886ygrp-mlmsg select, #yiv9159249886 input, #yiv9159249886 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9159249886 #yiv9159249886ygrp-mlmsg pre, #yiv9159249886 code {font:115% monospace;}#yiv9159249886 #yiv9159249886ygrp-mlmsg * {line-height:1.22em;}#yiv9159249886 #yiv9159249886ygrp-mlmsg #yiv9159249886logo {padding-bottom:10px;}#yiv9159249886 #yiv9159249886ygrp-msg p a {font-family:Verdana;}#yiv9159249886 #yiv9159249886ygrp-msg p#yiv9159249886attach-count span {color:#1E66AE;font-weight:700;}#yiv9159249886 #yiv9159249886ygrp-reco #yiv9159249886reco-head {color:#ff7900;font-weight:700;}#yiv9159249886 #yiv9159249886ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9159249886 #yiv9159249886ygrp-sponsor #yiv9159249886ov li a {font-size:130%;text-decoration:none;}#yiv9159249886 #yiv9159249886ygrp-sponsor #yiv9159249886ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9159249886 #yiv9159249886ygrp-sponsor #yiv9159249886ov ul {margin:0;padding:0 0 0 8px;}#yiv9159249886 #yiv9159249886ygrp-text {font-family:Georgia;}#yiv9159249886 #yiv9159249886ygrp-text p {margin:0 0 1em 0;}#yiv9159249886 #yiv9159249886ygrp-text tt {font-size:120%;}#yiv9159249886 #yiv9159249886ygrp-vital ul li:last-child {border-right:none !important;}#yiv9159249886
============================================================
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
---------------------------------------------------------------------
Diposting oleh EXCEL FEED LEARNING di 22.07

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)