Dear Pak Ghozi, untuk formula nya masih eror, di colom F, hasil nya angka di kolom F berulang masih menggunakan angka E5:Y5 (sheet contoh) seharusnya ketika berganti "source" hasil F akan berganti ke row sesuai "source"
terima kasih On Fri, Feb 3, 2017 at 5:33 PM, ghozi alkatiri [email protected] [belajar-excel] <[email protected]> wrote: > > > 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 > > >
