Kadang WP memberikan data softcopy GL dalam bentuk text file yang tidak beraturan, misalnya geser ke kanan/kiri, atau bahkan turun ke bawah beberapa row, hal ini bisanya disebabkan software GL yang tidak sempurna
Misalnya coba kita lihat contoh GL Text report yang tidak teratur sbb :
Disini kita liat ada ketidak beraturan posisi debet dan kredit yang akan menyusahkan kita dalam mengubahnya menjadi excel.
Kita akan berusaha convert dengan metode sbb :
- Untuk mengambil data debit/kredit kita ambil saja 2 kolom debet dan kredit secara bersama menggunakan fixed width lalu kita pisahin menjadi 2 kolom yaitu debet & Kredit menggunakan delimited (spasi)
- Untuk kolom debet & Kredit yang geser turun 1 baris akan kita gunakan fungsi untuk mengambil data dibawahnya jika atasnya kosong
Langsung aja kita On Action
- Kita buka dulu file txt menggunakan Notepad++/ultraedit/editor lainnya
- Kita analisa bahwa kolom debet & kredit saling berhubung sehingga tidak bisa dipotong menggunakan fixed width
- Jadi sebaiknya kolom debet & kredit kita potong bersama dikolom 83 baru kita pecah menggunakan delimiter spasi
- Kita buka dulu Excel dan Bikin New File
- Klik ribbon Data ->From Text
- pilih filenya lalu pilih fixed width lalu Next
- Potong2 sesuai keinginan yang penting kolom debet & Kredit kita potong di kolom 83
- Klik Next
- Pilih sebagai Text aja pada semua kolom, kecuali kolom debet kredit lalu finish
- Ini hasilnya
- Kolom yang turun 1 baris
- Kita isi dulu kolom debet & Kredit yang turun 1 baris
- Kita buat Kolom debit kredit baru dikolom E
- Kita buat rumusnya dikolom E, misalnya dibaris 10
- Rumus ini akan melihat jika baris tersebut merupakan detail transaksi (ditandai dengan tanggal) serta kolom debet kreditnya kosong maka ambil kolom dibawahnya 1 baris
- =IF(RIGHT(A10,3)="/10",IF(D10<>"",D10,D11))
- ini hasilnya
- Jika udah maka kita perlu bikin value rumus diatas, gunakan copy ->Paste values dikolom F (karena rumusnya biar gak ilang, lagian text to column kalo ke formula/rumus maka yang dipotong2 adalah rumusnya bukan hasilnya)
- Serta buat buat safety jika gagal
- Kita gunakan ribbon Data -> Text to Column (untuk motong kolom debit kredit)
- Pilih delimited
- Pilih space
- ini hasilnya
- Rupanya ada Text yang ikut keambil sehingga pemisahan kacau
- pusing juga
- karena angka (rata kanan) maka emang seharusnya diambil dari kanan
- Cuman nggak ada fungsi untuk itu
- Kita bisa pakai VBA atau formula
- Sebaiknya pakai formula saja agar lebih gampang memahaminya
- Kita akan gunakan fungsi Choose dan dikombinasikan dengan Fungsi text Trim serta Substitute
- Intinya kita kan sudah potong2 kolom F menggunakan delimiter Spasi
- Lalu Kita akan memilih kolom terakhir (sebagai kredit) dan Kolom Sebelumnya (Sebagai Debet)
- Kita hanya perlu mencari tahu ada berapa kolom hasil pemotongan
- Misalnya text "197,665,175.00 0.00" akan menjadi 2 kolom
- sedang text "TALS 705,381,062.00 0.00" akan jadi 3 kolom
- sedang text "ME TALS 705,381,062.00 0.00" akan jadi 4 kolom
- Kita cari kolom terluar yaitu kolom I (pastikan merupakan kolom terakhir)
- Kita buat formula untuk mencari jumlah kata
- Misalnya dibaris 5605
- =LEN(TRIM(E5605))-LEN(SUBSTITUTE(E5605," ",""))
- Akan menghasilkan 2 (berarti 3 kolom)
- Bikin di kolom J (debet), formula untuk ambil kolom sebelum terakhir
- =CHOOSE(I5605,F5605,G5605,H5605)
- Bikin dikolom K (kredit), formula untuk ambil kolom terakhir
- =CHOOSE(I5605+1,F5605,G5605,H5605)
- Tinggal menambahkan header Account No dan Account name
- Kita buat rumus pada kolom L untuk AccNo
- Misalnya pada sel L7 kita ketik
- =IF(AND(LEN(A7)=5,ISNUMBER(VALUE(A7))),A7,L6)
- Rumus diatas akan mengecek apakah pada kolom A berisi 5 digit angka
- Pada Kolom M kita bikin rumus untuk AccName
- =IF(AND(LEN(A7)=5,ISNUMBER(VALUE(A7))),B7&C7&D7,M6)
- Jangan lupa untuk menambahkan NoUrut supaya bisa dirunut ke dokumen sumber, buat aja dikolom N
- Gunakan Fill series atau ketik angka 1 di kolom N1 lalu seret fill Handle sambil pencet tombol Control
- Atau gunakan rumus : =row()
- Kalau memakai rumus diatas harap diubah jadi value dengan Copy Paste Values
- Jika sudah tinggal difilter dikolom A, dengan filter End With lalu diisi "/10"
- Ini Hasilnya
- hasilnya tidak balanced karena memang hanya sebagian GL yang diambil
- Hasil akhir bisa didonlot disini