@alfri

terlampir contohnya, silahkan diskusikan lagi jika tidak sesuai

Mohon izinnya mas @manweljs_ ...

Terlampir contoh utak-atik dari saya buat mas @alfri. Saya pakai kolom bantu.. Kalau masih banyak problemnya, silahkan didiskusikan sama mas @manweljs_ ... :D :D :D

Demikian.

@Alfri ...

Mungkin ada beberapa formula yang agak mudah dinarasikan, namun ada juga yang sulit. Saya coba sebisa saya. Untuk formula:

=IF(LEN([@A]);VLOOKUP([@A];db.Return;2;0);"")

jika dinarasikan:

Jika ada nilai pada sel [@A], maka lakukan pencarian nilai pada sel [@A] tersebut pada tabel db.Return. Jika nilai pada sel [@A] kosong, maka kembalikan nilai kosong.

Catatan:
Notasi [@A] digunakan untuk merujuk ke kolom dengan header A pada baris yang sama (Cell Reference), sedangkan notasi [A] digunakan untuk merujuk ke kolom dengan header A (Range Reference). Jadi notasi [@A] bukan kolom A pada Worksheet, namun header kolom dari objek tabel (header di bawah sel KODE ITEM). Gambarannya:

Yang lainnya nyusul ... :D

@alfri ...

Dicoba dahulu file terlampir mas, semoga sesuai revisinya...

@Alfri ...

Untuk formula:

=IF(LEN([@G]);INDEX(db.Sales[F];SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0)));"")

jika dinarasikan:

Jika ada nilai pada sel [@G], maka lakukan pencarian pada range data db.Sales kolom [F], berdasarkan kriteria pada sel [@G] dan sel [@H]. Jika nilai pada sel [@G] kosong, maka kembalikan nilai kosong.

Formula tersebut pada intinya untuk mendapatkan data NOMOR FAKTUR PAJAK berdasarkan KODE ITEM dan TANGGAL (kedua kriteria tersebut diambil dari kolom bantu yakni sel [@G] dan sel [@H]). Mengapa harus dengan 2 kriteria? Hal ini disebabkan oleh karena syaratnya data harus tersusun berdasarkan tanggal terakhir dahulu untuk setiap kode item yang sama. Contohnya, untuk kode item BR500101, tanggal terakhir adalah 30/01/2018 dan nomor fakturnya adalah 010.002-14.03274321.

Masalahnya, pada tabel data db.Sales, terdapat duplikasi data untuk kolom KODE dan kolom TANGGAL. Jika mencari data berdasarkan salah satu kolom, kemungkinan akan dihasilkan nilai yang tidak sesuai. Untuk mengatasi hal tersebut, salah satu caranya adalah dengan membuat kode unik — misalkan dengan menggabungkan nilai dari kedua kolom. Meski tidak sepenuhnya efektif, namun melihat data yang ada, cara tersebut dapat digunakan.

Meski fungsi MATCH mendukung tabel array, namun fungsi ini tidak mendukung array hasil penggabungan 2 kolom. Agar dapat berfungsi dengan array dari 2 kolom tersebut, maka fungsi MATCH harus bekerja sebagai Array Formula. Untuk membuatnya menjadi Array Formula, kita dapat menekan kombinasi tombol CONTROL SHIFT ENTER (CSE), atau menggunakan fungsi SUMPRODUCT.

Sebagai contoh, formula pada sel D6 akan mencari nilai dari tabel db.Sales berdasarkan nilai pada sel H6 dan I6. Indeks baris dari data ditentukan oleh bagian formula:

=SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0))

Pada formula tersebut, masing-masing parameter akan menghasilkan nilai:

[@G]&[@H]
= "BR500101"&43130
= "BR50010143130"

db.Sales[A] =
{"BR500101";"BR500101";"BR500101";"BR500101";"BR500102";"BR500102";"BR500102";"BR500103";"BR500104";"BR500105";"BR500105";"BR500105"}

db.Sales[B] =
{42370;42461;42896;43130;42370;42461;42896;42370;42370;42370;42461;42896}

db.Sales[A]&db.Sales[B] = 
{"BR50010142370";"BR50010142461";"BR50010142896";"BR50010143130";"BR50010242370";"BR50010242461";"BR50010242896";"BR50010342370";"BR50010442370";"BR50010542370";"BR50010542461";"BR50010542896"}

Dari nilai-nilai tersebut, dapat diketahui nilai 010.002-14.03274321 berada pada indeks data ke 4. Sehingga, jika indeks baris tersebut dimasukkan ke fungsi INDEX pada formula awal maka akan terurai menjadi:

= INDEX(db.Sales[F];SUMPRODUCT(MATCH([@G]&[@H];db.Sales[A]&db.Sales[B];0)))
= INDEX(db.Sales[F];4;0)))
= "010.002-14.03274321"

Yang lain menyusul jika sempat... :) Demikian.

@alfri ...

Untuk formula di sel E6:

=IF(LEN([@G]);IF(LEN([@A])*(COUNTIF(db.Sales[A];[@A])>1);IF(LEN([@A])*([@I]>[@B]);[@B];[@I]);IF(SUMIF($H$5:$H6;[@G];$J$5:$J6)>VLOOKUP([@G];db.Return;2;0);[@I]-(SUMIF($H$5:$H6;[@G];$J$5:$J6)-VLOOKUP([@G];db.Return;2;0));[@I]));IF(LEN($H5)*(ROW(A1)<>1)*(COUNTIF($H$5:$H5;$H5)>1);SUMIF($H$5:$H5;$H5;$E$5:$E5);""))

agak repot menjelaskannya. Jadi saya coba buat bloknya dahulu seperti berikut:

IF (LEN([@G]);
   IF (LEN([@A])*(COUNTIF(db.Sales[A];[@A])>1);
      IF (LEN([@A])*([@I]>[@B]);
         [@B];
         [@I]
      );
      IF (SUMIF($H$5:$H6;[@G];$J$5:$J6)>VLOOKUP([@G];db.Return;2;0);
         [@I]-(SUMIF($H$5:$H6;[@G];$J$5:$J6)-VLOOKUP([@G];db.Return;2;0));
         [@I]
      )
   );
   IF(LEN($H5)*(ROW(A1)<>1)*(COUNTIF($H$5:$H5;$H5)>1);
      SUMIF($H$5:$H5;$H5;$E$5:$E5);
      ""
   )
)

Blok formula tersebut jika diuraikan ke dalam bentuk narasi, ± menjadi:

[C1] : (H6 > 0) ?
[T1] :
   [C2] : (B6 > 0) dan ((banyaknya data B6 pada db.Sales) > 1) ?
   [T2] :
      [C3] : (B6 > 0) dan (J6 > C6) ?
         [T3] : ambil nilai C6.
         [F3] : ambil nilai J6.
   [F2] :
      [C4] : (jumlah nilai range $J$5:$J6 untuk item = H6) > (jumlah item returnya) ?
         [T4] : J6 - ((jumlah nilai range $J$5:$J6 untuk item = H6) - (jumlah item returnya)).
         [F4] : J6.
[F1] :
   [C5] : (H5 > 0) dan (indeks baris <> 1) dan ((banyaknya data H5 pada range H5:H5) > 1) ?
      [T5] : jumlahkan nilai range H5:H5 untuk item = E5.
      [F5] : kosongkan.

Catatan:
[C-n] : Kondisi yang akan diperiksa.
[T-n] : Blok yang akan dikerjakan jika kondisi dari C-n bernilai TRUE.
[F-n] : Blok yang akan dikerjakan jika kondisi dari C-n bernilai FALSE.

Untuk notasi LEN[@G] saya ubah menjadi (H6 > 0). Notasi LEN[@G] pada dasarnya untuk memeriksa apakah pada sel H6 ada nilainya atau tidak. Karena jika sel H6 tidak ada isinya (kosong), maka notasi LEN[@G] akan menghasilkan nilai 0 (nol) yang oleh Excel diartikan sebagai FALSE.

Blok di atas jika dibaca, kira-kira:

Jika kondisi C1 bernilai TRUE, maka kerjakan blok T1 dan abaikan blok F1. Namun jika kondisi C1 benilai FALSE, maka kerjakan blok F1 dan abaikan blok T1. Demikian pula dengan yang lainnya!

Untuk pertanyaan mengenai formula lainnya, coba disusun seperti yang saya jelaskan di atas. Kemudian coba pahami blok-blok formulanya. Saya kira demikian. Mudah-mudahan bisa dipahami.

@Caton
...Untuk formula di sel E6: ...

yang di H6 belum mas, yang ada lookup nya :

=IF(LEN([@A])*(COUNTIF($B$5:$B6,[@A])=1),[@A],IF(LEN($H5)*(COUNTIF($H$5:$H5,$H5)<COUNTIF(db.Sales[A],$H5))*(SUMIF($H$5:$H5,$H5,$J$5:$J5)<=INDEX($C$6:$C6,LOOKUP(2,1/($B$6:$B6<>""),ROW($A$1:$A1)))),$H5,""))

;)

@manweljs_ ...

... yang di H6 belum mas, yang ada lookup nya ...

Untuk sel H6 (dan juga sel-sel lainnya), saya yakin mas @manweljs_ dengan sangat mudah memahami formulanya... :P :D

Tapi okelah, saya jelaskan formulanya untuk mas @alfri ... Nilai pada kolom H ini merupakan kunci awal untuk formula pada sel-sel lainnya. Sebagai contoh, saya ambil formula pada sel H12:

=IF(LEN([@A])*(COUNTIF($B$5:$B12;[@A])=1);[@A];IF(LEN($H11)*(COUNTIF($H$5:$H11;$H11)<COUNTIF(db.Sales[A];$H11))*(SUMIF($H$5:$H11;$H11;$J$5:$J11)<=INDEX($C$6:$C12;LOOKUP(2;1/($B$6:$B12<>"");ROW($A$1:$A7))));$H11;""))

— A. Pertama-tama, periksa apakah sel B12 (KODE ITEM) ada nilai atau isinya. Dan jika sel B12 ada nilainya, periksa apakah nilai atau kode item tersebut sudah ada pada range $B$5:$B12 (tujuannya untuk mencegah kode item yang sama tidak diproses lebih dari sekali). Jika kedua kondisi terpenuhi (TRUE, yakni kode itemnya ada dan kode item tersebut baru muncul pertama kali), maka ambil nilai atau kode item dari sel B12 tersebut! Formulanya:

=IF(LEN([@A])*(COUNTIF($B$5:$B12;[@A])=1);[@A]; ...

— B. Jika kondisi tersebut di atas tidak terpenuhi, maka untuk blok FALSE ini akan dilakukan proses pemeriksaan terhadap beberapa kondisi yang harus terpenuhi:

[1] Apakah sel di atas (H11) ada isinya (kode itemnya). Formulanya:

LEN($H11)

[2] Apakah banyaknya data yang sudah diproses sebelumnya sesuai kriteria kode item pada sel H11 pada tabel db.Report (range $H$5:$H11) lebih kecil dari banyaknya data dengan kriteria yang sama tabel db.Sale (kolom [A]). Tujuannya agar kode item yang akan diproses tidak melebihi jumlah data sebenarnya. Formulanya:

(COUNTIF($H$5:$H11;$H11)<COUNTIF(db.Sales[A];$H11))

[3] Apakah jumlah barang (pada kolom I) retur dengan kode item sesuai dengan nilai pada sel H11 yang sudah diproses di tabel db.Report (range $H$5:$H11) lebih kecil atau sama dengan jumlah barang retur pada db. Return. Jika benar, artinya masih ada data lainnya yang akan diproses. Formulanya:

(SUMIF($H$5:$H11;$H11;$J$5:$J11)<=INDEX($C$6:$C12;LOOKUP(2;1/($B$6:$B12<>"");ROW($A$1:$A7))))

Pada formula di atas, notasi LOOKUP(2;1/($B$6:$B12<>"");ROW($A$1:$A7)) berguna untuk mendapatkan baris terakhir dari range $B$6:$B12 yang ada nilainya. Oleh karena sel B12 berisi kode item, maka indeks baris yang diambil dari notasi ROW($A$1:$A7) adalah 7. Maka nilai yang dihasilkan fungsi INDEX tersebut untuk data pada range $H$5:$H11 baris ke-7 adalah 45. Selain dengan formula tersebut, alternatifnya adalah dengan formula:

(SUMIF($H$5:$H11;$H11;$J$5:$J11)<=IFERROR(INDEX(db.Return[B];MATCH(IF(LEN([@A]);[@A];$H11);db.Return[A];0));0))

Jika ketiga kondisi tersebut terpenuhi, maka formula akan menghasilkan nilai yang sama dengan nilai pada sel sebelumnya yakni H11. Namun jika kondisi tersebut juga tidak terpenuhi, maka formula akan mengembalikan nilai kosong.

IF(LEN($H11)*(COUNTIF($H$5:$H11;$H11)<COUNTIF(db.Sales[A];$H11))*(SUMIF($H$5:$H11;$H11;$J$5:$J11)<=IFERROR(INDEX(db.Return[B];MATCH(IF(LEN([@A]);[@A];$H11);db.Return[A];0));0));$H11;"")

Demikian yang bisa saya jelaskan untuk formula kolom bantu yakni kolom [G] . Yang lain tinggal dipelajari saja ya mas @alfri. Jika masih kurang paham, bisa dikonsultasikan ke mas @manweljs_. Beliau pakarnya untuk formula-formula yang kompleks dan rumit... :P :D :D :D

Mengenai yang mas @alfri tanyakan,

... misalnya di tabel report yang ada itu, di kolom A, sudah terdapat drop down list, nah misalnya drop down list nya dihilangkan gmana yah ? jadinya saya hanya tinggal input di tabel db.return masukin kode dan qty nanti secara otomatis data akan masuk di tabel report di kolom A & B ...

Terlampir contoh yang dapat saya berikan. Perlu diperhatikan, jika saat menambah baris tabel db.Report terjadi kesalahan seperti:

sehingga timbul semacam ini (bisa juga tidak muncul):

maka coba blok seluruh range data tabel db.Report (pastikan seluruh range, termasuk range kolom Helper — unhide dahulu kolom Helper):

lalu tekan tombol CONTROL+D 1 atau 2 kali untuk menyalin formula dari baris pertama. Jangan tanya saya mengapa terjadi saya juga tidak tahu... ;)

Demikian, semoga sesuai dan bermanfaat.

Ada yang tertinggal sedikit:

Terlampir contoh yang dapat saya berikan. Perlu diperhatikan, jika saat menambah baris tabel db.Report terjadi kesalahan seperti:

sehingga timbul semacam ini (bisa juga tidak muncul):

dan seterusnya... Demikian dengan ini kesalahan kami perbaiki... :D

Untuk sel H6 (dan juga sel-sel lainnya)... dst...

ternyata benar point mas @Caton cuma hasil chit-chat aja :P :D :D ;)

edit - doble post

... ternyata benar point mas @Caton cuma hasil chit-chat aja :P :D :D ;)

Wkwkwkwk... serasa mau menambah point lagi jadinya ... :D :D :D Mas @alfri mana nih, kok gak hadir-hadir... (^_^)/ Kasih komen dong mas @alfri ... :D

@alfri ...

Apakah harus dengan 2 kunci pencarian? Aktualnya tidak. Pada tabel sheet RESULT, kolom H memang menjadi kunci utama untuk digunakan pada tabel tersebut. Dengan mendapatkan kunci tersebut, maka kita sudah menemukan jalan untuk mendapatkan posisi data yang dituju. Kalau dinarasikan:

Untuk setiap KODE ITEM yang sesuai dengan yang ingin dicari, ambil indeks baris datanya.

Kalau sebelumnya saya menggunakan kombinasi KODE ITEM dan TANGGAL sebagai kunci, adalah kurang tepat karena sebenarnya data langsung dapat diambil melalui indeks barisnya. Hanya saja, dalam hal ini TANGGAL pada tabel utama harus tersusun secara Ascending agar datanya sesuai (meski dapat diperbaikin dengan formula tambahan).

Terlampir contoh solusinya. Silahkan dipelajari...