Cara Convert GL Text Report yang tidak teratur

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

Download file GL Ruwet.txt

Misalnya coba kita lihat contoh GL Text report yang tidak teratur sbb :

GL kacau01

 

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 :

  1. 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)
  2. 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

  1. Kita buka dulu file txt menggunakan Notepad++/ultraedit/editor lainnya
  2. Kita analisa bahwa kolom debet & kredit saling berhubung sehingga tidak bisa dipotong menggunakan fixed width
  3. GL kacau02
  4. Jadi sebaiknya kolom debet & kredit kita potong bersama dikolom 83 baru kita pecah menggunakan delimiter spasi
  5. Kita buka dulu Excel dan Bikin New File
  6. Klik ribbon Data ->From Text
  7. pilih filenya lalu pilih fixed width lalu Next
  8. Potong2 sesuai keinginan yang penting kolom debet & Kredit kita potong di kolom 83
  9. GL kacau03
  10. Klik Next
  11. Pilih sebagai Text aja pada semua kolom, kecuali kolom debet kredit lalu finish
  12. Ini hasilnya
  13. GL kacau04a
  14. Kolom yang turun 1 baris
  15. Kita isi dulu kolom debet & Kredit yang turun 1 baris
  16. Kita buat Kolom debit kredit baru dikolom E
  17. Kita buat rumusnya dikolom E, misalnya dibaris 10
  18. Rumus ini akan melihat jika baris tersebut merupakan detail transaksi (ditandai dengan tanggal) serta kolom debet kreditnya kosong maka ambil kolom dibawahnya 1 baris
  19. =IF(RIGHT(A10,3)="/10",IF(D10<>"",D10,D11))
  20. ini hasilnya
  21. GL kacau05
  22. 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)
  23. Serta buat buat safety jika gagal
  24. Kita gunakan ribbon Data -> Text to Column (untuk motong kolom debit kredit)
  25. Pilih delimited
  26. Pilih space
  27. ini hasilnya
  28. GL kacau07
  29. Rupanya ada Text yang ikut keambil sehingga pemisahan kacau
  30. pusing juga
  31. karena angka (rata kanan) maka emang seharusnya diambil dari kanan
  32. Cuman nggak ada fungsi untuk itu
  33. Kita bisa pakai VBA atau formula
  34. Sebaiknya pakai formula saja agar lebih gampang memahaminya
  35. Kita akan gunakan fungsi Choose dan dikombinasikan dengan Fungsi text Trim serta Substitute
  36. Intinya kita kan sudah potong2 kolom F menggunakan delimiter Spasi
  37. Lalu Kita akan memilih kolom terakhir (sebagai kredit) dan Kolom Sebelumnya (Sebagai Debet)
  38. Kita hanya perlu mencari tahu ada berapa kolom hasil pemotongan
  39. Misalnya text "197,665,175.00              0.00" akan menjadi 2 kolom
  40. sedang text "TALS             705,381,062.00              0.00" akan jadi 3 kolom
  41. sedang text "ME TALS      705,381,062.00              0.00" akan jadi 4 kolom
  42. Kita cari kolom terluar yaitu kolom I (pastikan merupakan kolom terakhir)
  43. Kita buat formula untuk mencari jumlah kata
  44. Misalnya dibaris 5605
  45. =LEN(TRIM(E5605))-LEN(SUBSTITUTE(E5605," ",""))
  46. Akan menghasilkan 2 (berarti  3 kolom)
  47. Bikin di kolom J (debet), formula untuk ambil kolom sebelum terakhir
  48. =CHOOSE(I5605,F5605,G5605,H5605)
  49. Bikin dikolom K (kredit), formula untuk ambil kolom terakhir
  50. =CHOOSE(I5605+1,F5605,G5605,H5605)
  51. Tinggal menambahkan header Account No dan Account name
  52. Kita buat rumus pada kolom L untuk AccNo
  53. Misalnya pada sel L7 kita ketik
  54. =IF(AND(LEN(A7)=5,ISNUMBER(VALUE(A7))),A7,L6)
  55. Rumus diatas akan mengecek apakah pada kolom A berisi 5 digit angka
  56. Pada Kolom M kita bikin rumus untuk AccName
  57. =IF(AND(LEN(A7)=5,ISNUMBER(VALUE(A7))),B7&C7&D7,M6)
  58. Jangan lupa untuk menambahkan NoUrut supaya bisa dirunut ke dokumen sumber, buat aja dikolom N
  59. Gunakan Fill series atau ketik angka 1 di kolom N1 lalu seret fill Handle sambil pencet tombol Control
  60. Atau gunakan rumus : =row()
  61. Kalau memakai rumus diatas harap diubah jadi value dengan Copy Paste Values
  62. Jika sudah tinggal difilter dikolom A, dengan filter End With lalu diisi "/10"
  63. Ini Hasilnya
  64. GL kacau08
  65. hasilnya tidak balanced karena memang hanya sebagian GL yang diambil
  66. Hasil akhir bisa didonlot disini

 

 

Bookmark the permalink.

Leave a Reply