Find The Latest Value From Ununique Key Record Using VBA

Pendahuluan. Bagi anda yang sudah terbiasa mengolah data menggunakan Microsoft Excel, tentu tidak asing lagi dengan fungsi “Lookup and reference” semisal VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, INDIRECT, OFFSET atau kombinasi dari fungsi-fungsi tersebut, termasuk kelemahannya. Kelemahannya? Maksudnya?, Ya, ternyata fungsi-fungsi embedded Excel tersebut masih menyisakan “kelemahan”. Diantaranya; ketika anda mencari suatu value yang melekat pada sebuah record kunci (Primary Key) yang tidak uniq misalnya (i.e karena terdapat lebih dari satu Primary Key yang similar atau sama di dalam database, red), maka hasil akhir pencarian akan kembali kepada value yang melekat pada record kunci yang pertama kali ditemukan, bukan pada record kunci yang terakhir atau yang paling up-to-date. Bagaimana jika value yang anda cari itu justru terletak pada record kunci (identik) yang paling akhir?, That’s the problem of those Function. Contoh case realnya adalah sbb;

Dari tabel diatas kita ingin mengetahui; berapa “Jumlah total Aktivasi perdana yang tercatat terakhir dalam database”. Nah untuk mencarinya, kita ambil salah satu syntax dari Lookup Function diatas, taruhlah Index dan Match. Maka formulasinya adalah sbb;

=INDEX($B$2:$B$13,MATCH(“Activation”,$A$2:$A13$,0),1)

Berapa hasilnya? 260.500. Padahal jumlah aktivasi perdana terakhir yang seharusnya terdisplay di ActiveCell adalah 161.611, bukan 260.500. Begitu pula dengan function lainnya seperti VLOOKUP(“Activation”,$A$2:$B$13,2;FALSE) atau =INDIRECT(“B”&MATCH(“Activation”;$A$3:$A$13;0)), hasilnya tetap  akan sama.

Lantas bagaimana solusinya?. (Mungkin) ada banyak cara yang bisa kita lakukan, salah satunya adalah dengan memaksimalkan fungsi Visual Basic Application (VBA) Excel atau Macros seperti yang akan saya bahas berikut ini. (Wallaahu a’lam, jika ada rekan-rekan blogger yang mau berbagi trik lain yang lebih efektif, silahkan bisa disharing di sini, red). Terlampir adalah syntaxnya;

Private Sub FindLastRecord()

With Application

    .ScreenUpdating = False

End With

Set ws = Sheets("Sheet1")

Set wa = Sheets("Sheet2")

   

    With wa

    i = .Range("B2").Value

    End With

   

    With ws

        On Error Resume Next

        Set c = .Cells.Find(What:=i, LookIn:=xlValues, lookat:=xlPart, _

        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        On Error GoTo 0

       

        If Not c Is Nothing Then

            FirstAddress = c.Address

            Do

                rg = c.Offset(0, 1).Address

                Set c = .Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)).FindNext(c)

            Loop While Not (c Is Nothing) And (c.Address <> FirstAddress)

        End If

    End With

  

   ws.Range(rg).Copy _

   Destination:=wa.Range("C2")

  

    'Useful for Releasing Memory

    Set ws = Nothing

    Set wa = Nothing

   

With Application

    .ScreenUpdating = True

End With

End Sub

Atau bisa juga dengan menggunakan syntax yang sama tapi beberapa bagian syntaxnya direplace, yakni;

Syntax sebelumnya;

Do

   rg = c.Offset(0, 1).Address

   Set c = .Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)).FindNext(c)

Loop While Not (c Is Nothing) And (c.Address <> FirstAddress)

Dimodifikasi dengan;

For n = 1 To WorksheetFunction.CountIf(.Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)), i)

    rg = c.Offset(0, 1).Address

    Set c = .Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)).FindNext(c)

Next n

Hasilnya tetap akan sama. Sebelum kita masuk pada step by step practice-nya, ijinkan saya menjelaskan terlebih dahulu makna dari syntax di atas (sebatas apa yang saya tahu, red).

Penjelasan Syntax

Pertama;

Set ws = Sheets("Sheet1")

Set wa = Sheets("Sheet2")

Untuk mempersingkat karakter syntax, maka bagian syntax yang bisa kita definisikan, kita definisikan. Contoh; ws adalah nama lain dari Sheet1 yang (di dalamnya) terdapat table Data Performansi SCN (seperti yang ditunjukkan pada gambar diatas, red), sedangkan wa adalah sheet2 yang digunakan untuk menampilkan Primary Key (i = .Range("B2").Value) sekaligus hasil pencarian (ws.Range(rg).Copy Destination:=wa.Range("C2"))

Kedua;

c = .Cells.Find(What:=i, LookIn:=xlValues, lookat:=xlPart, _

        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Syntax diatas merupakan Find Method dalam VBA. Formulasinya sbb;

expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression A variable that represents a Range object.

Parameter “What:=” itu wajib diisi (required). Apa yang harus diisikan?, “The data to search for. Can be a string or any Microsoft Excel data type” yakni data yang kita cari, dalam hal ini adalah Primary Key (dalam contoh di atas misalnya Activation). Parameter “LookIn:=” itu bersifat optional, boleh ada dan boleh tidak.  Kalau ada, maka bisa diisi dengan xlValues atau xlFormulas, tergantung referensinya berwujud apa, apakah value (angka, string, char dll) ataukah formulasi. Parameter “lookat:=” juga bersifat Optional, bisa diisi dengan xlPart atau xlWhole. Dari referensi yang saya baca, xlPart dan xlWhole ini sama dengan 0 dan 1 pada fungsi MATCH atau TRUE dan FALSE pada fungsi VLOOKUP. Pada fungsi MATCH misalnya, jika kita ingin mengetahui ada tidaknya data dalam Table yang valuenya sama persis (exact) dengan Primary Key-nya, maka match_type-nya harus diset 0, jika tidak (harus sama persis alias mendekati, red), maka cukup kita set 1. Begitupula untuk case lookat:= ini, jika kita ingin mengetahui ada tidaknya data dalam Table/ database yang valuenya sama persis (exact) dengan Primary Key, maka match_type-nya harus diset xlWhole, jika tidak maka cukup kita set xlPart. Sebagaimana paramater sebelumnya yang bersifat optional, “SearchOrder:=” juga bersifat demikian. Parameter ini bisa diisi dengan xlByRows atau xlByColumns, keduanya menunjukkan “whether to search by rows or search by columns. Default value is xlByRows”. Kurang lebih sama dengan fungsi VLOOKUP dan HLOOKUP, dimana VLOOKUP itu by Rows sedangkan HLOOKUP by Columns. Parameter “SearchDirection:=” menujukkan metode pencarian data apakah ke atas (xlPrevious) atau ke bawah (xlNext) dan sifatnya Optional. Adapun Parameter “MatchCase:=” dan “SearchFormat:=” itu terkait apakah value yang dicari itu Case-Sensitive dan Specified Formatting atau tidak, jika iya maka diisi True dan jika tidak maka cukup diisi False. Pertanyaannya, jika syntax tersebut dirunning, apa hasil yang akan dimunculkan oleh c?. Jika data yang dicari sesuai dengan requirement i.e sama persis dengan Primary Key (i), maka hasilnya adalah Primary Key itu sendiri (dalam contoh di atas adalah Activation, red).

Ketiga;

If Not c Is Nothing Then

    FirstAddress = c.Address

    Do

      rg = c.Offset(0, 1).Address

      Set c = .Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)).FindNext(c)

    Loop While Not (c Is Nothing) And (c.Address <> FirstAddress)

End If

Maksudnya, jika c itu available dalam database, maka alamat cell-nya (FirstAddress) adalah c.Address. Dalam contoh case ini misalnya, FirstAddress = $A$4 (Silahkan lihat kembali gambar di atas. Activation yang pertama kali terdeteksi pada database adalah cell $A$4, red). Adapun syntax berikutnya berfungsi untuk mencari value yang melekat pada record kunci (Primary Key) terakhir yang up-to-date. Ketika proses running (program) baru sampai pada syntax: FirstAddress = c.Address, maka status rg is Empty atau rg = “”, baru setelah melewati syntax rg = c.Offset(0, 1).Address, rg akan menghasilkan address $B$4 (masih berupa address, belum value, red). Selanjutnya untuk mencari tahu apakah masih ada Primary Key (Activation) pada record setelahnya, digunakan syntax expression .FindNext(After), expression diisi dengan Range i.e .Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)) dan After diisi dengan c yang merepresentasikan cell yang mengandung Primary Key “Activation” setelah FirstAddress. Selama c masih available di record-record setelahnya, maka proses looping akan terus dilakukan sebanyak WorksheetFunction.CountIf(.Range(FirstAddress, .Range("A" & Rows.Count).End(xlUp)), i) dan berhenti setelah c tidak lagi available (yakni di $A$10).

Keempat;

ws.Range(rg).Copy _

   Destination:=wa.Range("C2")

Sebagaimana yang sudah dijelaskan pada keterangan pertama sebelumnya, syntax ws.Range(rg).Copy Destination:=wa.Range("C2") berfungsi untuk mengcopy hasil pencarian value ($B$4) pada sheet ws ke cell C2 pada sheet wa.

Step by Step Implementasi Program

1). Buat table yang sama seperti yang dicontohkan gambar di atas (Pada Sheet1)

2). Copy syntax FindLastRecord secara lengkap, masuk ke Developer → Code → Visual Basic. Pada jendela Microsoft Visual Basic, klik Insert → Module dan pastekan syntax tersebut ke dalam Windows Code sehingga akan tampak seperti disamping;  

3). Masukkan record kunci (Primary Key) pada cell B2 di Sheet2 yakni “Activation” atau “Distribution”, atau whatever it is selama key tersebut tersedia di database.

4). Klik tombol Run pada jendela Microsoft Visual Basic dan lihat apa yang terjadi.

Penutup

Sesuatu yang terlihat biasa belum tentu (hakikatnya) juga biasa-biasa saja, bisa jadi ia menyimpan potensi yang luar biasa hanya saja kita tidak atau belum mengetahuinya. Demikian pula dengan Excel, menurut sebagian orang yang belum “mengetahui” dan belum sempat mengeksplor Tools ini lebih dalam, tampilan Excel sepintas terlihat biasa saja, fungsinya juga terlihat biasa-biasa saja (Kali, bagi, tambah, kurang). Namun bagi mereka yang telaten mau mengeksplorasi, try and error, mau belajar meskipun harus berproses (seperti para MVP Excel itu, red), Tools ini akan terasa sangat powerful dan membantu (dengan segala kelebihan dan kekurangannya tentunya). Let’s explore this tool optimally...