[belajar-excel] Digest Number 3169

Milis Belajar Microsoft Excel Group

5 Messages

Digest #3169

1a

Re: menyaring data dengan kriteria tertentu by "hendrik karnadi" hendrikkarnadi

2a

Re: Sort Data Dengan Kriteria by "ris ha" har1sn.excel

3

Menampilkan Multiple data secara vertikal dengan kriteria by "ris ha" har1sn.excel

4a

Mohon bantuan untuk edit penomoran data di cell by sehatt@ymail.com

4b

Re: Mohon bantuan untuk edit penomoran data di cell by "WIRAnata Kemala" kwiranata

Messages

1a

Re: menyaring data dengan kriteria tertentu

Tue Nov 4, 2014 1:54 am (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Hai...(sdn55_kobi@yahoo.co.id),

Seperti telah sering disampaikan oleh bang Momod, sebaiknya garis besar kasus diuraikan dalam body email (supaya bisa cepat dijawab),

semisal

- data npsn sekolah (tidak semua member berasal dari sekolah dan mengetahui apa singkatan dan arti dari npsn)...perlu diuraikan dalam body email apakah data hanya terdiri dari satu kolom atau beberapa kolom dan yang 8 digit ada di kolom mana

- apabila jumlah digit itu benar maka data tersebut dicopi otomatis ke sheet lain...perlu diperjelas data tersebut sebaris/sekolom atau beberapa baris/beberapa kolom.

Input yang ada maupun output yang dikehendaki perlu "diterjemahkan" ke dalam Workbook atau Worksheets dengan sedikit penjelasan sehingga pihak yang ingin membantu dapat mengetahui dengan jelas kebutuhan anda.

Sebagai pelengkap biasanya disertai dengan salam penutup dan nama jelas penanya sehingga Be-Exceller yang menjawab dapat menyapa anda....

Salam,

HK
On Monday, 3 November 2014, 16:28, "sdn55_kobi@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:


 

saya punya kasus, bagaimana cara mencek data npsn sekolah dengan jumlah digit adalah 8 digit, dan apabila jumlah digit itu benar, maka data tersebut di copi otomatis ke sheet yang lain. mohon bantu para master.

<!--#yiv9283684000 #yiv9283684000ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9283684000 #yiv9283684000ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9283684000 #yiv9283684000ygrp-mkp #yiv9283684000hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9283684000 #yiv9283684000ygrp-mkp #yiv9283684000ads {margin-bottom:10px;}#yiv9283684000 #yiv9283684000ygrp-mkp .yiv9283684000ad {padding:0 0;}#yiv9283684000 #yiv9283684000ygrp-mkp .yiv9283684000ad p {margin:0;}#yiv9283684000 #yiv9283684000ygrp-mkp .yiv9283684000ad a {color:#0000ff;text-decoration:none;}#yiv9283684000 #yiv9283684000ygrp-sponsor #yiv9283684000ygrp-lc {font-family:Arial;}#yiv9283684000 #yiv9283684000ygrp-sponsor #yiv9283684000ygrp-lc #yiv9283684000hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9283684000 #yiv9283684000ygrp-sponsor #yiv9283684000ygrp-lc .yiv9283684000ad {margin-bottom:10px;padding:0 0;}#yiv9283684000 #yiv9283684000actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9283684000 #yiv9283684000activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9283684000 #yiv9283684000activity span {font-weight:700;}#yiv9283684000 #yiv9283684000activity span:first-child {text-transform:uppercase;}#yiv9283684000 #yiv9283684000activity span a {color:#5085b6;text-decoration:none;}#yiv9283684000 #yiv9283684000activity span span {color:#ff7900;}#yiv9283684000 #yiv9283684000activity span .yiv9283684000underline {text-decoration:underline;}#yiv9283684000 .yiv9283684000attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9283684000 .yiv9283684000attach div a {text-decoration:none;}#yiv9283684000 .yiv9283684000attach img {border:none;padding-right:5px;}#yiv9283684000 .yiv9283684000attach label {display:block;margin-bottom:5px;}#yiv9283684000 .yiv9283684000attach label a {text-decoration:none;}#yiv9283684000 blockquote {margin:0 0 0 4px;}#yiv9283684000 .yiv9283684000bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9283684000 .yiv9283684000bold a {text-decoration:none;}#yiv9283684000 dd.yiv9283684000last p a {font-family:Verdana;font-weight:700;}#yiv9283684000 dd.yiv9283684000last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9283684000 dd.yiv9283684000last p span.yiv9283684000yshortcuts {margin-right:0;}#yiv9283684000 div.yiv9283684000attach-table div div a {text-decoration:none;}#yiv9283684000 div.yiv9283684000attach-table {width:400px;}#yiv9283684000 div.yiv9283684000file-title a, #yiv9283684000 div.yiv9283684000file-title a:active, #yiv9283684000 div.yiv9283684000file-title a:hover, #yiv9283684000 div.yiv9283684000file-title a:visited {text-decoration:none;}#yiv9283684000 div.yiv9283684000photo-title a, #yiv9283684000 div.yiv9283684000photo-title a:active, #yiv9283684000 div.yiv9283684000photo-title a:hover, #yiv9283684000 div.yiv9283684000photo-title a:visited {text-decoration:none;}#yiv9283684000 div#yiv9283684000ygrp-mlmsg #yiv9283684000ygrp-msg p a span.yiv9283684000yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9283684000 .yiv9283684000green {color:#628c2a;}#yiv9283684000 .yiv9283684000MsoNormal {margin:0 0 0 0;}#yiv9283684000 o {font-size:0;}#yiv9283684000 #yiv9283684000photos div {float:left;width:72px;}#yiv9283684000 #yiv9283684000photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9283684000 #yiv9283684000photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9283684000 #yiv9283684000reco-category {font-size:77%;}#yiv9283684000 #yiv9283684000reco-desc {font-size:77%;}#yiv9283684000 .yiv9283684000replbq {margin:4px;}#yiv9283684000 #yiv9283684000ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9283684000 #yiv9283684000ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9283684000 #yiv9283684000ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9283684000 #yiv9283684000ygrp-mlmsg select, #yiv9283684000 input, #yiv9283684000 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9283684000 #yiv9283684000ygrp-mlmsg pre, #yiv9283684000 code {font:115% monospace;}#yiv9283684000 #yiv9283684000ygrp-mlmsg * {line-height:1.22em;}#yiv9283684000 #yiv9283684000ygrp-mlmsg #yiv9283684000logo {padding-bottom:10px;}#yiv9283684000 #yiv9283684000ygrp-msg p a {font-family:Verdana;}#yiv9283684000 #yiv9283684000ygrp-msg p#yiv9283684000attach-count span {color:#1E66AE;font-weight:700;}#yiv9283684000 #yiv9283684000ygrp-reco #yiv9283684000reco-head {color:#ff7900;font-weight:700;}#yiv9283684000 #yiv9283684000ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9283684000 #yiv9283684000ygrp-sponsor #yiv9283684000ov li a {font-size:130%;text-decoration:none;}#yiv9283684000 #yiv9283684000ygrp-sponsor #yiv9283684000ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9283684000 #yiv9283684000ygrp-sponsor #yiv9283684000ov ul {margin:0;padding:0 0 0 8px;}#yiv9283684000 #yiv9283684000ygrp-text {font-family:Georgia;}#yiv9283684000 #yiv9283684000ygrp-text p {margin:0 0 1em 0;}#yiv9283684000 #yiv9283684000ygrp-text tt {font-size:120%;}#yiv9283684000 #yiv9283684000ygrp-vital ul li:last-child {border-right:none !important;}-->

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2a

Re: Sort Data Dengan Kriteria

Tue Nov 4, 2014 2:24 am (PST) . Posted by:

"ris ha" har1sn.excel

Terima kasih pak bagus atas solusinya

On Monday, November 3, 2014 3:42 PM, "'Bagus' bagus@kingjim.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:


 

Pak Haris; Coba pada Hasil 1, menjadi begini:=IFERROR(INDEX(LEFT($A$2:$A$21,1),MATCH(0,COUNTIF($B$1:B1,LEFT($A$2:$A$211)),0)),"") => Array formula ya.. lalu copas kebawah dan jml menjadi begini:=IF(LEN(B2),COUNTIF($A$2:$A$21,B2&"*"),"")Copas kebawah  
Wassalam

~ Bagus ~

----- Original Message ----- From: ris ha har1sn.excel@yahoo.com [belajar-excel] To: belajar-excel@yahoogroups.com Sent: Monday, November 03, 2014 8:42 AM Subject: [***SPAM*** Score/Req: 05.20/5.0] [belajar-excel] Sort Data Dengan Kriteria [1 Attachment]
 

Assalamu'alaikum para suhu dan master excel semua.
Mohon solusinya. Bagaimana meng-sort data dengan kriteria, file terlampir
Terima kasih
Salam Haris


<!--#yiv9581904261 #yiv9581904261ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9581904261 #yiv9581904261ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9581904261 #yiv9581904261ygrp-mkp #yiv9581904261hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9581904261 #yiv9581904261ygrp-mkp #yiv9581904261ads {margin-bottom:10px;}#yiv9581904261 #yiv9581904261ygrp-mkp .yiv9581904261ad {padding:0 0;}#yiv9581904261 #yiv9581904261ygrp-mkp .yiv9581904261ad p {margin:0;}#yiv9581904261 #yiv9581904261ygrp-mkp .yiv9581904261ad a {color:#0000ff;text-decoration:none;}#yiv9581904261 #yiv9581904261ygrp-sponsor #yiv9581904261ygrp-lc {font-family:Arial;}#yiv9581904261 #yiv9581904261ygrp-sponsor #yiv9581904261ygrp-lc #yiv9581904261hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9581904261 #yiv9581904261ygrp-sponsor #yiv9581904261ygrp-lc .yiv9581904261ad {margin-bottom:10px;padding:0 0;}#yiv9581904261 #yiv9581904261actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9581904261 #yiv9581904261activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9581904261 #yiv9581904261activity span {font-weight:700;}#yiv9581904261 #yiv9581904261activity span:first-child {text-transform:uppercase;}#yiv9581904261 #yiv9581904261activity span a {color:#5085b6;text-decoration:none;}#yiv9581904261 #yiv9581904261activity span span {color:#ff7900;}#yiv9581904261 #yiv9581904261activity span .yiv9581904261underline {text-decoration:underline;}#yiv9581904261 .yiv9581904261attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9581904261 .yiv9581904261attach div a {text-decoration:none;}#yiv9581904261 .yiv9581904261attach img {border:none;padding-right:5px;}#yiv9581904261 .yiv9581904261attach label {display:block;margin-bottom:5px;}#yiv9581904261 .yiv9581904261attach label a {text-decoration:none;}#yiv9581904261 blockquote {margin:0 0 0 4px;}#yiv9581904261 .yiv9581904261bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9581904261 .yiv9581904261bold a {text-decoration:none;}#yiv9581904261 dd.yiv9581904261last p a {font-family:Verdana;font-weight:700;}#yiv9581904261 dd.yiv9581904261last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9581904261 dd.yiv9581904261last p span.yiv9581904261yshortcuts {margin-right:0;}#yiv9581904261 div.yiv9581904261attach-table div div a {text-decoration:none;}#yiv9581904261 div.yiv9581904261attach-table {width:400px;}#yiv9581904261 div.yiv9581904261file-title a, #yiv9581904261 div.yiv9581904261file-title a:active, #yiv9581904261 div.yiv9581904261file-title a:hover, #yiv9581904261 div.yiv9581904261file-title a:visited {text-decoration:none;}#yiv9581904261 div.yiv9581904261photo-title a, #yiv9581904261 div.yiv9581904261photo-title a:active, #yiv9581904261 div.yiv9581904261photo-title a:hover, #yiv9581904261 div.yiv9581904261photo-title a:visited {text-decoration:none;}#yiv9581904261 div#yiv9581904261ygrp-mlmsg #yiv9581904261ygrp-msg p a span.yiv9581904261yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9581904261 .yiv9581904261green {color:#628c2a;}#yiv9581904261 .yiv9581904261MsoNormal {margin:0 0 0 0;}#yiv9581904261 o {font-size:0;}#yiv9581904261 #yiv9581904261photos div {float:left;width:72px;}#yiv9581904261 #yiv9581904261photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9581904261 #yiv9581904261photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9581904261 #yiv9581904261reco-category {font-size:77%;}#yiv9581904261 #yiv9581904261reco-desc {font-size:77%;}#yiv9581904261 .yiv9581904261replbq {margin:4px;}#yiv9581904261 #yiv9581904261ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9581904261 #yiv9581904261ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9581904261 #yiv9581904261ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9581904261 #yiv9581904261ygrp-mlmsg select, #yiv9581904261 input, #yiv9581904261 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9581904261 #yiv9581904261ygrp-mlmsg pre, #yiv9581904261 code {font:115% monospace;}#yiv9581904261 #yiv9581904261ygrp-mlmsg * {line-height:1.22em;}#yiv9581904261 #yiv9581904261ygrp-mlmsg #yiv9581904261logo {padding-bottom:10px;}#yiv9581904261 #yiv9581904261ygrp-msg p a {font-family:Verdana;}#yiv9581904261 #yiv9581904261ygrp-msg p#yiv9581904261attach-count span {color:#1E66AE;font-weight:700;}#yiv9581904261 #yiv9581904261ygrp-reco #yiv9581904261reco-head {color:#ff7900;font-weight:700;}#yiv9581904261 #yiv9581904261ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9581904261 #yiv9581904261ygrp-sponsor #yiv9581904261ov li a {font-size:130%;text-decoration:none;}#yiv9581904261 #yiv9581904261ygrp-sponsor #yiv9581904261ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9581904261 #yiv9581904261ygrp-sponsor #yiv9581904261ov ul {margin:0;padding:0 0 0 8px;}#yiv9581904261 #yiv9581904261ygrp-text {font-family:Georgia;}#yiv9581904261 #yiv9581904261ygrp-text p {margin:0 0 1em 0;}#yiv9581904261 #yiv9581904261ygrp-text tt {font-size:120%;}#yiv9581904261 #yiv9581904261ygrp-vital ul li:last-child {border-right:none !important;}-->

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3

Menampilkan Multiple data secara vertikal dengan kriteria

Tue Nov 4, 2014 2:25 am (PST) . Posted by:

"ris ha" har1sn.excel

Assalamu'alaikum para master yang saya hormati

Siang ini, saya ada masalah, yaitu Cara Menampilkan Multiple data secara vertikal dengan kriteria (File terlampir). Mohon para master dapat berkenan memberikan solusi atas permasalahan saya.

Saya ucapkan terima kasih atas solusi yang diberikan. Semoga para master selalu dimudahkan segala urusan oleh Allah Swt. Amin

Salam

Haris

Reply to sender . Reply to group . Reply via Web Post . All Messages (1) . Top ^

4a

Mohon bantuan untuk edit penomoran data di cell

Tue Nov 4, 2014 2:26 am (PST) . Posted by:

sehatt@ymail.com

Selamat siang teman2 dan pakar2 Excel,

Salam kenal nm saya Tanto. Saya punya kesulitan untuk join 2 cell menjadi 1 cell value spt :

A1 : 000001
B1 : /PPH4/X/2014

Bagaimana cara menjoin 2 cell tsb sehingga

C1 menjadi 000001/PPH4/X/2014,
C2 menjadi 000002/PPH4/X/2014,
C3 menjadi 000003/PPH4/X/2014

Terima kasih atas bantuan nya dari teman2 grup sekalian,

Salam saya,
Tanto

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

4b

Re: Mohon bantuan untuk edit penomoran data di cell

Tue Nov 4, 2014 2:37 am (PST) . Posted by:

"WIRAnata Kemala" kwiranata

newbie coba ngebantu yah :)

cara 1, C1 =A1&B1
cara 2, C1 =CONCATENATE(A1,B1)

2014-11-04 15:46 GMT+07:00 sehatt@ymail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Selamat siang teman2 dan pakar2 Excel,
>
> Salam kenal nm saya Tanto. Saya punya kesulitan untuk join 2 cell menjadi
> 1 cell value spt :
>
> A1 : 000001
> B1 : /PPH4/X/2014
>
> Bagaimana cara menjoin 2 cell tsb sehingga
>
> C1 menjadi 000001/PPH4/X/2014,
> C2 menjadi 000002/PPH4/X/2014,
> C3 menjadi 000003/PPH4/X/2014
>
> Terima kasih atas bantuan nya dari teman2 grup sekalian,
>
> Salam saya,
> Tanto
>
>
>

--
regards, Wira 鄧又銘
on desktop FireFox

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

============================================================
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
---------------------------------------------------------------------

Visit Your Group

• New Members 4

• Privacy • Unsubscribe • Terms of Use