Deteksi dan eksekusi semua jenis penggandaan dengan COUNTIF

Salam Excel,

Jika pada tutorial sebelumnya mendeteksi value ganda menggunakan formula FREQUENCY maka pada tutorial ini menggunakan formula umum yaitu COUNTIF. Namun menjadi tidak umum jika ditulis menjadi rumus panjang atau yang dikenal mega formula seperti ini:

=IF(LEN(REPLACE(IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")&IF(COUNTIF(F11:T11,F11)>1,", "&F11,"")&IF(COUNTIF(H11:T11,H11)>1,", "&H11,"")&IF(COUNTIF(J11:T11,J11)>1,", "&J11,"")&IF(COUNTIF(L11:T11,L11)>1,", "&L11,"")&IF(COUNTIF(N11:T11,N11)>1,", "&N11,"")&IF(COUNTIF(P11:T11,P11)>1,", "&P11,"")&IF(COUNTIF(R11:T11,R11)>1,", "&R11,""),1,2,""))=0,"nihil",REPLACE(IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")&IF(COUNTIF(F11:T11,F11)>1,", "&F11,"")&IF(COUNTIF(H11:T11,H11)>1,", "&H11,"")&IF(COUNTIF(J11:T11,J11)>1,", "&J11,"")&IF(COUNTIF(L11:T11,L11)>1,", "&L11,"")&IF(COUNTIF(N11:T11,N11)>1,", "&N11,"")&IF(COUNTIF(P11:T11,P11)>1,", "&P11,"")&IF(COUNTIF(R11:T11,R11)>1,", "&R11,""),1,2,""))

Tutorial ini adalah solusi alternative dari solusi tutorial xxx dimana formula bertujuan untuk mendeteksi kode ganda pada sebuah penjadwalan. Formula pada tutorial Menandai Kode tersebut ditulis seperti dibawah:

=IF(FREQUENCY(D11:T11,D11:T11)>1,D11,IF(FREQUENCY(F11:T11,F11:T11)>1,F11,IF(FREQUENCY(H11:T11,H11:T11)>1,H11,IF(FREQUENCY(J11:T11,J11:T11)>1,J11,IF(FREQUENCY(L11:T11,L11:T11)>1,L11,IF(FREQUENCY(N11:T11,N11:T11)>1,N11,  IF(FREQUENCY(P11:T11,P11:T11)>1,P11,IF(FREQUENCY(R11:T11,R11:T11)>1,R11,"nihil"))))))))

Formula ini hanya menulis satu kode pertama ganda yang terdeteksi lebih awal dan jika tidak ada kode ganda maka akan ditulis kata “nihil”. Kode yang ganda yang lebih dari dua tidak akan ter-display sebelum kode ganda yang ditampilkan di-edit menjadi data single atau tunggal atau unik. Pada gambar dibawah pada baris 15 kode hanya ditulis 34 sementara 45 belum muncul.

Gambar 1.1

Pada gambar berikutnya ketika 34 diedit menjadi kode yang unik di baris tersebut maka pada kolom resume menuliskan kode ganda berikutnya. Dan seterusnya berlaku untuk kode ganda berikutnya.

Gambar 1.2

Kekuatan mega formula alternative pada tutorial kali ini ada pada point 2 dibawah yaitu eksekusi data ganda yang telah dideteksi oleh rumus utama. Eksekusi disini berupa penulisan semua kode ganda. Formula ini bertujuan untuk memenuhi requirement sbb:

1. Mendeteksi dan menulis kode ganda (kode utama)
2. Menulis kode ganda lebih dari satu dan tanda koma sebagai pemisah
3. Menulis “nihil” jika tidak ada kode ganda

Gambar 1.3

Cobalah ikuti tahapan-tahapan hasil dari meditasi saya dibawah:

Pada file terlampir, pilih sheet meditasi dan mulai tulis

U11: =COUNTIF(D11:T11,D11) … tekan enter

Hasilnya U11: 1

Ulangi kembali

U11: =IF(COUNTIF(D11:T11,D11)>1,D11,"")

Hasilnya U11: 34

Berikutnya untuk deteksi kode ganda untuk 28 pada cell F11, tuliskan

U11: =IF(COUNTIF(D11:T11,D11)>1,D11,"")&IF(COUNTIF(D11:T11,F11)>1,F11,"")

Hasilnya U11: 3428

Fungsi tanda “&” adalah penggabung hasil dua formula.

Sukses untuk meditasi tahap pertama sudah anda lakukan sekaligus menjawab requirement pertama diatas.

Selanjutnya meditasi kedua menambahkan tanda koma untuk pemisah. Modifikasi formula diatas menjadi

U11: =IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")& IF(COUNTIF(D11:T11,F11)>1,", "&F11,"")

Hasilnya U11:  , 34, 28

Abaikan koma didepan 34, tuliskan lebih panjang lagi dan copas ke cell dibawahnya

U11:

=IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")&IF(COUNTIF(F11:T11,F11)>1,", "&F11,"")&IF(COUNTIF(H11:T11,H11)>1,", "&H11,"")&IF(COUNTIF(J11:T11,J11)>1,", "&J11,"")&IF(COUNTIF(L11:T11,L11)>1,", "&L11,"")&IF(COUNTIF(N11:T11,N11)>1,", "&N11,"")&IF(COUNTIF(P11:T11,P11)>1,", "&P11,"")&IF(COUNTIF(R11:T11,R11)>1,", "&R11,"")

Hasil pada beberapa cell sbb:

U11:  , 34, 28

U12:  , 50

U13:  kosong

U14:  , 14, 21, 14, 21, 21

U15:  , 34, 45

Pada menulis sekali kode yang sama lebih dari 2 kali maka tulis ulang rumus dengan mengganti “>1” menjadi “=2”

U11:

=IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,"")

Kemudian copas kebawah. Hasilnya sbb:

U14:  , 14, 21

Meditasi kedua selesai

Selanjutnya adalah menghapus satu koma pada awal hasil, lengkapi formula dengan REPLACE

Bagaimana REPLACE bekerja? Cek fungsi pada menu Help Ms. Excel anda … disana dibeberkan fungsi dan contoh soal penggunaan formula tersebut. Tulis kembali

U11:

=REPLACE(IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,""),1,2,"")

Jika disederhanakan:

=REPLACE(formula meditasi kedua,1,2,"")

Pada gambar dibawah diterangkan

Yaa, menghapus oleh formula REPLACE teknisnya adalah mengganti koma dan spasi dengan tanpa value atau blank yang diwakili penulisannya dengan tanda petik dua diatas sebanyak dua buah tanpa spasi. Satu spasi dihitung satu karakter oleh excel maka jumlah 2 karakter yang digantikan blank diatas adalah menghapus koma dan satu spasi.

Meditasi ketiga DONE

Requirement terakhir adalah info nihil untuk baris tanpa ada kode ganda. Kembali tulis formula

U11:

=IF(LEN(REPLACE(IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,""),1,2,""))=0,"nihil",REPLACE(IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,""),1,2,""))

Jangan panik! Formula diatas jika disederhanakan menjadi

=IF(LEN(formula meditasi ketiga)=0,"nihil", formula meditasi ketiga))

Hasil pada beberapa cell sbb:

U11:  34, 28

U12:  50

U13:  nihil

U14:  14, 21

U15:  34, 45

Meditasi berakhir. Formula yang buat anda tak bisa tidur telah hadir dari jari anda.

Pada tahap akhir ini anda telah membuktikan pada dunia … bahwa anda telah berhasil menulis mega formula. BRAVOOO

Mudah kan?

Semoga bermanfaat

Salam COUNTIF

Klinikexcel