sorry yang benar formula di A3 =INDEX(Contoh!B$5:B$15;TRUNC((ROW(1:1)-1)/(COUNTA(Contoh!$E$3:$XFD$3))+1)) copy ke B3 dan C3 copy ke bawah.
wassalam Ghozi Alkatiri -------------------------------------------- Pada Jum, 3/2/17, Modby Matsnawabi [email protected] [belajar-excel] <[email protected]> menulis: Judul: Re: [belajar-excel] Rumus Transpose Otomatis [1 Attachment] Kepada: "belajar-excel" <[email protected]> Tanggal: Jumat, 3 Februari, 2017, 4:18 PM Terima kasih Pak, saya sudah coba pake formula tersebut ke data saya, tapi kenapa, ketika berganti "souce name" maka bulan nya akan di mulai dari bulan yang akhir "Mar" sehingga data double data (file terlampir) On Fri, Feb 3, 2017 at 3:13 PM, Modby Matsnawabi [email protected] [belajar-excel] <[email protected]> wrote: Terima kasih Pak Ghozi for quick respon & solusinya. mau tanya untuk rumus ini maksudnya apa yah? COUNTA(Contoh!$E$3:$XFD$3))+1) Kenapa pake XFD3? padahal nga ada cell kesana? On Fri, Feb 3, 2017 at 1:03 PM, ghozi alkatiri [email protected] [belajar-excel] <[email protected] > wrote: di cell A2 =INDEX(Contoh!B$5:B$15;TRUNC(R OW(1:1)/(COUNTA(Contoh!$E$3:$X FD$3)-1))+1) copy sampai C2 copy ke bawah sampai yang diinginkan di cell D2 =OFFSET(Contoh!$D$2;COLUMN(A:A );MOD(ROW(1:1)-1;COUNTA(Contoh !$E$3:$XFD$3))+1) copy sampai F2 copy ke bawah sampai yang diinginkan selanjutnya lihat di attachment sheet solusi wassalam Ghozi Alkatiri ------------------------------ -------------- Pada Kam, 2/2/17, Modby Matsnawabi [email protected] [belajar-excel] <[email protected] > menulis: Judul: [belajar-excel] Rumus Transpose Otomatis [1 Attachment] Kepada: "belajar-excel" <[email protected] > Tanggal: Kamis, 2 Februari, 2017, 5:09 PM Selamat Sore, Para expert Excel, saya ada case mau transpose 500 Row data yang memiliki 150 colom data. klo row nya cuma sedikit masih sangup manual, tapi ini 500 lumayan gempor..contoh case saya lampirkan. Untuk member lain yang sudah pernah sharing dan sudah ada pemecahannya tolong di share solusinya. Terima kasih, salam.. #yiv6900962383 #yiv6900962383 -- #yiv6900962383ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;marg in:10px 0;padding:0 10px;} #yiv6900962383 #yiv6900962383ygrp-mkp hr { border:1px solid #d8d8d8;} #yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383hd { color:#628c2a;font-size:85%;fo nt-weight:700;line-height:122% ;margin:10px 0;} #yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383ads { margin-bottom:10px;} #yiv6900962383 #yiv6900962383ygrp-mkp .yiv6900962383ad { padding:0 0;} #yiv6900962383 #yiv6900962383ygrp-mkp .yiv6900962383ad p { margin:0;} #yiv6900962383 #yiv6900962383ygrp-mkp .yiv6900962383ad a { color:#0000ff;text-decoration: none;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ygrp-lc { font-family:Arial;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ygrp-lc #yiv6900962383hd { margin:10px 0px;font-weight:700;font-size: 78%;line-height:122%;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ygrp-lc .yiv6900962383ad { margin-bottom:10px;padding:0 0;} #yiv6900962383 #yiv6900962383actions { font-family:Verdana;font-size: 11px;padding:10px 0;} #yiv6900962383 #yiv6900962383activity { background-color:#e0ecee;float :left;font-family:Verdana; font-size:10px;padding:10px;} #yiv6900962383 #yiv6900962383activity span { font-weight:700;} #yiv6900962383 #yiv6900962383activity span:first-child { text-transform:uppercase;} #yiv6900962383 #yiv6900962383activity span a { color:#5085b6;text-decoration: none;} #yiv6900962383 #yiv6900962383activity span span { color:#ff7900;} #yiv6900962383 #yiv6900962383activity span .yiv6900962383underline { text-decoration:underline;} #yiv6900962383 .yiv6900962383attach { clear:both;display:table;font- family:Arial;font-size:12px;pa dding:10px 0;width:400px;} #yiv6900962383 .yiv6900962383attach div a { text-decoration:none;} #yiv6900962383 .yiv6900962383attach img { border:none;padding-right:5px; } #yiv6900962383 .yiv6900962383attach label { display:block;margin-bottom:5p x;} #yiv6900962383 .yiv6900962383attach label a { text-decoration:none;} #yiv6900962383 blockquote { margin:0 0 0 4px;} #yiv6900962383 .yiv6900962383bold { font-family:Arial;font-size:13 px;font-weight:700;} #yiv6900962383 .yiv6900962383bold a { text-decoration:none;} #yiv6900962383 dd.yiv6900962383last p a { font-family:Verdana;font-weigh t:700;} #yiv6900962383 dd.yiv6900962383last p span { margin-right:10px;font-family: Verdana;font-weight:700;} #yiv6900962383 dd.yiv6900962383last p span.yiv6900962383yshortcuts { margin-right:0;} #yiv6900962383 div.yiv6900962383attach-table div div a { text-decoration:none;} #yiv6900962383 div.yiv6900962383attach-table { width:400px;} #yiv6900962383 div.yiv6900962383file-title a, #yiv6900962383 div.yiv6900962383file-title a:active, #yiv6900962383 div.yiv6900962383file-title a:hover, #yiv6900962383 div.yiv6900962383file-title a:visited { text-decoration:none;} #yiv6900962383 div.yiv6900962383photo-title a, #yiv6900962383 div.yiv6900962383photo-title a:active, #yiv6900962383 div.yiv6900962383photo-title a:hover, #yiv6900962383 div.yiv6900962383photo-title a:visited { text-decoration:none;} #yiv6900962383 div#yiv6900962383ygrp-mlmsg #yiv6900962383ygrp-msg p a span.yiv6900962383yshortcuts { font-family:Verdana;font-size: 10px;font-weight:normal;} #yiv6900962383 .yiv6900962383green { color:#628c2a;} #yiv6900962383 .yiv6900962383MsoNormal { margin:0 0 0 0;} #yiv6900962383 o { font-size:0;} #yiv6900962383 #yiv6900962383photos div { float:left;width:72px;} #yiv6900962383 #yiv6900962383photos div div { border:1px solid #666666;height:62px;overflow:h idden;width:62px;} #yiv6900962383 #yiv6900962383photos div label { color:#666666;font-size:10px;o verflow:hidden;text-align:cent er;white-space:nowrap;width: 64px;} #yiv6900962383 #yiv6900962383reco-category { font-size:77%;} #yiv6900962383 #yiv6900962383reco-desc { font-size:77%;} #yiv6900962383 .yiv6900962383replbq { margin:4px;} #yiv6900962383 #yiv6900962383ygrp-actbar div a:first-child { margin-right:2px;padding-right :5px;} #yiv6900962383 #yiv6900962383ygrp-mlmsg { font-size:13px;font-family:Ari al, helvetica, clean, sans-serif;} #yiv6900962383 #yiv6900962383ygrp-mlmsg table { font-size:inherit;font:100%;} #yiv6900962383 #yiv6900962383ygrp-mlmsg select, #yiv6900962383 input, #yiv6900962383 textarea { font:99% Arial, Helvetica, clean, sans-serif;} #yiv6900962383 #yiv6900962383ygrp-mlmsg pre, #yiv6900962383 code { font:115% monospace;} #yiv6900962383 #yiv6900962383ygrp-mlmsg * { line-height:1.22em;} #yiv6900962383 #yiv6900962383ygrp-mlmsg #yiv6900962383logo { padding-bottom:10px;} #yiv6900962383 #yiv6900962383ygrp-msg p a { font-family:Verdana;} #yiv6900962383 #yiv6900962383ygrp-msg p#yiv6900962383attach-count span { color:#1E66AE;font-weight:700; } #yiv6900962383 #yiv6900962383ygrp-reco #yiv6900962383reco-head { color:#ff7900;font-weight:700; } #yiv6900962383 #yiv6900962383ygrp-reco { margin-bottom:20px;padding:0px ;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ov li a { font-size:130%;text-decoration :none;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ov li { font-size:77%;list-style-type: square;padding:6px 0;} #yiv6900962383 #yiv6900962383ygrp-sponsor #yiv6900962383ov ul { margin:0;padding:0 0 0 8px;} #yiv6900962383 #yiv6900962383ygrp-text { font-family:Georgia;} #yiv6900962383 #yiv6900962383ygrp-text p { margin:0 0 1em 0;} #yiv6900962383 #yiv6900962383ygrp-text tt { font-size:120%;} #yiv6900962383 #yiv6900962383ygrp-vital ul li:last-child { border-right:none !important; } #yiv6900962383 #yiv9372165695 #yiv9372165695 -- #yiv9372165695ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;} #yiv9372165695 #yiv9372165695ygrp-mkp hr { border:1px solid #d8d8d8;} #yiv9372165695 #yiv9372165695ygrp-mkp #yiv9372165695hd { color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;} #yiv9372165695 #yiv9372165695ygrp-mkp #yiv9372165695ads { margin-bottom:10px;} #yiv9372165695 #yiv9372165695ygrp-mkp .yiv9372165695ad { padding:0 0;} #yiv9372165695 #yiv9372165695ygrp-mkp .yiv9372165695ad p { margin:0;} #yiv9372165695 #yiv9372165695ygrp-mkp .yiv9372165695ad a { color:#0000ff;text-decoration:none;} #yiv9372165695 #yiv9372165695ygrp-sponsor #yiv9372165695ygrp-lc { font-family:Arial;} #yiv9372165695 #yiv9372165695ygrp-sponsor #yiv9372165695ygrp-lc #yiv9372165695hd { margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;} #yiv9372165695 #yiv9372165695ygrp-sponsor #yiv9372165695ygrp-lc .yiv9372165695ad { margin-bottom:10px;padding:0 0;} #yiv9372165695 #yiv9372165695actions { font-family:Verdana;font-size:11px;padding:10px 0;} #yiv9372165695 #yiv9372165695activity { background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;} #yiv9372165695 #yiv9372165695activity span { font-weight:700;} #yiv9372165695 #yiv9372165695activity span:first-child { text-transform:uppercase;} #yiv9372165695 #yiv9372165695activity span a { color:#5085b6;text-decoration:none;} #yiv9372165695 #yiv9372165695activity span span { color:#ff7900;} #yiv9372165695 #yiv9372165695activity span .yiv9372165695underline { text-decoration:underline;} #yiv9372165695 .yiv9372165695attach { clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;} #yiv9372165695 .yiv9372165695attach div a { text-decoration:none;} #yiv9372165695 .yiv9372165695attach img { border:none;padding-right:5px;} #yiv9372165695 .yiv9372165695attach label { display:block;margin-bottom:5px;} #yiv9372165695 .yiv9372165695attach label a { text-decoration:none;} #yiv9372165695 blockquote { margin:0 0 0 4px;} #yiv9372165695 .yiv9372165695bold { font-family:Arial;font-size:13px;font-weight:700;} #yiv9372165695 .yiv9372165695bold a { text-decoration:none;} #yiv9372165695 dd.yiv9372165695last p a { font-family:Verdana;font-weight:700;} #yiv9372165695 dd.yiv9372165695last p span { margin-right:10px;font-family:Verdana;font-weight:700;} #yiv9372165695 dd.yiv9372165695last p span.yiv9372165695yshortcuts { margin-right:0;} #yiv9372165695 div.yiv9372165695attach-table div div a { text-decoration:none;} #yiv9372165695 div.yiv9372165695attach-table { width:400px;} #yiv9372165695 div.yiv9372165695file-title a, #yiv9372165695 div.yiv9372165695file-title a:active, #yiv9372165695 div.yiv9372165695file-title a:hover, #yiv9372165695 div.yiv9372165695file-title a:visited { text-decoration:none;} #yiv9372165695 div.yiv9372165695photo-title a, #yiv9372165695 div.yiv9372165695photo-title a:active, #yiv9372165695 div.yiv9372165695photo-title a:hover, #yiv9372165695 div.yiv9372165695photo-title a:visited { text-decoration:none;} #yiv9372165695 div#yiv9372165695ygrp-mlmsg #yiv9372165695ygrp-msg p a span.yiv9372165695yshortcuts { font-family:Verdana;font-size:10px;font-weight:normal;} #yiv9372165695 .yiv9372165695green { color:#628c2a;} #yiv9372165695 .yiv9372165695MsoNormal { margin:0 0 0 0;} #yiv9372165695 o { font-size:0;} #yiv9372165695 #yiv9372165695photos div { float:left;width:72px;} #yiv9372165695 #yiv9372165695photos div div { border:1px solid #666666;height:62px;overflow:hidden;width:62px;} #yiv9372165695 #yiv9372165695photos div label { color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;} #yiv9372165695 #yiv9372165695reco-category { font-size:77%;} #yiv9372165695 #yiv9372165695reco-desc { font-size:77%;} #yiv9372165695 .yiv9372165695replbq { margin:4px;} #yiv9372165695 #yiv9372165695ygrp-actbar div a:first-child { margin-right:2px;padding-right:5px;} #yiv9372165695 #yiv9372165695ygrp-mlmsg { font-size:13px;font-family:Arial, helvetica, clean, sans-serif;} #yiv9372165695 #yiv9372165695ygrp-mlmsg table { font-size:inherit;font:100%;} #yiv9372165695 #yiv9372165695ygrp-mlmsg select, #yiv9372165695 input, #yiv9372165695 textarea { font:99% Arial, Helvetica, clean, sans-serif;} #yiv9372165695 #yiv9372165695ygrp-mlmsg pre, #yiv9372165695 code { font:115% monospace;} #yiv9372165695 #yiv9372165695ygrp-mlmsg * { line-height:1.22em;} #yiv9372165695 #yiv9372165695ygrp-mlmsg #yiv9372165695logo { padding-bottom:10px;} #yiv9372165695 #yiv9372165695ygrp-msg p a { font-family:Verdana;} #yiv9372165695 #yiv9372165695ygrp-msg p#yiv9372165695attach-count span { color:#1E66AE;font-weight:700;} #yiv9372165695 #yiv9372165695ygrp-reco #yiv9372165695reco-head { color:#ff7900;font-weight:700;} #yiv9372165695 #yiv9372165695ygrp-reco { margin-bottom:20px;padding:0px;} #yiv9372165695 #yiv9372165695ygrp-sponsor #yiv9372165695ov li a { font-size:130%;text-decoration:none;} #yiv9372165695 #yiv9372165695ygrp-sponsor #yiv9372165695ov li { font-size:77%;list-style-type:square;padding:6px 0;} #yiv9372165695 #yiv9372165695ygrp-sponsor #yiv9372165695ov ul { margin:0;padding:0 0 0 8px;} #yiv9372165695 #yiv9372165695ygrp-text { font-family:Georgia;} #yiv9372165695 #yiv9372165695ygrp-text p { margin:0 0 1em 0;} #yiv9372165695 #yiv9372165695ygrp-text tt { font-size:120%;} #yiv9372165695 #yiv9372165695ygrp-vital ul li:last-child { border-right:none !important; } #yiv9372165695
