[belajar-excel] Menentukan data awal dan data akhir [1 Attachment]

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Menentukan data awal dan data akhir [1 Attachment]

Milis Belajar Excel mailing list
Assalamualaikum Wr. Wb.Para Master Excel
Maaf mengganggu untuk semuanya
Saya hendak menentukan data awal dan data akhir berdasarkan kode tertentu seperti data terlampirbagaimanakah formulanya agar data bisa ditentukan sesuai lampiran cell "F3" & "F4" yang saya beri warna kuning.
Terimakasih BanyakWassalamualaikum Wr. Wb.
Hairul Alam

Menentukan Data Awal dan Data Akhir.xlsx (13K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Menentukan data awal dan data akhir

Milis Belajar Excel mailing list
Walaikumsalam warahmatullah.

Dear Hairul Alam,

Pakai kolom bantu yaa.....

di D3 sebagai kolom bantu digunakan formula:
=B3&TEXT(COUNTIF($B$3:B3;B3);"00")

copy formula ini sampai D21

di F3 digunakan formula:

=INDEX($C$3:$C$21;MATCH(E3&"01";$D$3:$D$21;0))

sedangkan di G3 formulanya:

=INDEX($C$3:$C$21;MATCH(E3&TEXT(COUNTIF($B$3:$B$21;E3);"00");$D$3:$D$21;0))

Jangan lupa ganti tanda ; menjadi , jika regional settingnya berbahasa
inggris

salam

NangAgus


On 19/05/17 08.17, HAIRUL ALAM [hidden email] [belajar-excel] wrote:

> Assalamualaikum Wr. Wb.
> Para Master Excel
>
> Maaf mengganggu untuk semuanya
>
> Saya hendak menentukan data awal dan data akhir berdasarkan kode
> tertentu seperti data terlampir
> bagaimanakah formulanya agar data bisa ditentukan sesuai lampiran cell
> "F3" & "F4" yang saya beri warna kuning.
>
> Terimakasih Banyak
> Wassalamualaikum Wr. Wb.
>
> Hairul Alam
>

Reply | Threaded
Open this post in threaded view
|

Bls: [belajar-excel] Menentukan data awal dan data akhir

Milis Belajar Excel mailing list
AssalamualaikumPara Master
Terimakasih Banyak Pencerahannya
Wassalamualaikum Wr. Wb.Hairul Alam



    Pada Jumat, 19 Mei 2017 11:53, "nangagus [hidden email] [belajar-excel]" <[hidden email]> menulis:




      Walaikumsalam warahmatullah. Dear Hairul Alam, Pakai kolom bantu yaa..... di D3 sebagai kolom bantu digunakan formula:
 =B3&TEXT(COUNTIF($B$3:B3;B3);"00") copy formula ini sampai D21
  di F3 digunakan formula:
  =INDEX($C$3:$C$21;MATCH(E3&"01";$D$3:$D$21;0)) sedangkan di G3 formulanya:
  =INDEX($C$3:$C$21;MATCH(E3&TEXT(COUNTIF($B$3:$B$21;E3);"00");$D$3:$D$21;0)) Jangan lupa ganti tanda ; menjadi , jika regional settingnya berbahasa inggris salam NangAgus

 On 19/05/17 08.17, HAIRUL ALAM [hidden email] [belajar-excel] wrote:

     Assalamualaikum Wr. Wb. Para Master Excel
  Maaf mengganggu untuk semuanya
  Saya hendak menentukan data awal dan data akhir berdasarkan kode tertentu seperti data terlampir bagaimanakah formulanya agar data bisa ditentukan sesuai lampiran cell "F3" & "F4" yang saya beri warna kuning.
  Terimakasih Banyak Wassalamualaikum Wr. Wb.
  Hairul Alam

  #yiv1344155731 #yiv1344155731 -- #yiv1344155731ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1344155731 #yiv1344155731ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1344155731 #yiv1344155731ygrp-mkp #yiv1344155731hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1344155731 #yiv1344155731ygrp-mkp #yiv1344155731ads {margin-bottom:10px;}#yiv1344155731 #yiv1344155731ygrp-mkp .yiv1344155731ad {padding:0 0;}#yiv1344155731 #yiv1344155731ygrp-mkp .yiv1344155731ad p {margin:0;}#yiv1344155731 #yiv1344155731ygrp-mkp .yiv1344155731ad a {color:#0000ff;text-decoration:none;}#yiv1344155731 #yiv1344155731ygrp-sponsor #yiv1344155731ygrp-lc {font-family:Arial;}#yiv1344155731 #yiv1344155731ygrp-sponsor #yiv1344155731ygrp-lc #yiv1344155731hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1344155731 #yiv1344155731ygrp-sponsor #yiv1344155731ygrp-lc .yiv1344155731ad {margin-bottom:10px;padding:0 0;}#yiv1344155731 #yiv1344155731actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1344155731 #yiv1344155731activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1344155731 #yiv1344155731activity span {font-weight:700;}#yiv1344155731 #yiv1344155731activity span:first-child {text-transform:uppercase;}#yiv1344155731 #yiv1344155731activity span a {color:#5085b6;text-decoration:none;}#yiv1344155731 #yiv1344155731activity span span {color:#ff7900;}#yiv1344155731 #yiv1344155731activity span .yiv1344155731underline {text-decoration:underline;}#yiv1344155731 .yiv1344155731attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1344155731 .yiv1344155731attach div a {text-decoration:none;}#yiv1344155731 .yiv1344155731attach img {border:none;padding-right:5px;}#yiv1344155731 .yiv1344155731attach label {display:block;margin-bottom:5px;}#yiv1344155731 .yiv1344155731attach label a {text-decoration:none;}#yiv1344155731 blockquote {margin:0 0 0 4px;}#yiv1344155731 .yiv1344155731bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1344155731 .yiv1344155731bold a {text-decoration:none;}#yiv1344155731 dd.yiv1344155731last p a {font-family:Verdana;font-weight:700;}#yiv1344155731 dd.yiv1344155731last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1344155731 dd.yiv1344155731last p span.yiv1344155731yshortcuts {margin-right:0;}#yiv1344155731 div.yiv1344155731attach-table div div a {text-decoration:none;}#yiv1344155731 div.yiv1344155731attach-table {width:400px;}#yiv1344155731 div.yiv1344155731file-title a, #yiv1344155731 div.yiv1344155731file-title a:active, #yiv1344155731 div.yiv1344155731file-title a:hover, #yiv1344155731 div.yiv1344155731file-title a:visited {text-decoration:none;}#yiv1344155731 div.yiv1344155731photo-title a, #yiv1344155731 div.yiv1344155731photo-title a:active, #yiv1344155731 div.yiv1344155731photo-title a:hover, #yiv1344155731 div.yiv1344155731photo-title a:visited {text-decoration:none;}#yiv1344155731 div#yiv1344155731ygrp-mlmsg #yiv1344155731ygrp-msg p a span.yiv1344155731yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1344155731 .yiv1344155731green {color:#628c2a;}#yiv1344155731 .yiv1344155731MsoNormal {margin:0 0 0 0;}#yiv1344155731 o {font-size:0;}#yiv1344155731 #yiv1344155731photos div {float:left;width:72px;}#yiv1344155731 #yiv1344155731photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1344155731 #yiv1344155731photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1344155731 #yiv1344155731reco-category {font-size:77%;}#yiv1344155731 #yiv1344155731reco-desc {font-size:77%;}#yiv1344155731 .yiv1344155731replbq {margin:4px;}#yiv1344155731 #yiv1344155731ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1344155731 #yiv1344155731ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1344155731 #yiv1344155731ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1344155731 #yiv1344155731ygrp-mlmsg select, #yiv1344155731 input, #yiv1344155731 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1344155731 #yiv1344155731ygrp-mlmsg pre, #yiv1344155731 code {font:115% monospace;}#yiv1344155731 #yiv1344155731ygrp-mlmsg * {line-height:1.22em;}#yiv1344155731 #yiv1344155731ygrp-mlmsg #yiv1344155731logo {padding-bottom:10px;}#yiv1344155731 #yiv1344155731ygrp-msg p a {font-family:Verdana;}#yiv1344155731 #yiv1344155731ygrp-msg p#yiv1344155731attach-count span {color:#1E66AE;font-weight:700;}#yiv1344155731 #yiv1344155731ygrp-reco #yiv1344155731reco-head {color:#ff7900;font-weight:700;}#yiv1344155731 #yiv1344155731ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1344155731 #yiv1344155731ygrp-sponsor #yiv1344155731ov li a {font-size:130%;text-decoration:none;}#yiv1344155731 #yiv1344155731ygrp-sponsor #yiv1344155731ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1344155731 #yiv1344155731ygrp-sponsor #yiv1344155731ov ul {margin:0;padding:0 0 0 8px;}#yiv1344155731 #yiv1344155731ygrp-text {font-family:Georgia;}#yiv1344155731 #yiv1344155731ygrp-text p {margin:0 0 1em 0;}#yiv1344155731 #yiv1344155731ygrp-text tt {font-size:120%;}#yiv1344155731 #yiv1344155731ygrp-vital ul li:last-child {border-right:none !important;}#yiv1344155731



Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Menentukan data awal dan data akhir

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list
Coba pakai vlookup tanpa kolom bantu. Syaratnya, kolom key sorted asc.
Awal : vlookup( key, area_data , 2 , 0 )
Akhir : vlookup( key & "z" , area_data , 2 )

Regards,
Kid
Sent from my smart enough phone

> On May 19, 2017, at 10:53, nangagus [hidden email] [belajar-excel] <[hidden email]> wrote:
>
> Walaikumsalam warahmatullah.
>
> Dear Hairul Alam,
>
> Pakai kolom bantu yaa.....
>
> di D3 sebagai kolom bantu digunakan formula:
> =B3&TEXT(COUNTIF($B$3:B3;B3);"00")
>
> copy formula ini sampai D21
> di F3 digunakan formula:
> =INDEX($C$3:$C$21;MATCH(E3&"01";$D$3:$D$21;0))
>
> sedangkan di G3 formulanya:
> =INDEX($C$3:$C$21;MATCH(E3&TEXT(COUNTIF($B$3:$B$21;E3);"00");$D$3:$D$21;0))
>
> Jangan lupa ganti tanda ; menjadi , jika regional settingnya berbahasa inggris
>
> salam
>
> NangAgus
>
>> On 19/05/17 08.17, HAIRUL ALAM [hidden email] [belajar-excel] wrote:
>>  
>> Assalamualaikum Wr. Wb.
>> Para Master Excel
>>
>> Maaf mengganggu untuk semuanya
>>
>> Saya hendak menentukan data awal dan data akhir berdasarkan kode tertentu seperti data terlampir
>> bagaimanakah formulanya agar data bisa ditentukan sesuai lampiran cell "F3" & "F4" yang saya beri warna kuning.
>>
>> Terimakasih Banyak
>> Wassalamualaikum Wr. Wb.
>>
>> Hairul Alam
>
>
Reply | Threaded
Open this post in threaded view
|

Bls: [belajar-excel] Menentukan data awal dan data akhir

Milis Belajar Excel mailing list
Terimakasih BanyakPak Kid
Pencerahannya sangat sederhana
Regards,Hairul Alam 


    Pada Jumat, 19 Mei 2017 22:12, "'Mr. Kid' [hidden email] [belajar-excel]" <[hidden email]> menulis:



     Coba pakai vlookup tanpa kolom bantu. Syaratnya, kolom key sorted asc.Awal : vlookup( key, area_data , 2 , 0 )Akhir : vlookup( key & "z" , area_data , 2 )



Regards,Kid
Sent from my smart enough phone
On May 19, 2017, at 10:53, nangagus [hidden email] [belajar-excel] <[hidden email]> wrote:




     Walaikumsalam warahmatullah. Dear Hairul Alam, Pakai kolom bantu yaa..... di D3 sebagai kolom bantu digunakan formula:
 =B3&TEXT(COUNTIF($B$3:B3;B3);"00") copy formula ini sampai D21
  di F3 digunakan formula:
  =INDEX($C$3:$C$21;MATCH(E3&"01";$D$3:$D$21;0)) sedangkan di G3 formulanya:
  =INDEX($C$3:$C$21;MATCH(E3&TEXT(COUNTIF($B$3:$B$21;E3);"00");$D$3:$D$21;0)) Jangan lupa ganti tanda ; menjadi , jika regional settingnya berbahasa inggris salam NangAgus

 On 19/05/17 08.17, HAIRUL ALAM [hidden email] [belajar-excel] wrote:

     Assalamualaikum Wr. Wb. Para Master Excel
  Maaf mengganggu untuk semuanya
  Saya hendak menentukan data awal dan data akhir berdasarkan kode tertentu seperti data terlampir bagaimanakah formulanya agar data bisa ditentukan sesuai lampiran cell "F3" & "F4" yang saya beri warna kuning.
  Terimakasih Banyak Wassalamualaikum Wr. Wb.
  Hairul Alam


  #yiv4100036361 #yiv4100036361 -- #yiv4100036361ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4100036361 #yiv4100036361ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4100036361 #yiv4100036361ygrp-mkp #yiv4100036361hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4100036361 #yiv4100036361ygrp-mkp #yiv4100036361ads {margin-bottom:10px;}#yiv4100036361 #yiv4100036361ygrp-mkp .yiv4100036361ad {padding:0 0;}#yiv4100036361 #yiv4100036361ygrp-mkp .yiv4100036361ad p {margin:0;}#yiv4100036361 #yiv4100036361ygrp-mkp .yiv4100036361ad a {color:#0000ff;text-decoration:none;}#yiv4100036361 #yiv4100036361ygrp-sponsor #yiv4100036361ygrp-lc {font-family:Arial;}#yiv4100036361 #yiv4100036361ygrp-sponsor #yiv4100036361ygrp-lc #yiv4100036361hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4100036361 #yiv4100036361ygrp-sponsor #yiv4100036361ygrp-lc .yiv4100036361ad {margin-bottom:10px;padding:0 0;}#yiv4100036361 #yiv4100036361actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4100036361 #yiv4100036361activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4100036361 #yiv4100036361activity span {font-weight:700;}#yiv4100036361 #yiv4100036361activity span:first-child {text-transform:uppercase;}#yiv4100036361 #yiv4100036361activity span a {color:#5085b6;text-decoration:none;}#yiv4100036361 #yiv4100036361activity span span {color:#ff7900;}#yiv4100036361 #yiv4100036361activity span .yiv4100036361underline {text-decoration:underline;}#yiv4100036361 .yiv4100036361attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4100036361 .yiv4100036361attach div a {text-decoration:none;}#yiv4100036361 .yiv4100036361attach img {border:none;padding-right:5px;}#yiv4100036361 .yiv4100036361attach label {display:block;margin-bottom:5px;}#yiv4100036361 .yiv4100036361attach label a {text-decoration:none;}#yiv4100036361 blockquote {margin:0 0 0 4px;}#yiv4100036361 .yiv4100036361bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4100036361 .yiv4100036361bold a {text-decoration:none;}#yiv4100036361 dd.yiv4100036361last p a {font-family:Verdana;font-weight:700;}#yiv4100036361 dd.yiv4100036361last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4100036361 dd.yiv4100036361last p span.yiv4100036361yshortcuts {margin-right:0;}#yiv4100036361 div.yiv4100036361attach-table div div a {text-decoration:none;}#yiv4100036361 div.yiv4100036361attach-table {width:400px;}#yiv4100036361 div.yiv4100036361file-title a, #yiv4100036361 div.yiv4100036361file-title a:active, #yiv4100036361 div.yiv4100036361file-title a:hover, #yiv4100036361 div.yiv4100036361file-title a:visited {text-decoration:none;}#yiv4100036361 div.yiv4100036361photo-title a, #yiv4100036361 div.yiv4100036361photo-title a:active, #yiv4100036361 div.yiv4100036361photo-title a:hover, #yiv4100036361 div.yiv4100036361photo-title a:visited {text-decoration:none;}#yiv4100036361 div#yiv4100036361ygrp-mlmsg #yiv4100036361ygrp-msg p a span.yiv4100036361yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4100036361 .yiv4100036361green {color:#628c2a;}#yiv4100036361 .yiv4100036361MsoNormal {margin:0 0 0 0;}#yiv4100036361 o {font-size:0;}#yiv4100036361 #yiv4100036361photos div {float:left;width:72px;}#yiv4100036361 #yiv4100036361photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4100036361 #yiv4100036361photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4100036361 #yiv4100036361reco-category {font-size:77%;}#yiv4100036361 #yiv4100036361reco-desc {font-size:77%;}#yiv4100036361 .yiv4100036361replbq {margin:4px;}#yiv4100036361 #yiv4100036361ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4100036361 #yiv4100036361ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4100036361 #yiv4100036361ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4100036361 #yiv4100036361ygrp-mlmsg select, #yiv4100036361 input, #yiv4100036361 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4100036361 #yiv4100036361ygrp-mlmsg pre, #yiv4100036361 code {font:115% monospace;}#yiv4100036361 #yiv4100036361ygrp-mlmsg * {line-height:1.22em;}#yiv4100036361 #yiv4100036361ygrp-mlmsg #yiv4100036361logo {padding-bottom:10px;}#yiv4100036361 #yiv4100036361ygrp-msg p a {font-family:Verdana;}#yiv4100036361 #yiv4100036361ygrp-msg p#yiv4100036361attach-count span {color:#1E66AE;font-weight:700;}#yiv4100036361 #yiv4100036361ygrp-reco #yiv4100036361reco-head {color:#ff7900;font-weight:700;}#yiv4100036361 #yiv4100036361ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4100036361 #yiv4100036361ygrp-sponsor #yiv4100036361ov li a {font-size:130%;text-decoration:none;}#yiv4100036361 #yiv4100036361ygrp-sponsor #yiv4100036361ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4100036361 #yiv4100036361ygrp-sponsor #yiv4100036361ov ul {margin:0;padding:0 0 0 8px;}#yiv4100036361 #yiv4100036361ygrp-text {font-family:Georgia;}#yiv4100036361 #yiv4100036361ygrp-text p {margin:0 0 1em 0;}#yiv4100036361 #yiv4100036361ygrp-text tt {font-size:120%;}#yiv4100036361 #yiv4100036361ygrp-vital ul li:last-child {border-right:none !important;}#yiv4100036361