[belajar-excel] Digest Number 4881

Milis Belajar Microsoft Excel Group

3 Messages

Digest #4881

1a

Re: urutkan data dengan RUMUS [1 Attachment] by "ghozi alkatiri" ghozialkatiri

1b

Re: urutkan data dengan RUMUS by "HAIRUL ALAM"

2

hasil dari data kode nomor yang berulang by "HAIRUL ALAM"

Messages

1a

Re: urutkan data dengan RUMUS [1 Attachment]

Sat Oct 19, 2019 1:30 am (PDT) . Posted by:

"ghozi alkatiri" ghozialkatiri

tulis di F3 (array formula)
=IFERROR(INDEX($D$3:$D$1296,MATCH(SUM(COUNTIF($D$3:$D$1296,F$2:F2)),COUNTIF($D$3:$D$1296,"<"&$D$3:$D$1296),),),"")

copy ke bawah
wassalam
Ghozi Alkatiri
Pada Sabtu, 19 Oktober 2019 08.55.20 WIB, HAIRUL ALAM ha_mj76@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

Salam para Master
Selamat Pagi, semoga selalu dalam keadaan sehat dan semangat
Terlampir data, bagaimana mengurutkan data sesuai nomor yang diinginkan dari kecil ke besar dengan rumus, Terimakasih banyak
AssalamualaikumHairul Alam #yiv0226895623 #yiv0226895623 -- #yiv0226895623ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0226895623 #yiv0226895623ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0226895623 #yiv0226895623ygrp-mkp #yiv0226895623hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0226895623 #yiv0226895623ygrp-mkp #yiv0226895623ads {margin-bottom:10px;}#yiv0226895623 #yiv0226895623ygrp-mkp .yiv0226895623ad {padding:0 0;}#yiv0226895623 #yiv0226895623ygrp-mkp .yiv0226895623ad p {margin:0;}#yiv0226895623 #yiv0226895623ygrp-mkp .yiv0226895623ad a {color:#0000ff;text-decoration:none;}#yiv0226895623 #yiv0226895623ygrp-sponsor #yiv0226895623ygrp-lc {font-family:Arial;}#yiv0226895623 #yiv0226895623ygrp-sponsor #yiv0226895623ygrp-lc #yiv0226895623hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0226895623 #yiv0226895623ygrp-sponsor #yiv0226895623ygrp-lc .yiv0226895623ad {margin-bottom:10px;padding:0 0;}#yiv0226895623 #yiv0226895623actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0226895623 #yiv0226895623activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0226895623 #yiv0226895623activity span {font-weight:700;}#yiv0226895623 #yiv0226895623activity span:first-child {text-transform:uppercase;}#yiv0226895623 #yiv0226895623activity span a {color:#5085b6;text-decoration:none;}#yiv0226895623 #yiv0226895623activity span span {color:#ff7900;}#yiv0226895623 #yiv0226895623activity span ..yiv0226895623underline {text-decoration:underline;}#yiv0226895623 .yiv0226895623attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0226895623 .yiv0226895623attach div a {text-decoration:none;}#yiv0226895623 .yiv0226895623attach img {border:none;padding-right:5px;}#yiv0226895623 .yiv0226895623attach label {display:block;margin-bottom:5px;}#yiv0226895623 .yiv0226895623attach label a {text-decoration:none;}#yiv0226895623 blockquote {margin:0 0 0 4px;}#yiv0226895623 .yiv0226895623bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0226895623 .yiv0226895623bold a {text-decoration:none;}#yiv0226895623 dd.yiv0226895623last p a {font-family:Verdana;font-weight:700;}#yiv0226895623 dd.yiv0226895623last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0226895623 dd.yiv0226895623last p span.yiv0226895623yshortcuts {margin-right:0;}#yiv0226895623 div.yiv0226895623attach-table div div a {text-decoration:none;}#yiv0226895623 div.yiv0226895623attach-table {width:400px;}#yiv0226895623 div.yiv0226895623file-title a, #yiv0226895623 div.yiv0226895623file-title a:active, #yiv0226895623 div.yiv0226895623file-title a:hover, #yiv0226895623 div.yiv0226895623file-title a:visited {text-decoration:none;}#yiv0226895623 div.yiv0226895623photo-title a, #yiv0226895623 div.yiv0226895623photo-title a:active, #yiv0226895623 div.yiv0226895623photo-title a:hover, #yiv0226895623 div.yiv0226895623photo-title a:visited {text-decoration:none;}#yiv0226895623 div#yiv0226895623ygrp-mlmsg #yiv0226895623ygrp-msg p a span.yiv0226895623yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0226895623 .yiv0226895623green {color:#628c2a;}#yiv0226895623 .yiv0226895623MsoNormal {margin:0 0 0 0;}#yiv0226895623 o {font-size:0;}#yiv0226895623 #yiv0226895623photos div {float:left;width:72px;}#yiv0226895623 #yiv0226895623photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv0226895623 #yiv0226895623photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0226895623 #yiv0226895623reco-category {font-size:77%;}#yiv0226895623 #yiv0226895623reco-desc {font-size:77%;}#yiv0226895623 .yiv0226895623replbq {margin:4px;}#yiv0226895623 #yiv0226895623ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0226895623 #yiv0226895623ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0226895623 #yiv0226895623ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0226895623 #yiv0226895623ygrp-mlmsg select, #yiv0226895623 input, #yiv0226895623 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0226895623 #yiv0226895623ygrp-mlmsg pre, #yiv0226895623 code {font:115% monospace;}#yiv0226895623 #yiv0226895623ygrp-mlmsg * {line-height:1.22em;}#yiv0226895623 #yiv0226895623ygrp-mlmsg #yiv0226895623logo {padding-bottom:10px;}#yiv0226895623 #yiv0226895623ygrp-msg p a {font-family:Verdana;}#yiv0226895623 #yiv0226895623ygrp-msg p#yiv0226895623attach-count span {color:#1E66AE;font-weight:700;}#yiv0226895623 #yiv0226895623ygrp-reco #yiv0226895623reco-head {color:#ff7900;font-weight:700;}#yiv0226895623 #yiv0226895623ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0226895623 #yiv0226895623ygrp-sponsor #yiv0226895623ov li a {font-size:130%;text-decoration:none;}#yiv0226895623 #yiv0226895623ygrp-sponsor #yiv0226895623ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0226895623 #yiv0226895623ygrp-sponsor #yiv0226895623ov ul {margin:0;padding:0 0 0 8px;}#yiv0226895623 #yiv0226895623ygrp-text {font-family:Georgia;}#yiv0226895623 #yiv0226895623ygrp-text p {margin:0 0 1em 0;}#yiv0226895623 #yiv0226895623ygrp-text tt {font-size:120%;}#yiv0226895623 #yiv0226895623ygrp-vital ul li:last-child {border-right:none !important;}#yiv0226895623

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

1b

Re: urutkan data dengan RUMUS

Sat Oct 19, 2019 2:34 am (PDT) . Posted by:

"HAIRUL ALAM"

Terimakasih Banyak Mas Ghozi
Assalamualaikum
Pada Sabtu, 19 Oktober 2019 15.31.05 WIB, ghozi alkatiri ghozialkatiri@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

tulis di F3 (array formula)
=IFERROR(INDEX($D$3:$D$1296,MATCH(SUM(COUNTIF($D$3:$D$1296,F$2:F2)),COUNTIF($D$3:$D$1296,"<"&$D$3:$D$1296),),),"")

copy ke bawah
wassalam
Ghozi Alkatiri
Pada Sabtu, 19 Oktober 2019 08.55.20 WIB, HAIRUL ALAM ha_mj76@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

Salam para Master
Selamat Pagi, semoga selalu dalam keadaan sehat dan semangat
Terlampir data, bagaimana mengurutkan data sesuai nomor yang diinginkan dari kecil ke besar dengan rumus, Terimakasih banyak
AssalamualaikumHairul Alam #yiv2131349457 #yiv2131349457 -- #yiv2131349457ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2131349457 #yiv2131349457ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2131349457 #yiv2131349457ygrp-mkp #yiv2131349457hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2131349457 #yiv2131349457ygrp-mkp #yiv2131349457ads {margin-bottom:10px;}#yiv2131349457 #yiv2131349457ygrp-mkp .yiv2131349457ad {padding:0 0;}#yiv2131349457 #yiv2131349457ygrp-mkp .yiv2131349457ad p {margin:0;}#yiv2131349457 #yiv2131349457ygrp-mkp .yiv2131349457ad a {color:#0000ff;text-decoration:none;}#yiv2131349457 #yiv2131349457ygrp-sponsor #yiv2131349457ygrp-lc {font-family:Arial;}#yiv2131349457 #yiv2131349457ygrp-sponsor #yiv2131349457ygrp-lc #yiv2131349457hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2131349457 #yiv2131349457ygrp-sponsor #yiv2131349457ygrp-lc .yiv2131349457ad {margin-bottom:10px;padding:0 0;}#yiv2131349457 #yiv2131349457actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2131349457 #yiv2131349457activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2131349457 #yiv2131349457activity span {font-weight:700;}#yiv2131349457 #yiv2131349457activity span:first-child {text-transform:uppercase;}#yiv2131349457 #yiv2131349457activity span a {color:#5085b6;text-decoration:none;}#yiv2131349457 #yiv2131349457activity span span {color:#ff7900;}#yiv2131349457 #yiv2131349457activity span .yiv2131349457underline {text-decoration:underline;}#yiv2131349457 .yiv2131349457attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2131349457 .yiv2131349457attach div a {text-decoration:none;}#yiv2131349457 .yiv2131349457attach img {border:none;padding-right:5px;}#yiv2131349457 .yiv2131349457attach label {display:block;margin-bottom:5px;}#yiv2131349457 .yiv2131349457attach label a {text-decoration:none;}#yiv2131349457 blockquote {margin:0 0 0 4px;}#yiv2131349457 .yiv2131349457bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2131349457 .yiv2131349457bold a {text-decoration:none;}#yiv2131349457 dd.yiv2131349457last p a {font-family:Verdana;font-weight:700;}#yiv2131349457 dd.yiv2131349457last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2131349457 dd.yiv2131349457last p span.yiv2131349457yshortcuts {margin-right:0;}#yiv2131349457 div.yiv2131349457attach-table div div a {text-decoration:none;}#yiv2131349457 div.yiv2131349457attach-table {width:400px;}#yiv2131349457 div.yiv2131349457file-title a, #yiv2131349457 div.yiv2131349457file-title a:active, #yiv2131349457 div.yiv2131349457file-title a:hover, #yiv2131349457 div.yiv2131349457file-title a:visited {text-decoration:none;}#yiv2131349457 div.yiv2131349457photo-title a, #yiv2131349457 div.yiv2131349457photo-title a:active, #yiv2131349457 div.yiv2131349457photo-title a:hover, #yiv2131349457 div.yiv2131349457photo-title a:visited {text-decoration:none;}#yiv2131349457 div#yiv2131349457ygrp-mlmsg #yiv2131349457ygrp-msg p a span.yiv2131349457yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2131349457 ...yiv2131349457green {color:#628c2a;}#yiv2131349457 .yiv2131349457MsoNormal {margin:0 0 0 0;}#yiv2131349457 o {font-size:0;}#yiv2131349457 #yiv2131349457photos div {float:left;width:72px;}#yiv2131349457 #yiv2131349457photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv2131349457 #yiv2131349457photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2131349457 #yiv2131349457reco-category {font-size:77%;}#yiv2131349457 #yiv2131349457reco-desc {font-size:77%;}#yiv2131349457 .yiv2131349457replbq {margin:4px;}#yiv2131349457 #yiv2131349457ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2131349457 #yiv2131349457ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2131349457 #yiv2131349457ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2131349457 #yiv2131349457ygrp-mlmsg select, #yiv2131349457 input, #yiv2131349457 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2131349457 #yiv2131349457ygrp-mlmsg pre, #yiv2131349457 code {font:115% monospace;}#yiv2131349457 #yiv2131349457ygrp-mlmsg * {line-height:1.22em;}#yiv2131349457 #yiv2131349457ygrp-mlmsg #yiv2131349457logo {padding-bottom:10px;}#yiv2131349457 #yiv2131349457ygrp-msg p a {font-family:Verdana;}#yiv2131349457 #yiv2131349457ygrp-msg p#yiv2131349457attach-count span {color:#1E66AE;font-weight:700;}#yiv2131349457 #yiv2131349457ygrp-reco #yiv2131349457reco-head {color:#ff7900;font-weight:700;}#yiv2131349457 #yiv2131349457ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2131349457 #yiv2131349457ygrp-sponsor #yiv2131349457ov li a {font-size:130%;text-decoration:none;}#yiv2131349457 #yiv2131349457ygrp-sponsor #yiv2131349457ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2131349457 #yiv2131349457ygrp-sponsor #yiv2131349457ov ul {margin:0;padding:0 0 0 8px;}#yiv2131349457 #yiv2131349457ygrp-text {font-family:Georgia;}#yiv2131349457 #yiv2131349457ygrp-text p {margin:0 0 1em 0;}#yiv2131349457 #yiv2131349457ygrp-text tt {font-size:120%;}#yiv2131349457 #yiv2131349457ygrp-vital ul li:last-child {border-right:none !important;}#yiv2131349457

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

2

hasil dari data kode nomor yang berulang

Sat Oct 19, 2019 2:35 am (PDT) . Posted by:

"HAIRUL ALAM"

Salam Para Master
Selamat Sore, semoga selalu dalam keadaan sehat

sepertidata terlampir, Hasilyang diinginkan seperti nilairange (D sd I) data yang diambil dari sheet "data1" sedangkan yangdiperlukan RUMUS untuk range L sd Q kotak warna merah data yang diambil darisheet "data2" dengan kode no yang tanpa menggunakan tanda ( ' ) baikdibelakan atau didepannya (kode nomor angka neumerik)

Terimakasih banyak
Assalamualaikum
Hairul Alam

Reply to sender . Reply to group . Reply via Web Post . All Messages (1) . 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

• Privacy • Unsubscribe • Terms of Use