Kumpulan Kasus Excel Jilid 7

Jumpa lagi di gelombang yang sama, maaf ya teman2x semua lama tak posting maklum makin kesini makin tak ada ide aku ini haha.. tapi yang penting sekarang posting, horeeee, langsung yak...

KASUS 1

Dari tabel diatas kita ingin rubah ke

Rumusnya (Array Formula):
=IFERROR(IFERROR(IFERROR(IFERROR(INDEX($A$2:$C$2;MATCH(0;IF(ISBLANK($A$2:$C$2);"";COUNTIF($F$1:F1;$A$2:$C$2));0));INDEX($A$3:$C$3;MATCH(0;IF(ISBLANK($A$3:$C$3);"";COUNTIF($F$1:F1;$A$3:$C$3));0)));INDEX($A$4:$C$4;MATCH(0;IF(ISBLANK($A$4:$C$4);"";COUNTIF($F$1:F1;$A$4:$C$4));0)));INDEX($A$5:$C$5;MATCH(0;IF(ISBLANK($A$5:$C$5);"";COUNTIF($F$1:F1;$A$5:$C$5));0)));INDEX($A$6:$C$6;MATCH(0;IF(ISBLANK($A$6:$C$6);"";COUNTIF($F$1:F1;$A$6:$C$6));0)))

KASUS 2

Kasus diatas misal kita punya nomor yang di merged (digabung) di beberapa cell, kita inginnya di unmerged atau di rubah menjadi

Rumusnya:
=IFERROR(INDEX(A:A;SMALL(INDEX((A$1:INDEX(A:A;MATCH(99^99;A:A))="")*99^99+ROW(A$1:INDEX(A:A;MATCH(99^99;A:A)));0);ROWS(C$2:C2)));"")

KASUS 3

Hasil yang diinginkan

Ini kasus lookup ya, rumusnya:
=IFERROR(INDEX($B$1:$B$19;MATCH($D2;$A$1:$A$19;0)+MATCH($E$1;INDIRECT("A"&MATCH($D2;$A$1:$A$19;0)&":A19");0)-1);"")

KASUS 4

Dari tabel diatas kita ingin hasilnya seperti ini

Rumus Array:
=IFERROR(INDEX(A$2:A$12;SMALL(IF(((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12))=LARGE(IF(((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12));((COUNTIFS($D$1:$D1;$A$2:$A$12;$E$1:$E1;$B$2:$B$12)=0)*COUNTIF($B$2:$B$12;">="&$B$2:$B$12));"");1);ROW($B$2:$B$12);"");1)-ROW($A$2)+1);"")

KASUS 5

Dari dua tabel diatas kita ingin merubah lay out tabelnya menjadi

Rumus kolom F
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF($A$4:$C$6<>"";ROW($A$4:$C$6)*10^4+COLUMN($A$4:$C$6));ROWS($A$4:A4));"R0000C0000");0);"")

Rumus kolom G
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF($A$10:$C$12<>"";ROW($A$10:$C$12)*10^4+COLUMN($A$10:$C$12));ROWS($A$10:B10));"R0000C0000");0);"")

Rumus kolom I (Array Formula)
=IFERROR(INDIRECT("Kasus5!"&TEXT(SMALL(IF(($A$4:$C$12<>"")*(LEFT($A$4:$C$12;5)<>"Table");ROW($A$4:$C$12)*10^4+COLUMN($A$4:$C$12));ROWS($A$4:A4));"R0000C0000");0);"")

KASUS 6

Hasil yang diharapkan

Rumus Non Array:
=SUMPRODUCT((LOOKUP(ROW($A$3:$A$14);ROW($A$3:$A$14)/($A$3:$A$14<>"");$A$3:$A$14)=$H5)*($B$3:$B$14=I$3)*$C$3:$C$14)

Rumus Array:
=SUM((VLOOKUP(N(IF({1};ROW($A$3:$A$14)));IF({1\0};ROW($A$3:$A$14)/($A$3:$A$14<>"");$A$3:$A$14);2;1)=$H5)*($B$3:$B$14=I$3)*$C$3:$C$14)

KASUS 7

Kasus kali ini agak menarik ya yaitu mengurutkan angka dalam satu cell, rumusnya adalah rumus array atau rumus CSE:
=REPT(0;LEN(A2)-LEN(SUBSTITUTE(A2;0;"")))&SUM(10^(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2;0;""))))-1)*LARGE(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)+0;ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2;0;""))))))

KASUS 8

Kasus ke 8 ini adalah menyisipkan spasi di anatara angka, rumusnya:
=TEXT(A1;REPT("0 ";LEN(A1)))

KASUS 9

Kasus terakhir ini adalah lookup dengan mengganti teks berdasar kolom kata kunci (keyword)
Rumusnya
=IFERROR(INDEX($D$3:$D$5;LOOKUP(10^308;MATCH(TRIM(MID(SUBSTITUTE(A3;" ";REPT(" ";255));{1;2;3;4;5}*255-254;255));$D$3:$D$5;0));1);"")

KASUS 10

Kasus terkahir ini adalah cara menghilangkan angka dan menghilangkan teks
1. Rumus menghilangkan huruf dari sebuah kata (Rumus Array)
=NPV(-0,9;;IFERROR(MID(A2;1+LEN(A2)-ROW(OFFSET(A$1;;;LEN(A2)));1)%;""))
2. Rumus menghilangkan angka dari sebuah kata (Rumus Biasa)
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;""))

Unduh File Excel

Penjumlahan Dari Banyak Lembar Kerja (Summing From Multiple Sheets)

Selamat pagi rekan2x penggemar excel, hari ini ane ada kasus baru lagi buat elu2x pade, yaitu bagaimana caranya melakukan penjumlahan dari beberapa sheets sekaligus, tetapi syarat utama yang harus diperhatikan adalah data harus mempunyai lay out yang sama, kasusnya seperti ini

KASUS 1

pada gambar diatas saya mempunyai 3 lembar kerja yang saya beri nama 1, 2 dan 3, kemudian datanya seperti ini, sheet 1

Sheet 2

Sheet 3

Kemudian hasilnya yang ingin ditampilkan seperti ini

Rumus yang diwarna kuning adalah
=SUMPRODUCT(SUMIF(INDIRECT("'"&{1\2\3}&"'!A1:A10");$A2;INDIRECT("'"&{1\2\3}&"'!B1:B10")))

KASUS 2

Kasus ke 2 ini saya mempunyai 3 sheet yaitu sheet source1, source2 dan source3

Sheet source1

Sheet source2

Sheet source3

Hasil yang di harapkan

Rumus:
1. =SUM(IF(T(OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A1");;{1\2\3\4}))=B$1;SUMIF(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");$A2;OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");;{1\2\3\4}))))

2. =SUM(IF(T(OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A1");;{1\2\3\4}))=B$1;SUMIF(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");$A2;OFFSET(INDIRECT("'"&{"source1";"source2";"source3"}&"'!A:A");;{1\2\3\4})))) --> rumus array

3. =SUM(IF(T(OFFSET(INDIRECT("'"&{"source1"\"source2"\"source3"}&"'!A1");;{1;2;3;4}))=B$1;SUMIF(INDIRECT("'"&{"source1"\"source2"\"source3"}&"'!A:A");$A2;OFFSET(INDIRECT("'"&{"source1"\"source2"\"source3"}&"'!A:A");;{1;2;3;4})))) --> umus array

DOWNLOAD File Latihan
File_1
File_2

Lookup Dari Multiple Sheets

Met malem readers, kadang-kadang saya dapat ide untuk artikel blog ini dari pembaca yang menanyakan kasusnya, nah kali ini artikel ini ide dari Bapak Dady Lucky, terima kasih pak sudah memberikan inspirasi malam ini dan kebetulan saya belum pernah posting artikel seperti ini. Sesuai judul artikel bagaimana sih melakukan Lookup dari beberapa Sheets? Langsung ya ke tkp.....................

1. Mengambil hasil dari beberapa tab sheets (One Results)

Dan kunci keberhasilan lookup dari beberapa tabel dari sheet yang berbeda, anda harus punya tabel dengan lay out yang sama walaupun datanya beda2x, jika tidak tidak akan berhasil. Dan saya punya beberapa tab sheets disini

Ada 5 sheets ya dan kemudian hasil yang diinginkan

Dan rumusya adalah, letakkan pada sheet "Hasil" pada cell B5 kemudian copas ke bawah dan ke samping:
=IFERROR(VLOOKUP($B$2;INDIRECT("'"&INDEX({1\2\3\4\5};AGGREGATE(15;6;1/(1/{1\2\3\4\5}*(COUNTIFS(INDIRECT("'"&{1\2\3\4\5}&"'!A2:A100");$B$2)=1));ROW(A1)))&"'!$A$2:$A$100");1;0);"")


2. Mengambil hasil dari beberapa tab sheets (Multiple Results)

Dan saya punya 3 sheet saja disini

Hasil yang diharapkan

Nah rumusnya (rumus array) :
=IF(ROWS($1:1)>SUMPRODUCT(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1));"";INDEX(INDIRECT("'"&INDEX({"A"\"B"\"C"};MATCH(TRUE;MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1)))>=ROWS($1:1);0))&"'!A2:B10");SMALL(IF(INDIRECT("'"&INDEX({"A"\"B"\"C"};MATCH(TRUE;MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1)))>=ROWS($1:1);0))&"'!B2:B10")=$B$1;ROW(INDIRECT("1:"&ROWS($A$2:$B$10))));IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1);1+MMULT(0+(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))>=TRANSPOSE(ROW(INDIRECT("1:"&COUNTA({"A"\"B"\"C"})))));TRANSPOSE(COUNTIF(INDIRECT("'"&{"A"\"B"\"C"}&"'!B2:B10");$B$1))));ROWS($1:1)));COLUMNS($A:A)))

Have good night and sleep tight readers......:)

Download :
File_1
File_2

Kumpulan Kasus Excel Jilid 6

Kumpulan Kasus back to fight guys, malam ini melanjutkan seri yang ke 6 dan mudah2xan dapat digunakan untuk keperluan para readers, langsung saja ke urutan pertama

KASUS 1

Kasus pertama ini adalah menggabungkan kolom, 2 kolom atau lebih ya

Rumus:
a. =IFERROR(IF(OFFSET(A$2;MOD(ROWS(A$2:A2)-1;COUNTA(A:A)-1);INT((ROWS(A$2:A2)-1)/ROWS($A$2:$B$10)))=0;"";OFFSET(A$2;MOD(ROWS(A$2:A2)-1;COUNTA(A:A)-1);INT((ROWS(A$2:A2)-1)/ROWS($A$2:$B$10))));"")

b. =IFERROR(INDEX($A$2:$B$10;MOD(ROWS($A$2:A2)-1;ROWS($A$2:$B$10))+1;INT((ROWS($A$2:A2)-1)/ROWS($A$2:$B$10))+1);"")

KASUS 2

Kasus ke 2 ini adalah kasus penjumlahan dengan kolom kriteria yang di-merged, hasilnya

Rumus:
a. =SUM(MMULT(N(LOOKUP(ROW($A$1:$A$49);IF($A$1:$A$49<>"";ROW($A$1:$A$49)))=TRANSPOSE(IF($A$1:$A$49=D4;ROW($A$1:$A$49))));ROW($A$1:$A$49)^0)*$B$1:$B$49)

b. =SUM(IFERROR(IF(MATCH(LOOKUP(ROW(D);IF(D<>"";ROW(D)));IF(D=D4;ROW(D));0);V);0)) --> array formula

c. =SUM(ISNUMBER(MATCH(LOOKUP(ROW(D);IF(D<>"";ROW(D)));IF(D=D4;ROW(D));0))*V) --> array formula

d. =SUM(IF(LOOKUP(ROW(D);ROW(D)/(D<>"");D)=D4;V))

KASUS 3

Kasus ini merupakan kasus lookup dengan beberapa kolom, hasilnya

Rumus:
=IFERROR(SUMIF(INDEX($B$2:$F$8;MATCH(B10;$A$2:$A$8;0);0);A10;INDEX($C$2:$G$8;MATCH(B10;$A$2:$A$8;0);0));"")

KASUS 4

Kasus ke 4 ini adalah inspirasi dari mas Hendra jadi saya share untuk artikel kali ini tentang Golongan dan Kepangkatan PNS, untuk hasilnya adalah yang diwarna kuning ya, dan ini merupakan tabel panduannya

Rumusnya
=IFERROR(IF(RIGHT($D3;1)="E";$D3;IF(AND(LOOKUP(LEFT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));FIND("/";INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)-1);{"I";"II";"III";"IV"};{1;2;3;4})=LOOKUP(LEFT(D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});RIGHT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)<RIGHT($D3;1));$D3;IF(AND(LOOKUP(LEFT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));FIND("/";INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)-1);{"I";"II";"III";"IV"};{1;2;3;4})=LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});RIGHT(INDEX($K$4:$K$10;MATCH($F3;$J$4:$J$10;0));1)=RIGHT($D3;1));$D3;IF(RIGHT($D3;1)="D";LOOKUP(LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4})+1;{1;2;3;4};{"I";"II";"III";"IV"})&"/"&"A";LOOKUP(LOOKUP(LEFT($D3;FIND("/";$D3;1)-1);{"I";"II";"III";"IV"};{1;2;3;4});{1;2;3;4};{"I";"II";"III";"IV"})&"/"&CHAR(CODE(RIGHT($D3;1))+1)))));"")

KASUS 5

Hasil yang diharapkan

Rumus:
a. =INDEX(AGGREGATE({14;15};6;$B$2:$E$5/(($G2=$A$2:$A$5)*ISNUMBER($B$2:$E$5));1);COLUMNS(A:$A))

b. =IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$5;MATCH($G2;$A$2:$A$5;0);0)=$H2;COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COUNTIFS($G$2:G2;G2;$H$2:H2;H2)));"")

KASUS 6

Kasus terakhir ini merupakan kasus split teks dengan kriteria, pada tabel diatas saya berikan contoh menggunakan angka supaya hasilnya lebih jelas walaupun jika diganti teks bisa saja, hasil

Rumus di cell A8:
=LEFT($A$1;FIND("@";SUBSTITUTE($A$1;" ";"@";$A$6))-1)

Rumus di cell A9 dan copas ke bawah
=IFERROR(MID($A$1;FIND("@";SUBSTITUTE($A$1;" ";"@";$A$6*ROWS($1:1)))+1;MMULT(FIND("@";SUBSTITUTE($A$1;" ";"@";TRANSPOSE(MMULT(ROW($A1:$A2);1))*$A$6));{-1;1})-1);"")


FILE LATIHAN EXCEL

Kumpulan Kasus Excel Jilid 5

Hello guys and girls, long time no post ha..ha.. , mohon maaf saya akhir2x ini suibuk bgt, belum bisa sempat buat bahan kasus untuk artikel di blog ini, lanjut ya ke TKP..................

KASUS 1

Hasilnya berupa konversi ke table bentuk yang lain dengan menyisipkan baris kosong

Rumus untuk "Hasil 1":
1. Produk : =IF(ISERROR(INDEX($A$3:$A$5;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)));"";INDEX($A$3:$A$5;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)))

2. "Jual", "Beli", "Profit" : =IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";INDEX($B$2:$D$2;CEILING(ROWS(A$1:A1)/(SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1);1)))

3. Value : =IF(ISERROR(INDEX($B$3:$D$5;MATCH($A8;$A$3:$A$5;0);MATCH($B8;$B$2:$D$2;0)));"";INDEX($B$3:$D$5;MATCH($A8;$A$3:$A$5;0);MATCH($B8;$B$2:$D$2;0)))

Rumus untuk "Hasil 2" :
1. Produk : =IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";INDEX($A$3:$A$5;INT((ROWS($1:1)-1)/4)+1))

2. "Jual", "Beli", "Profit" : =IF(ISERROR(INDEX($B$2:$D$2;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)));"";INDEX($B$2:$D$2;IF(MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1=4;"";MOD(ROW($A1)-1;SUMPRODUCT(($B$2:$D$2=$B$2)*($B$3:$D$5<>""))+1)+1)))

3. Value : =IFERROR(INDEX($B$3:$D$5;MATCH($E8;$A$3:$A$5;0);MATCH($F8;$B$2:$D$2;0));"")

KASUS 2

Kemudian di-konversi menjadi :

Rumus (letakkan di cell C2 kemudian copied down and cross) :
=IF(($A1=0)*($A2<>"");IFERROR(INDEX($A2:INDEX($A2:$A$100;MATCH(1;INDEX(0+($A2:$A$100="");0);0));COLUMNS($A:A))&"";"");"")

 KASUS 3

Kasus ke 3 ini mirip dengan yang diatas yaitu meng-konversi tabel ke bentuk tabel yang lain (beda lay out)

Hasil

Rumus untuk kolom KOTA :
1. Pada cell A7, rumusnya =A2
2. Pada cell A8 dan copy down, =IFERROR(IF(COUNTIF(A$7:A7;A7)<COLUMNS($B$2:$D$5);A7;INDEX($A$2:$A$5;MATCH(A7;$A$2:$A$5;0)+1)); "")

Rumus untuk kolom TANGGAL :
=IF(A7 = ""; ""; INDEX($B$1:$D$1;MOD((ROW(A1)-1);COLUMNS($B$1:$D$1)) + 1))

Rumus untuk kolom ANGKA :
=IF(A7=""; "";INDEX($B$2:$D$5;MATCH(A7;$A$2:$A$5;0);COUNTIF(A$7:A7; A7)))

KASUS 4

Kasus ke 4 ini berbeda ya, disini saya beri contoh  bagaimana caranya membuat nomor dan data dengan sisipan baris kosong pada nomor baris2x tertentu.

Rumus:
1. Untuk Nomor : =IF(OR(ROWS(A1:$A$1)={5\11\32\38\45\77\100\110\132\144\166\172\199\207\217\240\262\272\295\312\318\343\350\356\370\392\403\421\429\444\461\478\480});"";MAX(A$1:A1)+1)
2. Untuk Area : =IF(OR(ROWS(A1:$A$1)={5\11\32\38\45\77\100\110\132\144\166\172\199\207\217\240\262\272\295\312\318\343\350\356\370\392\403\421\429\444\461\478\480});"";"Area"&COUNTIF(B$1:B1;""))


KASUS 5

Ini kasus merubah lay out lagi ya, menjadi........

Rumus:
=IFERROR(INDIRECT(TEXT(AGGREGATE(15;6;ROW($2:$10)/1%+{1\2}/(COUNTIF(D$2:D2;A$2:B$10)=0);1);"R0C00");0);"")

KASUS 6

Kasus 6 ini membuat rumus untuk menggabungkan beberapa cell sekaligus, rumusnya :
=SUBSTITUTE(TRIM((CONCATENATE(A1;" ";B1;" ";C1;" ";D1;" ";E1;" ";F1)));" ";", ")

KASUS 7

Kasus ke 7 ini adalah mencocokkan antara kata pada kolom A dengan kolom B (secara parsial), bila ada ada kesamaan maka muncul "Yes", jika tidak "No", rumusnya (rusmus array) :
=IF(OR(A2="";B2="");"";IF(OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A2;" ";REPT(" ";99));1+99*(ROW(OFFSET($A$1;;;1+LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))))-1);99));B2)));"Yes";IF(OR(A2="";B2="");"";IF(OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(B2;" ";REPT(" ";99));1+99*(ROW(OFFSET($B$1;;;1+LEN(B2)-LEN(SUBSTITUTE(B2;" ";""))))-1);99));A2)));"Yes";"No"))))

KASUS 8

Kasus ke 8 ini bagaimana caranya memisahkan data yang menjadi satu pada contoh di atas, hasil yang diinginkan

Rumus (letakkan di C2 kemudian copas ke bawah):
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(" "&$A$3;" ";REPT(" ";255));255*ROWS($A$1:A1);255));",";"")

KASUS 9
Pada kasus 9 ini saya akan memberikan contoh rumus penomoran pada 2 kolom dan 3 kolom, sperti ini misalnya

Rumus pada kolom A :
=COUNTA($B$2:B2)

Rumus pada kolom C :
=COUNTA(D$2:D2)+MAX($A$2:$A$6)

Rumus pada kolom A :
=COUNTA($B$10:B10)

Rumus pada kolom C :
=COUNTA(D$10:D10)+MAX($A$10:$A$14)

Rumus pada kolom E :
=COUNTA(F$10:F10)+MAX($C$10:$C$14)

KASUS 10

Hasil yang ingin ditampilkan :

Rumus pada kolom G :
=LOOKUP(ROWS(A$1:A1);SUMIF(OFFSET(A$3:C$3;;;ROW($1:$4););"<>")+1;D$4:D$6)&""

Rumus pada kolom H :
=IF(G3="";"";LOOKUP(COUNTIF(G$3:G3;G3)-1;IFERROR(SUMIF(OFFSET(INDEX(A$4:A$6;MATCH(G3;D$4:D$6;0));;;;ROW($1:$3)-1);"<>");0);T(OFFSET(A$3;0;{0;1;2;0;1;2;0;1;2}))&""))

Rumus pada kolom I :
=LOOKUP(ROWS(A$1:A1);SUMIF(OFFSET(A$3:C$3;;;ROW($1:$4););"<>")+1;E$4:E$6)&""

KASUS 11

Dari tabel diatas dirubah menjadi :

Rumus (rumus array) :
=TRIM(IFERROR(INDEX($A$2:$C$13;(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+2;MOD(ROW(1:1)-1;3)+1)&"-"&INDEX($A$2:$C$13;(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+3;MOD(ROW(1:1)-1;3)+1)&"-"&INDEX($A$2:$C$13;(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+4;MOD(ROW(1:1)-1;3)+1);""))

LINK FILE KASUS