iya terima kasih pak Kid, akhirnya saya juga pakai vlookup dengan data yang lengkap kemudian difilter
Pada tanggal 03/11/11, Kid Mr. <mr.nm...@gmail.com> menulis: > Betul itu Pak Himmah, > > Bagaimana kalau dicoba dengan membuat seed number (daftar nomor ideal -- > seluruh nomor --), kemudian disusun list missing number dari daftar > tersebut. > Lebih baik lagi jika daftar tersebut diletakkan pada sheet yang berbeda. > Pada daftar itu diberi kolom bantu untuk mendata nomor yang sudah ada di > sheet data. > > Cara penyusunan list missingnya bisa dengan cara : > 1. Autofilter sheet seed number nya berdasar kolom bantu yang menyatakan > belum ada nomor di sheet data, lalu cope visible cells only ke sheet hasil. > 2. Memanfaatkan pivot table, dengan bagian report field dimasukkan field > kolom bantu yang berisi status ada atau tidaknya si angka seed dalam sheet > data, lalu pilih yang tidak ada di sheet data saja. > 3. Koneksi data ke file Excel itu sendiri, dengan query berupa left join > antara sheet seed numbernya dengan sheet datanya, lalu difilter (WHERE) > nilai sheet datanya adalah NULL. > > Semoga file terlampir lebih bisa menjelaskannya. (download file attachment > yang sesuai dengan versi MS Excel Anda). > Syaratnya : > 1. Letakkan file di drive C: pada root-nya ( C:\ ) > 2. Jangan ganti nama file atau ekstensinya > > Setelah dibuka dan bisa mengganti seluruh properti koneksi, maka bebas akan > meletakkan file dimana saja dengan nama apa saja. > > Wassalam. > Kid. > > 2011/11/2 him mah <himmah....@gmail.com> > >> ** >> >> >> ketika datanya banyak, kalau pakai array formula prosesnya jadi lambat >> >> >> Pada tanggal 02/11/11, Kid Mr. <mr.nm...@gmail.com> menulis: >> > Untuk hasil array formula yang diletakkan di kolom bantu, letakkan >> formula >> > di sebuah cell saja dan ndak perlu ada cell lain yang diblok. >> > Lalu array formula di copy paste kebawah biasa saja seperti copy paste >> > formula lain. >> > >> > Ketika menggunakan udf jointext, cukup buat 1 cell saja tapi array >> formula >> > bagian yang dibiru diemail lalu harus diubah dari row(1:1) jadi >> > row(1:99) >> > >> > Kid. >> > >> > 2011/11/2 him mah <himmah....@gmail.com> >> > >> >> ** >> >> >> >> >> >> >> iya sudah berhasil pak Kid, cuma untuk copy paste array formula harus >> >> satu-satu ya >> >> >> >> Pada tanggal 02/11/11, Kid Mr. <mr.nm...@gmail.com> menulis: >> >> >> >> > Coba array formula : >> >> > =Small( IF( CountIF( range_data, Row( Indirect( "1:" & Max( >> range_data ) >> >> ) >> >> > ) ), "" , Row( Indirect( "1:" & Max( range_data ) ) ) ) , *Row(1:1)* >> >> > ) >> >> >> >> > >> >> > Lebih baik tetap ada yang berupa deret per cell seperti di atas, agar >> >> bisa >> >> > digunakan lebih leluasa. >> >> > >> >> > Untuk menggabungkan kedalam satu cell, bisa buat UDF JoinText >> >> > public function JoinText(rngData as range, optional sDelimiter as >> >> > string=vbnullstring,optional bNotBlank as boolean = true) as string >> >> > dim sRes as string >> >> > dim vTmp as variant >> >> > >> >> > for each vtmp in rngdata >> >> > if not iserror(vtmp) then >> >> > if lenb(vtmp)<>0 or not bnotblank then >> >> > sres=sres & sdelimiter & vtmp >> >> > endif >> >> > endif >> >> > next vtmp >> >> > jointext=sres >> >> > if lenb(sdelimiter)<>0 then >> >> > jointext=mid$(sres,2) >> >> > endif >> >> > end function >> >> > >> >> > Cara pakai di worksheet : >> >> > (join semua text di range datanya dengan delimiter titik koma dan >> >> > yang >> >> > blank *gak *boleh ikut) >> >> >> >> > =jointext( range_datanya , ";" ) >> >> > >> >> > (join semua text di range datanya dengan delimiter titik koma dan >> >> > yang >> >> > blank boleh ikut) >> >> > =jointext( range_datanya , ";" , false) >> >> > >> >> > Jika ingin membuat fungsi yang bisa menerima array (misal hasil array >> >> > formula), sehingga langsung tanpa ada kolom bantu, maka ubah jadi : >> >> > public function JoinText(*vData as variant*, optional sDelimiter as >> >> >> >> > string=vbnullstring,optional bNotBlank as boolean = true) as string >> >> > dim sRes as string >> >> > dim vTmp as variant >> >> > >> >> > * if isarray(vdata) then >> >> > * for each vtmp in *vdata* >> >> >> >> > if not iserror(vtmp) then >> >> > if lenb(vtmp)<>0 or not bnotblank then >> >> > sres=sres & sdelimiter & vtmp >> >> > endif >> >> > endif >> >> > next vtmp >> >> > * else >> >> > sres=vdata >> >> > endif >> >> > * jointext=sres >> >> >> >> > if lenb(sdelimiter)<>0 then >> >> > jointext=mid$(sres,2) >> >> > endif >> >> > end function >> >> > >> >> > Jadi bisa dipakai menjadi array formula di satu cell : >> >> > (join semua text hasil array formula dengan delimiter titik koma dan >> >> > yang >> >> > blank *gak *boleh ikut) >> >> >> >> > =JoinText( Small( IF( CountIF( range_data, Row( Indirect( "1:" & Max( >> >> > range_data ) ) ) ), "" , Row( Indirect( "1:" & Max( range_data ) ) ) >> >> > ) >> >> > ,* >> >> > Row(1:99)* , ";" ) >> >> >> >> > >> >> > (join semua text hasil array formula dengan delimiter titik koma dan >> >> > yang >> >> > blank boleh ikut) >> >> > =JoinText( Small( IF( CountIF( range_data, Row( Indirect( "1:" & Max( >> >> > range_data ) ) ) ), "" , Row( Indirect( "1:" & Max( range_data ) ) ) >> >> > ) >> >> > ,* >> >> > Row(1:99)* , ";" , false ) >> >> >> >> > >> >> > >> >> > Dicoba dulu, sapa tahu ada errornya. >> >> > >> >> > Kid. >> >> > >> >> > >> >> > 2011/11/2 him mah <himmah....@gmail.com> >> >> > >> >> >> ** >> >> >> >> >> >> >> >> >> >> >> Assalamu'alaikum Wr. Wb. >> >> >> >> >> >> maaf sebelumnya belum nyari-nyari dulu di posting sebelumnya >> kebetulan >> >> >> koneksi internetnya lambat (karena yang pakai banyak) >> >> >> >> >> >> kita punya deretan angka yang tidak urut (seharusnya angka tersebut >> >> urut) >> >> >> misal di kolom A saya punya angka 1;2;5;6;9;10;11;12;15 (tanda titik >> >> >> koma sebagai pemisah baris). dari data tersebut ada beberapa angka >> >> >> yang hilang yaitu 3,4,7,8,13,14 >> >> >> >> >> >> yang jadi pertanyaan adalah bagaimana memunculkan angka-angka yang >> >> >> hilang tersebut >> >> >> >> >> >> kalau datanya sedikit sih bisa manual yang repot kalau datanya >> >> >> banyak >> >> >> >> >> >> terima kasih >> >> >> >> >> >> >> >> > >> >> >> >> >> >> >> > >> >> >> >
