[belajar-excel] lookup data [1 Attachment]

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

[belajar-excel] lookup data [1 Attachment]

HAIRUL ALAM
Salam Para Master
Selamat Sore, semoga semua dalam keadaan sehat
Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data yang tidak bernilai, mohon solusinya
Terimakasih Banyak
AssalamalaikumHairul Alam

=?UTF-8?b?bG9va3VwIGRhdGEueGxzeA==?= (206K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] lookup data

Kid
Administrator
karena datanya tidak di-sort berdasar kolom kunci lookup nya

On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Salam Para Master
>
> Selamat Sore, semoga semua dalam keadaan sehat
>
> Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak
> berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data
> yang tidak bernilai, mohon solusinya
>
> Terimakasih Banyak
>
> Assalamalaikum
> Hairul Alam
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] lookup data

HAIRUL ALAM
 Salam Master
Saya belum mudeng
Maaf
Assalamualaikum
    Pada Senin, 21 Oktober 2019 16.05.02 GMT+7, 'Mr. Kid' [hidden email] [belajar-excel] <[hidden email]> menulis:

  

karena datanya tidak di-sort berdasar kolom kunci lookup nya
On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> wrote:

  

Salam Para Master
Selamat Sore, semoga semua dalam keadaan sehat
Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data yang tidak bernilai, mohon solusinya
Terimakasih Banyak
AssalamalaikumHairul Alam


  #yiv7084709709 #yiv7084709709 -- #yiv7084709709ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7084709709 #yiv7084709709ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7084709709 #yiv7084709709ygrp-mkp #yiv7084709709hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7084709709 #yiv7084709709ygrp-mkp #yiv7084709709ads {margin-bottom:10px;}#yiv7084709709 #yiv7084709709ygrp-mkp .yiv7084709709ad {padding:0 0;}#yiv7084709709 #yiv7084709709ygrp-mkp .yiv7084709709ad p {margin:0;}#yiv7084709709 #yiv7084709709ygrp-mkp .yiv7084709709ad a {color:#0000ff;text-decoration:none;}#yiv7084709709 #yiv7084709709ygrp-sponsor #yiv7084709709ygrp-lc {font-family:Arial;}#yiv7084709709 #yiv7084709709ygrp-sponsor #yiv7084709709ygrp-lc #yiv7084709709hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7084709709 #yiv7084709709ygrp-sponsor #yiv7084709709ygrp-lc .yiv7084709709ad {margin-bottom:10px;padding:0 0;}#yiv7084709709 #yiv7084709709actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7084709709 #yiv7084709709activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7084709709 #yiv7084709709activity span {font-weight:700;}#yiv7084709709 #yiv7084709709activity span:first-child {text-transform:uppercase;}#yiv7084709709 #yiv7084709709activity span a {color:#5085b6;text-decoration:none;}#yiv7084709709 #yiv7084709709activity span span {color:#ff7900;}#yiv7084709709 #yiv7084709709activity span .yiv7084709709underline {text-decoration:underline;}#yiv7084709709 .yiv7084709709attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7084709709 .yiv7084709709attach div a {text-decoration:none;}#yiv7084709709 .yiv7084709709attach img {border:none;padding-right:5px;}#yiv7084709709 .yiv7084709709attach label {display:block;margin-bottom:5px;}#yiv7084709709 .yiv7084709709attach label a {text-decoration:none;}#yiv7084709709 blockquote {margin:0 0 0 4px;}#yiv7084709709 .yiv7084709709bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7084709709 .yiv7084709709bold a {text-decoration:none;}#yiv7084709709 dd.yiv7084709709last p a {font-family:Verdana;font-weight:700;}#yiv7084709709 dd.yiv7084709709last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7084709709 dd.yiv7084709709last p span.yiv7084709709yshortcuts {margin-right:0;}#yiv7084709709 div.yiv7084709709attach-table div div a {text-decoration:none;}#yiv7084709709 div.yiv7084709709attach-table {width:400px;}#yiv7084709709 div.yiv7084709709file-title a, #yiv7084709709 div.yiv7084709709file-title a:active, #yiv7084709709 div.yiv7084709709file-title a:hover, #yiv7084709709 div.yiv7084709709file-title a:visited {text-decoration:none;}#yiv7084709709 div.yiv7084709709photo-title a, #yiv7084709709 div.yiv7084709709photo-title a:active, #yiv7084709709 div.yiv7084709709photo-title a:hover, #yiv7084709709 div.yiv7084709709photo-title a:visited {text-decoration:none;}#yiv7084709709 div#yiv7084709709ygrp-mlmsg #yiv7084709709ygrp-msg p a span.yiv7084709709yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7084709709 .yiv7084709709green {color:#628c2a;}#yiv7084709709 .yiv7084709709MsoNormal {margin:0 0 0 0;}#yiv7084709709 o {font-size:0;}#yiv7084709709 #yiv7084709709photos div {float:left;width:72px;}#yiv7084709709 #yiv7084709709photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv7084709709 #yiv7084709709photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7084709709 #yiv7084709709reco-category {font-size:77%;}#yiv7084709709 #yiv7084709709reco-desc {font-size:77%;}#yiv7084709709 .yiv7084709709replbq {margin:4px;}#yiv7084709709 #yiv7084709709ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7084709709 #yiv7084709709ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7084709709 #yiv7084709709ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7084709709 #yiv7084709709ygrp-mlmsg select, #yiv7084709709 input, #yiv7084709709 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7084709709 #yiv7084709709ygrp-mlmsg pre, #yiv7084709709 code {font:115% monospace;}#yiv7084709709 #yiv7084709709ygrp-mlmsg * {line-height:1.22em;}#yiv7084709709 #yiv7084709709ygrp-mlmsg #yiv7084709709logo {padding-bottom:10px;}#yiv7084709709 #yiv7084709709ygrp-msg p a {font-family:Verdana;}#yiv7084709709 #yiv7084709709ygrp-msg p#yiv7084709709attach-count span {color:#1E66AE;font-weight:700;}#yiv7084709709 #yiv7084709709ygrp-reco #yiv7084709709reco-head {color:#ff7900;font-weight:700;}#yiv7084709709 #yiv7084709709ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7084709709 #yiv7084709709ygrp-sponsor #yiv7084709709ov li a {font-size:130%;text-decoration:none;}#yiv7084709709 #yiv7084709709ygrp-sponsor #yiv7084709709ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7084709709 #yiv7084709709ygrp-sponsor #yiv7084709709ov ul {margin:0;padding:0 0 0 8px;}#yiv7084709709 #yiv7084709709ygrp-text {font-family:Georgia;}#yiv7084709709 #yiv7084709709ygrp-text p {margin:0 0 1em 0;}#yiv7084709709 #yiv7084709709ygrp-text tt {font-size:120%;}#yiv7084709709 #yiv7084709709ygrp-vital ul li:last-child {border-right:none !important;}#yiv7084709709
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] lookup data

Kid
Administrator
eh...
wkwkwkwkwk....

saya di luar rumah. saya mau ambil laptop yang tertinggal di dalam rumah,
dan saya tidak paham dimana letak kesalahannya karena saya tidak bisa masuk
rumah, mohon solusinya.
> karena pintu rumahnya masih tertutup

Jadi, solusinya apa ya ?
Bongkar jendela lalu masuk lewat jendela ?
Bongkar atap dan plafon lalu masuk dari atas rumah ?

wkwkwkwk....
ada-ada sajah...


Diketahui :
-  data lookup (berarti pakai fungsi-fungsi yang bisa melakukan lookup)
-  ada rumus yang tidak berfungsi (berarti ada yang berfungsi)

Masalah :
-  tidak paham dimana letak kesalahannya

Jawaban :
- karena datanya tidak di-sort berdasar kolom kunci lookup nya
  (gak perlu buka filenya pun sudah jelas begitu penyebabnya, tapi itu cuma
katanya kid, entah kalo katanya yang lainnya ato katanya google n youtube
ato orang barat ya...)
  *** Sort adalah bahasa inggris. Bahasa Indonesianya adalah mengurutkan
berdasar kelompok tertentu (di excel umumnya berdasar kolom tertentu)
  *** Filter adalah bahasa inggris. Bahasa Indonesianya adalah menyaring
atau memilah berdasar kriteria tertentu (di excel biasanya berdasar suatu
nilai tertentu di sebuah cells yang dibutuhkan atau dipilih user)

Jadi, sudah tahu penyebabnya kan...
Sudah terjawab permasalahannya.



On Mon, Oct 21, 2019 at 5:45 PM HAIRUL ALAM [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Salam Master
>
> Saya belum mudeng
>
> Maaf
>
> Assalamualaikum
>
> Pada Senin, 21 Oktober 2019 16.05.02 GMT+7, 'Mr. Kid' [hidden email]
> [belajar-excel] <[hidden email]> menulis:
>
>
>
>
> karena datanya tidak di-sort berdasar kolom kunci lookup nya
>
> On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM [hidden email]
> [belajar-excel] <[hidden email]> wrote:
>
>
>
> Salam Para Master
>
> Selamat Sore, semoga semua dalam keadaan sehat
>
> Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak
> berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data
> yang tidak bernilai, mohon solusinya
>
> Terimakasih Banyak
>
> Assalamalaikum
> Hairul Alam
>
>
>
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] lookup data

M.Ade Alfarid
In reply to this post by HAIRUL ALAM
Mas hairul,

Coba urutkan/filter kolom date nya



ade



*From:* [hidden email] [mailto:[hidden email]]

*Sent:* Monday, October 21, 2019 5:24 PM
*To:* [hidden email]
*Subject:* Re: [belajar-excel] lookup data





Salam Master



Saya belum mudeng



Maaf



Assalamualaikum



Pada Senin, 21 Oktober 2019 16.05.02 GMT+7, 'Mr. Kid' [hidden email]
[belajar-excel] <[hidden email]> menulis:







karena datanya tidak di-sort berdasar kolom kunci lookup nya



On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM [hidden email]
[belajar-excel] <[hidden email]> wrote:



Salam Para Master



Selamat Sore, semoga semua dalam keadaan sehat



Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak
berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data
yang tidak bernilai, mohon solusinya



Terimakasih Banyak



Assalamalaikum

Hairul Alam



image001.jpg (496 bytes) Download Attachment
image002.jpg (454 bytes) Download Attachment
image003.png (26K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] lookup data

M.Ade Alfarid
In reply to this post by HAIRUL ALAM
Mas hairul dgn formula begitu,

Komputer saya jadi berat dan lama kalkulasinya,

Sebaiknya cari cara yg lebih mudah.



*From:* [hidden email] [mailto:[hidden email]]

*Sent:* Monday, October 21, 2019 5:24 PM
*To:* [hidden email]
*Subject:* Re: [belajar-excel] lookup data





Salam Master



Saya belum mudeng



Maaf



Assalamualaikum



Pada Senin, 21 Oktober 2019 16.05.02 GMT+7, 'Mr. Kid' [hidden email]
[belajar-excel] <[hidden email]> menulis:







karena datanya tidak di-sort berdasar kolom kunci lookup nya



On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM [hidden email]
[belajar-excel] <[hidden email]> wrote:



Salam Para Master



Selamat Sore, semoga semua dalam keadaan sehat



Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak
berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data
yang tidak bernilai, mohon solusinya



Terimakasih Banyak



Assalamalaikum

Hairul Alam



image001.jpg (496 bytes) Download Attachment
image002.jpg (454 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Re: lookup data

HAIRUL ALAM
In reply to this post by HAIRUL ALAM
 Salam Mr.Kid
Maaf, master, masalahnya sudah saya masukkan rumus sesuai yang pernah Pak Kid contohkan
=IF(E4=E3,IF(B4=1,"",I3),"")&IF(COUNTIFS(E$3:E3,E4,H$3:H3,H4),"",", "&H4&" : "&COUNTIFS($E$4:$E$1387,E4,$H$4:$H$1387,H4))

saya aplikasikan ke data terlampir tapi hasil yang di hitung tidak sama (ada row yang tidak bernilai = "") itulah yang bikin saya bingung
Maaf dan Terimakasih sebelum dan sesudahnya
Assalamualaikum
    Pada Senin, 21 Oktober 2019 15.15.19 WIB, HAIRUL ALAM <[hidden email]> menulis:  
 
 Salam Para Master
Selamat Sore, semoga semua dalam keadaan sehat
Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data yang tidak bernilai, mohon solusinya
Terimakasih Banyak
AssalamalaikumHairul Alam  
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] lookup data [1 Attachment]

HAIRUL ALAM
In reply to this post by M.Ade Alfarid
 Terimakasih mas ade, iya bisa ternyata dengan mengurutkan datanya
    Pada Senin, 21 Oktober 2019 18.19.40 WIB, 'M.Ade Alfarid' [hidden email] [belajar-excel] <[hidden email]> menulis:

  


Mas hairul,

Coba urutkan/filter kolom date nya



 

ade

 

From: [hidden email] [mailto:[hidden email]]
Sent: Monday, October 21, 2019 5:24 PM
To: [hidden email]
Subject: Re: [belajar-excel] lookup data

 

 

Salam Master

 

Saya belum mudeng

 

Maaf

 

Assalamualaikum

 

Pada Senin, 21 Oktober 2019 16..05.02 GMT+7, 'Mr. Kid' [hidden email] [belajar-excel] <[hidden email]> menulis:

 

 

 

karena datanya tidak di-sort berdasar kolom kunci lookup nya

 

On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM [hidden email] [belajar-excel] <[hidden email]> wrote:


 

Salam Para Master

 

Selamat Sore, semoga semua dalam keadaan sehat

 

Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data yang tidak bernilai, mohon solusinya

 

Terimakasih Banyak

 

Assalamalaikum

Hairul Alam



  #yiv9275747797 #yiv9275747797 -- #yiv9275747797ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9275747797 #yiv9275747797ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9275747797 #yiv9275747797ygrp-mkp #yiv9275747797hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9275747797 #yiv9275747797ygrp-mkp #yiv9275747797ads {margin-bottom:10px;}#yiv9275747797 #yiv9275747797ygrp-mkp .yiv9275747797ad {padding:0 0;}#yiv9275747797 #yiv9275747797ygrp-mkp .yiv9275747797ad p {margin:0;}#yiv9275747797 #yiv9275747797ygrp-mkp .yiv9275747797ad a {color:#0000ff;text-decoration:none;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ygrp-lc {font-family:Arial;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ygrp-lc #yiv9275747797hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ygrp-lc .yiv9275747797ad {margin-bottom:10px;padding:0 0;}#yiv9275747797 #yiv9275747797actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9275747797 #yiv9275747797activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9275747797 #yiv9275747797activity span {font-weight:700;}#yiv9275747797 #yiv9275747797activity span:first-child {text-transform:uppercase;}#yiv9275747797 #yiv9275747797activity span a {color:#5085b6;text-decoration:none;}#yiv9275747797 #yiv9275747797activity span span {color:#ff7900;}#yiv9275747797 #yiv9275747797activity span .yiv9275747797underline {text-decoration:underline;}#yiv9275747797 .yiv9275747797attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9275747797 .yiv9275747797attach div a {text-decoration:none;}#yiv9275747797 .yiv9275747797attach img {border:none;padding-right:5px;}#yiv9275747797 .yiv9275747797attach label {display:block;margin-bottom:5px;}#yiv9275747797 .yiv9275747797attach label a {text-decoration:none;}#yiv9275747797 blockquote {margin:0 0 0 4px;}#yiv9275747797 .yiv9275747797bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9275747797 .yiv9275747797bold a {text-decoration:none;}#yiv9275747797 dd.yiv9275747797last p a {font-family:Verdana;font-weight:700;}#yiv9275747797 dd.yiv9275747797last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9275747797 dd.yiv9275747797last p span.yiv9275747797yshortcuts {margin-right:0;}#yiv9275747797 div.yiv9275747797attach-table div div a {text-decoration:none;}#yiv9275747797 div.yiv9275747797attach-table {width:400px;}#yiv9275747797 div.yiv9275747797file-title a, #yiv9275747797 div.yiv9275747797file-title a:active, #yiv9275747797 div.yiv9275747797file-title a:hover, #yiv9275747797 div.yiv9275747797file-title a:visited {text-decoration:none;}#yiv9275747797 div.yiv9275747797photo-title a, #yiv9275747797 div.yiv9275747797photo-title a:active, #yiv9275747797 div.yiv9275747797photo-title a:hover, #yiv9275747797 div.yiv9275747797photo-title a:visited {text-decoration:none;}#yiv9275747797 div#yiv9275747797ygrp-mlmsg #yiv9275747797ygrp-msg p a span.yiv9275747797yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9275747797 .yiv9275747797green {color:#628c2a;}#yiv9275747797 .yiv9275747797MsoNormal {margin:0 0 0 0;}#yiv9275747797 o {font-size:0;}#yiv9275747797 #yiv9275747797photos div {float:left;width:72px;}#yiv9275747797 #yiv9275747797photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv9275747797 #yiv9275747797photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9275747797 #yiv9275747797reco-category {font-size:77%;}#yiv9275747797 #yiv9275747797reco-desc {font-size:77%;}#yiv9275747797 .yiv9275747797replbq {margin:4px;}#yiv9275747797 #yiv9275747797ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9275747797 #yiv9275747797ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9275747797 #yiv9275747797ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9275747797 #yiv9275747797ygrp-mlmsg select, #yiv9275747797 input, #yiv9275747797 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9275747797 #yiv9275747797ygrp-mlmsg pre, #yiv9275747797 code {font:115% monospace;}#yiv9275747797 #yiv9275747797ygrp-mlmsg * {line-height:1.22em;}#yiv9275747797 #yiv9275747797ygrp-mlmsg #yiv9275747797logo {padding-bottom:10px;}#yiv9275747797 #yiv9275747797ygrp-msg p a {font-family:Verdana;}#yiv9275747797 #yiv9275747797ygrp-msg p#yiv9275747797attach-count span {color:#1E66AE;font-weight:700;}#yiv9275747797 #yiv9275747797ygrp-reco #yiv9275747797reco-head {color:#ff7900;font-weight:700;}#yiv9275747797 #yiv9275747797ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ov li a {font-size:130%;text-decoration:none;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ov ul {margin:0;padding:0 0 0 8px;}#yiv9275747797 #yiv9275747797ygrp-text {font-family:Georgia;}#yiv9275747797 #yiv9275747797ygrp-text p {margin:0 0 1em 0;}#yiv9275747797 #yiv9275747797ygrp-text tt {font-size:120%;}#yiv9275747797 #yiv9275747797ygrp-vital ul li:last-child {border-right:none !important;}#yiv9275747797

image003.png (26K) Download Attachment