di cell A2
=INDEX(Contoh!B$5:B$15;TRUNC(ROW(1:1)/(COUNTA(Contoh!$E$3:$XFD$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 modbym@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

Judul: [belajar-excel] Rumus Transpose Otomatis [1 Attachment]
Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
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;margin:10px
0;padding:0 10px;}

#yiv6900962383 #yiv6900962383ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383hd {
color:#628c2a;font-size:85%;font-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;padding: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:5px;}

#yiv6900962383 .yiv6900962383attach label a {
text-decoration:none;}

#yiv6900962383 blockquote {
margin:0 0 0 4px;}

#yiv6900962383 .yiv6900962383bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv6900962383 .yiv6900962383bold a {
text-decoration:none;}

#yiv6900962383 dd.yiv6900962383last p a {
font-family:Verdana;font-weight: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:hidden;width:62px;}

#yiv6900962383 #yiv6900962383photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;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:Arial, 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

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1b

Re: Rumus Transpose Otomatis [1 Attachment]

Fri Feb 3, 2017 12:13 am (PST) . Posted by:

"Modby Matsnawabi"

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 ghozialkatiri@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> di cell A2
> =INDEX(Contoh!B$5:B$15;TRUNC(ROW(1:1)/(COUNTA(Contoh!$E$3:$XFD$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 modbym@gmail.com [belajar-excel] <
> belajar-excel@yahoogroups.com> menulis:
>
> Judul: [belajar-excel] Rumus Transpose Otomatis [1 Attachment]
> Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
> 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;margin:10px
> 0;padding:0 10px;}
>
> #yiv6900962383 #yiv6900962383ygrp-mkp hr {
> border:1px solid #d8d8d8;}
>
> #yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383hd {
> color:#628c2a;font-size:85%;font-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;padding: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:5px;}
>
> #yiv6900962383 .yiv6900962383attach label a {
> text-decoration:none;}
>
> #yiv6900962383 blockquote {
> margin:0 0 0 4px;}
>
> #yiv6900962383 .yiv6900962383bold {
> font-family:Arial;font-size:13px;font-weight:700;}
>
> #yiv6900962383 .yiv6900962383bold a {
> text-decoration:none;}
>
> #yiv6900962383 dd.yiv6900962383last p a {
> font-family:Verdana;font-weight: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:hidden;width:62px;}
>
> #yiv6900962383 #yiv6900962383photos div label {
> color:#666666;font-size:10px;overflow:hidden;text-align:
> center;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:Arial, 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
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1c

Re: Rumus Transpose Otomatis

Fri Feb 3, 2017 1:18 am (PST) . Posted by:

"Modby Matsnawabi"

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 modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com> 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 ghozialkatiri@yahoo.co.id
> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>>
>>
>> di cell A2
>> =INDEX(Contoh!B$5:B$15;TRUNC(ROW(1:1)/(COUNTA(Contoh!$E$3:$XFD$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 modbym@gmail.com [belajar-excel] <
>> belajar-excel@yahoogroups.com> menulis:
>>
>> Judul: [belajar-excel] Rumus Transpose Otomatis [1 Attachment]
>> Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
>> 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;margin:10px
>> 0;padding:0 10px;}
>>
>> #yiv6900962383 #yiv6900962383ygrp-mkp hr {
>> border:1px solid #d8d8d8;}
>>
>> #yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383hd {
>> color:#628c2a;font-size:85%;font-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;padding: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:5px;}
>>
>> #yiv6900962383 .yiv6900962383attach label a {
>> text-decoration:none;}
>>
>> #yiv6900962383 blockquote {
>> margin:0 0 0 4px;}
>>
>> #yiv6900962383 .yiv6900962383bold {
>> font-family:Arial;font-size:13px;font-weight:700;}
>>
>> #yiv6900962383 .yiv6900962383bold a {
>> text-decoration:none;}
>>
>> #yiv6900962383 dd.yiv6900962383last p a {
>> font-family:Verdana;font-weight: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:hidden;width:62px;}
>>
>> #yiv6900962383 #yiv6900962383photos div label {
>> color:#666666;font-size:10px;overflow: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:Arial, 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
>>
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1d

Re: Rumus Transpose Otomatis [1 Attachment]

Fri Feb 3, 2017 2:17 am (PST) . Posted by:

"Sony Ario"

Halo Modby,

Saya coba bantu pake Macro ya ?
Maaf kalau belum sesuai harapan.

Salam
Sony

*gnl-excelinfo.blogspot.co.id <http://gnl-excelinfo.blogspot.co.id>*

2017-02-02 17:09 GMT+07:00 Modby Matsnawabi modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> 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..
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1e

Re: Rumus Transpose Otomatis [1 Attachment]

Fri Feb 3, 2017 2:33 am (PST) . Posted by:

"ghozi alkatiri" ghozialkatiri

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 modbym@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

Judul: Re: [belajar-excel] Rumus Transpose Otomatis [1 Attachment]
Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
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 modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>
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 ghozialkatiri@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com
> 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 modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com
> menulis:

Judul: [belajar-excel] Rumus Transpose Otomatis [1
Attachment]

Kepada: "belajar-excel" <belajar-excel@yahoogroups.com
>

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

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1f

Re: Rumus Transpose Otomatis [1 Attachment]

Fri Feb 3, 2017 3:11 am (PST) . Posted by:

"Modby Matsnawabi"

Terima kasih pak Sony,

saya belum paham untuk macro..
jika saya pergunakan macro tersebut ke data saya, apakah cukup copy paste
query macro nya saja tanpa ada perubahan(hanya perubahan nama sheet)?
bagaimana jika ada tambahan colom dan row? apakah otomatis macro akan
jalan? saya sudah coba copy paste macro namun error ketika di run, dan jika
saya copy paste data ke file sample excel ada error di activecell

2017-02-03 17:11 GMT+07:00 Sony Ario sony.ario1@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Halo Modby,
>
> Saya coba bantu pake Macro ya ?
> Maaf kalau belum sesuai harapan.
>
>
> Salam
> Sony
>
> *gnl-excelinfo.blogspot.co.id <http://gnl-excelinfo.blogspot.co.id>*
>
> 2017-02-02 17:09 GMT+07:00 Modby Matsnawabi modbym@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> 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..
>>
>>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1g

Re: Rumus Transpose Otomatis

Fri Feb 3, 2017 3:20 am (PST) . Posted by:

"Modby Matsnawabi"

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 ghozialkatiri@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> 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 modbym@gmail.com [belajar-excel] <
> belajar-excel@yahoogroups.com> menulis:
>
> Judul: Re: [belajar-excel] Rumus Transpose Otomatis [1 Attachment]
> Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
> 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 modbym@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>
> 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 ghozialkatiri@yahoo.co.id
> [belajar-excel] <belajar-excel@yahoogroups.com
> > 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 modbym@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com
> > menulis:
>
>
>
> Judul: [belajar-excel] Rumus Transpose Otomatis [1
> Attachment]
>
> Kepada: "belajar-excel" <belajar-excel@yahoogroups.com
> >
>
> 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
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1h

Re: Rumus Transpose Otomatis [1 Attachment]

Fri Feb 3, 2017 11:00 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Mats,

File terlampir menggunakan kolom bantu dan nama range.

Regards,
Kid

2017-02-02 17:09 GMT+07:00 Modby Matsnawabi modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> 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..
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1i

Re: Rumus Transpose Otomatis

Fri Feb 3, 2017 1:24 pm (PST) . Posted by:

"Sony Ario"

Halo Modby,

Bisa tolong di capture error nya ?

Macro yang saya buat tetap harus ada penyesuaian. Terutama di nama Sheet
nya. Walaupun penambahan baris atau kolom baru tetap masih bisa di
antisipasi. Apakah format data real nya sama, terutama 4 kolom pertama ?

Atau di coba data real nya di copy paste ke Sheet Contoh dan coba jalankan
Macro nya, liat hasilnya di Sheet Hasil2 itu ?

*gnl-excelinfo.blogspot.co.id <http://gnl-excelinfo.blogspot.co.id>*

2017-02-03 18:11 GMT+07:00 Modby Matsnawabi modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Terima kasih pak Sony,
>
> saya belum paham untuk macro..
> jika saya pergunakan macro tersebut ke data saya, apakah cukup copy paste
> query macro nya saja tanpa ada perubahan(hanya perubahan nama sheet)?
> bagaimana jika ada tambahan colom dan row? apakah otomatis macro akan
> jalan? saya sudah coba copy paste macro namun error ketika di run, dan jika
> saya copy paste data ke file sample excel ada error di activecell
>
> 2017-02-03 17:11 GMT+07:00 Sony Ario sony.ario1@gmail.com [belajar-excel]
> <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Halo Modby,
>>
>> Saya coba bantu pake Macro ya ?
>> Maaf kalau belum sesuai harapan.
>>
>>
>> Salam
>> Sony
>>
>> *gnl-excelinfo.blogspot.co.id <http://gnl-excelinfo.blogspot.co.id>*
>>
>> 2017-02-02 17:09 GMT+07:00 Modby Matsnawabi modbym@gmail.com
>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>
>>>
>>>
>>> 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..
>>>
>>>
>>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

2a

Re: Rumus Transpose Otomatis

Fri Feb 3, 2017 12:58 am (PST) . Posted by:

"ghozi alkatiri" ghozialkatiri

XFD adalah kolom terakhir pada excel.
jadi ini adalah upaya antisipasi supaya formula tersebut tetap bisa berfungsi dinamis sesuai dengan penambahan data yang akan terjadi .
adapun counta adalah untuk menghitung jumlah sel berisi data baik angka ataupun karakter pada range yang ingin dihitung jumlah datanya.
lain lain bisa ditanyakan lebih lanjut ,insya Allah kalau memang bisa dan sempat dengan senang hati saya akan membantu.

wassalam

Ghozi Alkatiri
--------------------------------------------
Pada Jum, 3/2/17, Modby Matsnawabi modbym@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

Judul: Re: [belajar-excel] Rumus Transpose Otomatis
Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
Tanggal: Jumat, 3 Februari, 2017, 3:13 PM

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 ghozialkatiri@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com>
wrote:

di cell A2

=INDEX(Contoh!B$5:B$15;TRUNC( ROW(1:1)/(COUNTA(Contoh!$E$3:$
XFD$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 modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com
> menulis:

Judul: [belajar-excel] Rumus Transpose Otomatis [1
Attachment]

Kepada: "belajar-excel" <belajar-excel@yahoogroups.com
>

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; margin:10px

0;padding:0 10px;}

#yiv6900962383 #yiv6900962383ygrp-mkp hr {

border:1px solid #d8d8d8;}

#yiv6900962383 #yiv6900962383ygrp-mkp #yiv6900962383hd {

color:#628c2a;font-size:85%; font-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;
padding: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: 5px;}

#yiv6900962383 .yiv6900962383attach label a {

text-decoration:none;}

#yiv6900962383 blockquote {

margin:0 0 0 4px;}

#yiv6900962383 .yiv6900962383bold {

font-family:Arial;font-size: 13px;font-weight:700;}

#yiv6900962383 .yiv6900962383bold a {

text-decoration:none;}

#yiv6900962383 dd.yiv6900962383last p a {

font-family:Verdana;font- weight: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: hidden;width:62px;}

#yiv6900962383 #yiv6900962383photos div label {

color:#666666;font-size:10px; overflow:hidden;text-align:
center;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: Arial, 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

#yiv1964091577 #yiv1964091577 --
#yiv1964091577ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv1964091577 #yiv1964091577ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv1964091577 #yiv1964091577ygrp-mkp #yiv1964091577hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv1964091577 #yiv1964091577ygrp-mkp #yiv1964091577ads {
margin-bottom:10px;}

#yiv1964091577 #yiv1964091577ygrp-mkp .yiv1964091577ad {
padding:0 0;}

#yiv1964091577 #yiv1964091577ygrp-mkp .yiv1964091577ad p {
margin:0;}

#yiv1964091577 #yiv1964091577ygrp-mkp .yiv1964091577ad a {
color:#0000ff;text-decoration:none;}
#yiv1964091577 #yiv1964091577ygrp-sponsor
#yiv1964091577ygrp-lc {
font-family:Arial;}

#yiv1964091577 #yiv1964091577ygrp-sponsor
#yiv1964091577ygrp-lc #yiv1964091577hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv1964091577 #yiv1964091577ygrp-sponsor
#yiv1964091577ygrp-lc .yiv1964091577ad {
margin-bottom:10px;padding:0 0;}

#yiv1964091577 #yiv1964091577actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv1964091577 #yiv1964091577activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv1964091577 #yiv1964091577activity span {
font-weight:700;}

#yiv1964091577 #yiv1964091577activity span:first-child {
text-transform:uppercase;}

#yiv1964091577 #yiv1964091577activity span a {
color:#5085b6;text-decoration:none;}

#yiv1964091577 #yiv1964091577activity span span {
color:#ff7900;}

#yiv1964091577 #yiv1964091577activity span
.yiv1964091577underline {
text-decoration:underline;}

#yiv1964091577 .yiv1964091577attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv1964091577 .yiv1964091577attach div a {
text-decoration:none;}

#yiv1964091577 .yiv1964091577attach img {
border:none;padding-right:5px;}

#yiv1964091577 .yiv1964091577attach label {
display:block;margin-bottom:5px;}

#yiv1964091577 .yiv1964091577attach label a {
text-decoration:none;}

#yiv1964091577 blockquote {
margin:0 0 0 4px;}

#yiv1964091577 .yiv1964091577bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv1964091577 .yiv1964091577bold a {
text-decoration:none;}

#yiv1964091577 dd.yiv1964091577last p a {
font-family:Verdana;font-weight:700;}

#yiv1964091577 dd.yiv1964091577last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv1964091577 dd.yiv1964091577last p
span.yiv1964091577yshortcuts {
margin-right:0;}

#yiv1964091577 div.yiv1964091577attach-table div div a {
text-decoration:none;}

#yiv1964091577 div.yiv1964091577attach-table {
width:400px;}

#yiv1964091577 div.yiv1964091577file-title a, #yiv1964091577
div.yiv1964091577file-title a:active, #yiv1964091577
div.yiv1964091577file-title a:hover, #yiv1964091577
div.yiv1964091577file-title a:visited {
text-decoration:none;}

#yiv1964091577 div.yiv1964091577photo-title a,
#yiv1964091577 div.yiv1964091577photo-title a:active,
#yiv1964091577 div.yiv1964091577photo-title a:hover,
#yiv1964091577 div.yiv1964091577photo-title a:visited {
text-decoration:none;}

#yiv1964091577 div#yiv1964091577ygrp-mlmsg
#yiv1964091577ygrp-msg p a span.yiv1964091577yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv1964091577 .yiv1964091577green {
color:#628c2a;}

#yiv1964091577 .yiv1964091577MsoNormal {
margin:0 0 0 0;}

#yiv1964091577 o {
font-size:0;}

#yiv1964091577 #yiv1964091577photos div {
float:left;width:72px;}

#yiv1964091577 #yiv1964091577photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv1964091577 #yiv1964091577photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv1964091577 #yiv1964091577reco-category {
font-size:77%;}

#yiv1964091577 #yiv1964091577reco-desc {
font-size:77%;}

#yiv1964091577 .yiv1964091577replbq {
margin:4px;}

#yiv1964091577 #yiv1964091577ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv1964091577 #yiv1964091577ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv1964091577 #yiv1964091577ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv1964091577 #yiv1964091577ygrp-mlmsg select,
#yiv1964091577 input, #yiv1964091577 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv1964091577 #yiv1964091577ygrp-mlmsg pre, #yiv1964091577
code {
font:115% monospace;}

#yiv1964091577 #yiv1964091577ygrp-mlmsg * {
line-height:1.22em;}

#yiv1964091577 #yiv1964091577ygrp-mlmsg #yiv1964091577logo {
padding-bottom:10px;}

#yiv1964091577 #yiv1964091577ygrp-msg p a {
font-family:Verdana;}

#yiv1964091577 #yiv1964091577ygrp-msg
p#yiv1964091577attach-count span {
color:#1E66AE;font-weight:700;}

#yiv1964091577 #yiv1964091577ygrp-reco
#yiv1964091577reco-head {
color:#ff7900;font-weight:700;}

#yiv1964091577 #yiv1964091577ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ov
li a {
font-size:130%;text-decoration:none;}

#yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv1964091577 #yiv1964091577ygrp-sponsor #yiv1964091577ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv1964091577 #yiv1964091577ygrp-text {
font-family:Georgia;}

#yiv1964091577 #yiv1964091577ygrp-text p {
margin:0 0 1em 0;}

#yiv1964091577 #yiv1964091577ygrp-text tt {
font-size:120%;}

#yiv1964091577 #yiv1964091577ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv1964091577

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

2b

Re: Rumus Transpose Otomatis

Fri Feb 3, 2017 5:14 am (PST) . Posted by:

"ghozi alkatiri" ghozialkatiri

untuk kolom F rumusnya diganti di F3

=OFFSET(Contoh!$D$2;COLUMN(C:C)+TRUNC((ROW(1:1)-1)/(COUNTA(Contoh!$E$3:$XFD$3))+1)-1;MOD(ROW(1:1)-1;COUNTA(Contoh!$E$3:$XFD$3))+1)
copy ke bawah

wassalam

Ghozi Alkatiri
--------------------------------------------
Pada Jum, 3/2/17, Modby Matsnawabi modbym@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

Judul: Re: [belajar-excel] Rumus Transpose Otomatis
Kepada: "belajar-excel" <belajar-excel@yahoogroups.com>
Tanggal: Jumat, 3 Februari, 2017, 6:20 PM

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 ghozialkatiri@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com>
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 modbym@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com
> menulis:

Judul: Re: [belajar-excel] Rumus Transpose Otomatis [1
Attachment]

Kepada: "belajar-excel" <belajar-excel@yahoogroups.com
>

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 modbym@gmail.com

[belajar-excel] <belajar-excel@yahoogroups.com
>

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 ghozialkatiri@yahoo.co.id

[belajar-excel] <belajar-excel@yahoogroups.com

> 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 modbym@gmail.com

[belajar-excel] <belajar-excel@yahoogroups.com

> menulis:

Judul: [belajar-excel] Rumus Transpose Otomatis [1

Attachment]

Kepada: "belajar-excel" <belajar-excel@yahoogroups.com

>

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

#yiv2207720914 #yiv2207720914 --
#yiv2207720914ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv2207720914 #yiv2207720914ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv2207720914 #yiv2207720914ygrp-mkp #yiv2207720914hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv2207720914 #yiv2207720914ygrp-mkp #yiv2207720914ads {
margin-bottom:10px;}

#yiv2207720914 #yiv2207720914ygrp-mkp .yiv2207720914ad {
padding:0 0;}

#yiv2207720914 #yiv2207720914ygrp-mkp .yiv2207720914ad p {
margin:0;}

#yiv2207720914 #yiv2207720914ygrp-mkp .yiv2207720914ad a {
color:#0000ff;text-decoration:none;}
#yiv2207720914 #yiv2207720914ygrp-sponsor
#yiv2207720914ygrp-lc {
font-family:Arial;}

#yiv2207720914 #yiv2207720914ygrp-sponsor
#yiv2207720914ygrp-lc #yiv2207720914hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv2207720914 #yiv2207720914ygrp-sponsor
#yiv2207720914ygrp-lc .yiv2207720914ad {
margin-bottom:10px;padding:0 0;}

#yiv2207720914 #yiv2207720914actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv2207720914 #yiv2207720914activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv2207720914 #yiv2207720914activity span {
font-weight:700;}

#yiv2207720914 #yiv2207720914activity span:first-child {
text-transform:uppercase;}

#yiv2207720914 #yiv2207720914activity span a {
color:#5085b6;text-decoration:none;}

#yiv2207720914 #yiv2207720914activity span span {
color:#ff7900;}

#yiv2207720914 #yiv2207720914activity span
.yiv2207720914underline {
text-decoration:underline;}

#yiv2207720914 .yiv2207720914attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv2207720914 .yiv2207720914attach div a {
text-decoration:none;}

#yiv2207720914 .yiv2207720914attach img {
border:none;padding-right:5px;}

#yiv2207720914 .yiv2207720914attach label {
display:block;margin-bottom:5px;}

#yiv2207720914 .yiv2207720914attach label a {
text-decoration:none;}

#yiv2207720914 blockquote {
margin:0 0 0 4px;}

#yiv2207720914 .yiv2207720914bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv2207720914 .yiv2207720914bold a {
text-decoration:none;}

#yiv2207720914 dd.yiv2207720914last p a {
font-family:Verdana;font-weight:700;}

#yiv2207720914 dd.yiv2207720914last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv2207720914 dd.yiv2207720914last p
span.yiv2207720914yshortcuts {
margin-right:0;}

#yiv2207720914 div.yiv2207720914attach-table div div a {
text-decoration:none;}

#yiv2207720914 div.yiv2207720914attach-table {
width:400px;}

#yiv2207720914 div.yiv2207720914file-title a, #yiv2207720914
div.yiv2207720914file-title a:active, #yiv2207720914
div.yiv2207720914file-title a:hover, #yiv2207720914
div.yiv2207720914file-title a:visited {
text-decoration:none;}

#yiv2207720914 div.yiv2207720914photo-title a,
#yiv2207720914 div.yiv2207720914photo-title a:active,
#yiv2207720914 div.yiv2207720914photo-title a:hover,
#yiv2207720914 div.yiv2207720914photo-title a:visited {
text-decoration:none;}

#yiv2207720914 div#yiv2207720914ygrp-mlmsg
#yiv2207720914ygrp-msg p a span.yiv2207720914yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv2207720914 .yiv2207720914green {
color:#628c2a;}

#yiv2207720914 .yiv2207720914MsoNormal {
margin:0 0 0 0;}

#yiv2207720914 o {
font-size:0;}

#yiv2207720914 #yiv2207720914photos div {
float:left;width:72px;}

#yiv2207720914 #yiv2207720914photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv2207720914 #yiv2207720914photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv2207720914 #yiv2207720914reco-category {
font-size:77%;}

#yiv2207720914 #yiv2207720914reco-desc {
font-size:77%;}

#yiv2207720914 .yiv2207720914replbq {
margin:4px;}

#yiv2207720914 #yiv2207720914ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv2207720914 #yiv2207720914ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv2207720914 #yiv2207720914ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv2207720914 #yiv2207720914ygrp-mlmsg select,
#yiv2207720914 input, #yiv2207720914 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv2207720914 #yiv2207720914ygrp-mlmsg pre, #yiv2207720914
code {
font:115% monospace;}

#yiv2207720914 #yiv2207720914ygrp-mlmsg * {
line-height:1.22em;}

#yiv2207720914 #yiv2207720914ygrp-mlmsg #yiv2207720914logo {
padding-bottom:10px;}

#yiv2207720914 #yiv2207720914ygrp-msg p a {
font-family:Verdana;}

#yiv2207720914 #yiv2207720914ygrp-msg
p#yiv2207720914attach-count span {
color:#1E66AE;font-weight:700;}

#yiv2207720914 #yiv2207720914ygrp-reco
#yiv2207720914reco-head {
color:#ff7900;font-weight:700;}

#yiv2207720914 #yiv2207720914ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv2207720914 #yiv2207720914ygrp-sponsor #yiv2207720914ov
li a {
font-size:130%;text-decoration:none;}

#yiv2207720914 #yiv2207720914ygrp-sponsor #yiv2207720914ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv2207720914 #yiv2207720914ygrp-sponsor #yiv2207720914ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv2207720914 #yiv2207720914ygrp-text {
font-family:Georgia;}

#yiv2207720914 #yiv2207720914ygrp-text p {
margin:0 0 1em 0;}

#yiv2207720914 #yiv2207720914ygrp-text tt {
font-size:120%;}

#yiv2207720914 #yiv2207720914ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv2207720914

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

3a

Re: Tabel tanda tangan dalam Advanced Filter

Fri Feb 3, 2017 6:49 am (PST) . Posted by:

"rubi anto"

terima kasih pak hendrik saya sudah mencobanya... tapi masih ada yang ingin

tanyakan lagi.
1. Bagaimana caranya agar bordernya tidak hilang jika saya filter,karena
saya hanya menggunakan conditional formating.
2. bisa tidak jika kolom tanda tangannya dibuat di sheet lain dan nantinya
dikopy dibawah data,karena saya ingin membuat validasi untuk masing-masing
penanda tangan,jadi tidak perlu ketik ulang jika ganti orang....

untuk lebih jelasnya,monggo panjenengan lihat file terlampirnya

Pada 1 Februari 2017 21.07, hendrik karnadi hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> menulis:

>
>
> Hai Rubi,
> Terima kasih kembali.
> Sebagai variasi, coba buat macro agar no urut hasil filternya mulai dari
> no 1.
>
> Salam,
> HK
>
>
> Sent from Yahoo Mail on Android
> <https://overview.mail.yahoo.com/mobile/?.src=Android>
>
>
>
> Terima kasih pak hendrik.... sudah berhasil pak.....
>
>
> Pada 1 Februari 2017 14.28, hendrik karnadi hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com> menulis:
>

>>
>>
>> Hai Rubi,
>> Coba ubah codenya menjadi seperti ini :
>>
>> *ThisWorkbook.Sheets("Laporan") .Range("B7:F1000").Clear*
>>
>> ThisWorkbook.Sheets("Data"). Range("A1").CurrentRegion. _
>> AdvancedFilter Action:=xlFilterCopy, _
>> CriteriaRange:=ThisWorkbook. Sheets("Laporan").Range("C3: D4"), _
>> CopyToRange:=ThisWorkbook. Sheets("Laporan").Range("B6: F6"), _
>> Unique:=False
>>
>> *ThisWorkbook.Sheets("Laporan") .Range("B6").End(xlDown). Select*
>> *With Selection*
>> * .Offset(5, 0).Value = "Yang Membuat"*
>> * .Offset(5, 2).Value = "Mengetahui"*
>> * .Offset(5, 4).Value = "Menyetujui"*
>> * .Offset(9, 0).Value = "TTD1"*
>> * .Offset(9, 2).Value = "TTD2"*
>> * .Offset(9, 4).Value = "TTD3"*
>> *End With*
>>
>> *catatan : Huruf yang dicetak tebal adalah tambahan codenya*
>>
>> *Salam,*
>> *HK*
>>
>>
>> ------------------------------
>> *From:* "rubi anto rubiantoxx@gmail.com [belajar-excel]" <
>> belajar-excel@yahoogroups.com >
>> *To:* belajar-excel@yahoogroups.com
>> *Sent:* Wednesday, 1 February 2017, 12:58
>> *Subject:* Re: [belajar-excel] Tabel tanda tangan dalam Advanced Filter
>> [1 Attachment]
>>
>>
>> Apakah seperti data berikut Mr Kid....
>>
>> Tapi jika ada data bertambah malah tertindis...
>> Maaf saya masih belum Faham Mr. Kid.....
>>

>> Pada 31 Januari 2017 20.42, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]
>> <belajar-excel@yahoogroups.com > menulis:
>>
>>
>> Wa'alaikumussalam wr wb
>>
>> Hai Rubi,
>>
>> Bisa membuat template tanda tangan di tempat lain (misal di kolom ZZ
>> dst). Kemudian copy range area template tanda tangan tersebut ke bawah
>> tabel hasil advanced filter.
>>
>> Kalau proses copy template tanda tangan akan diotomasi, bisa dilakukan
>> dengan record macro.
>> Cara meletakkan posisi cells aktif ke baris kosong kedua di bawah tabel
>> pada kolom terkiri tabel adalah sebagai berikut : (asumsi : kolom pertama
>> tabel selalu terisi penuh)
>> 1. klik cells baris 1 pada sebuah kolom kosong
>> 2. tekan CTRL panah ke bawah -> tekan CTRL panah ke kiri ::: sampai
>> di kolom A baris terakhir Excel
>> 3. [jika kolom pertama tabel bukan di kolom A] tekan panah ke kanan
>> sampai ke kolom pertama tabel atau sampai kolom tabel yang selalu terisi
>> penuh
>> 4. tekan CTRL panah ke atas ::: sampai di baris terakhir tabel
>> 5. tekan panah ke bawah 2 kali ::: sampai baris kosong kedua di kolom
>> tabel yang selalu terisi penuh
>> 6. [jika kolom pertama tabel bukan kolom yang selalu terisi penuh] tekan
>> CTRL panah kiri atau kanan sampai ke kolom pertama tabel
>>
>>
>> Jadi urutan copy paste template tanda tangan menjadi :
>> >> blok area template tanda tangan -> tekan CTRL C -> ikuti langkah 1
>> sampai 6 di atas -> tekan CTRL V
>>
>>
>> Wassalamu'alaikum wr wb
>> Kid
>>
>>
>>
>> 2017-01-31 18:37 GMT+07:00 rubi anto rubiantoxx@gmail.com
>> [belajar-excel] <belajar-excel@yahoogroups.com >:
>>
>>
>> Assalamu'alaikum.....
>>
>> Para master excel.....
>>
>> Saya sedang belajar membuat data laporan pembayaran dengan advanced
>> filter macro,tetapi ada kendala....
>> kolom untuk tanda tangannya selalu saja hilang jika saya klik button
>> filter....
>>
>> Bagaimanakah caranya agar kolom tanda tangan itu tidak hilang dan selalu
>> berada di bawah tabel data jika data bertambah atau berkurang....
>>
>> Terimakasih sebelumnya atas bantuan para guru.....
>> (File terlampir)
>>
>> salam
>> Rubi
>>
>>
>>
>>
>>
>>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

4a

Re: Membuat Form Popup gambar

Fri Feb 3, 2017 7:03 am (PST) . Posted by:

"Odhyz" odhyz83_assasaky

Dear Mr Kid,
Sepertinya temen2 yg laen belum mau nyoba... Mungkin bisa Mr. Kid bisa bantu untuk Case ini....

Terima kasih atas bantuannya...

Dikirim dari iPhone saya

Pada 31 Jan 2017, pukul 12.26, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

> Hai Odhy,
>
> Dari penjelasan Anda terakhir, tampaknya Anda bermaksud userform hanya sebagai viewer saja.
> Kalau kasusnya demikian, maka userform tidak perlu diisi baris kode apapun.
> Seluruh kegiatan pengaturan dilakukan melalui event selection change milik worksheet, karena yang menjadi pemicu munculnya userform, tertutupnya userform, dan berubahnya nilai-nilai setiap controls dalam userform adalah kegiatan memindahkan cells yang aktif dari satu cells ke cells lain.
> Pembatasan area cells yang memicu proses terhadap userform juga diatur melalui event selection change tersebut.
>
> Bingungkan.... hadeeh...
>
> mbok yao urutan proses yang diinginkan itu ditulis yang rinci, supaya Anda sendiri bisa lebih mudah dalam :
> a. menganalisa permasalahan
> b. menyusun pertanyaan
> c. mencerna informasi dari orang lain terkait hal tersebut
> d. mempelajari, memahami, mencoba, dan mengamalkannya
>
> Contohnya begini...
> Misal ada area range D1:D10 dan pengennya adalah :
> 1. kalo user mindahin cells dari yang tadinya diluar suatu area (misal dari cells A1) hingga masuk ke area cells tertentu (misal ke D3), barulah si userform nongol
> -> artinya, butuh event selection change milik worksheet untuk nampilin si userform
> 2. kalo user mindahin cells diluar area tertentu (dari A1 ke Z99), si userform disembunyikan
> -> artinya, butuh event selection change milik worksheet untuk nyembunyiin si userform
> 3. kalo user mindahin cells dari dalam area cells tertentu (misal D7) ke luar area cells tertentu (misal ke C4), si userform disembunyikan
> -> artinya, butuh event selection change milik worksheet untuk nyembunyiin si userform
> 4. kalo user mindahin cells di dalam area tertentu (misal dari D2 ke D4), si userform tetap tampak, tapi nilai-nilai controls diubah nilainya
> -> artinya, butuh event selection change milik worksheet untuk mengubah nilai controls yang ada dalam userform
>
>
> loh... rak kepenak to kalo kaya di atas itu,
> penak le menganalisa, menanyakan, menjawab, mempelajari, dsb...
>
>
> Kalo lihat uraian di atas, jadinya gak butuh event keypress ataupun event keydown kan...
> Bisa jadi userform-nya malah beneran gak perlu diisi cekrip apapun.
> >> ada yang mau nyoba ?
> >> yang katanya suka VBA atau yang lagi demen VBA atau yang ngakunya rada atau mahir VBA, ayo coba deh...
>
> So, odhy sabar ya, lagi pada mikir-mikir berani nyoba ato kagak tuh..
>
>
> Regards,
> Kid
>
>
>
>
> 2017-01-30 17:06 GMT+07:00 Odhy odhyz83_assasaky@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:
>>
>> Dear Mr. Kid,
>> Apakah seperti ini :
>> Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
>> If KeyCode = vbKeyDown Then Unload Me
>> If KeyCode = vbKeyUp Then Unload Me

>> If KeyCode = vbKeyLeft Then Unload Me
>> If KeyCode = vbKeyRight Then Unload Me
>> End Sub
>>
>> apakah bisa pada saat tekan KeyDown / atau KeyUp userform langsung di close dan memunculkan userform untuk cell berikutnya.
>> karena kondisi dengan event keydown ini tekan keydown-> tutup userform, tekan keydown lagi baru muncul userform lagi.
>> apakah bisa seperti pada contoh yg Mr. Kid berikan sebelumnya saat pindah cell langsung muncul userform.
>>
>> Terima Kasih atas pencerahannya, Odhy.
>>
>>
>> On Monday, January 30, 2017 12:59 PM, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
>>
>>
>>
>> Hai Odhy,
>>
>> Coba manfaatkan event KeyDown daripada event KeyPress.
>>
>> Regards,
>> Kid
>>
>>
>> On Mon, Jan 30, 2017 at 11:51 AM, Odhy odhyz83_assasaky@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>>
>> Dear Mr. Kid,
>> saya coba menggunakan userform agar userform bisa close sendiri pada saat saya pilih keyDown dan keyUp bagaimana caranya??
>> jadi sementara saya menggunakan Enter untuk close spt code berikut :
>>
>> Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
>> If KeyAscii = 13 Then Unload Me
>> End Sub
>>
>>
>> Terima Kasih atas pencerahannya, Odhy.
>>
>>
>> On Monday, January 30, 2017 9:53 AM, "Odhy odhyz83_assasaky@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com > wrote:
>>
>>
>>
>> Dear Mr. Kid,
>> Keren.... Excel memang powerfull.....
>> Terima kasih atas bantuannya.........
>>
>> Odhy.
>>
>>
>> On Friday, January 27, 2017 7:41 AM, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com > wrote:
>>
>>
>>
>> Hai Odhyz,
>>

>> File terlampir contoh tanpa userform.
>> Meski tanpa userform, baris-baris kode yang ada bisa diterapkan pada image control yang ada dalam suatu userform.
>>
>> Di dalam baris-baris kode dalam file terlampir juga diberi keterangan dan beberapa tambahan yang masih non aktif seperti pengaturan ketersediaan default image file ketika data yang terpilih belum memiliki image file yang sesuai inputan.
>>
>> Silakan dikembangkan secara santun.
>>
>> Regards,
>> Kid
>>
>>
>>
>>
>>
>>
>> 2017-01-26 20:38 GMT+07:00 Odhyz odhyz83_assasaky@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com >:
>>
>> Dear Master,
>> Terima kasih atas bantuannya... Codingya sdh running....
>>
>> Dikirim dari iPhone saya
>>
>> Pada 24 Jan 2017, pukul 09.14, 'Heru Safrudin' fcprocessing.bisikediri@bisi. co.id [belajar-excel] <belajar-excel@yahoogroups.com > menulis:
>>
>>>
>>> Coba kode dikoreksi seperti berikut :
>>>
>>> Private Sub Worksheet_SelectionChange( ByVal Target As Range)
>>> Dim MyPath As String
>>> Dim Mypic As String
>>>
>>> MyPath = ThisWorkbook.Path & "\" & Target.Value & ".jpg" 'Ini sudah mendefinisikan gambarnya
>>> MsgBox MyPath
>>> If Intersect(Target, Range("a1:a5")) Is Nothing Then Exit Sub
>>> Mypic = Target.Value & ".jpg" 'Jadi ini sudah tidak diperlukan lagi
>>> UserForm1.Image1.Picture = LoadPicture(MyPath) 'Coba dengan kode ini
>>> UserForm1.Show
>>>
>>> End Sub
>>>
>>>
>>>
>>> <image002.gif><image004.png>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> From: belajar-excel@yahoogroups.com [mailto:belajar-excel@ yahoogroups.com]
>>> Sent: 19 Januari 2017 17:54
>>> To: belajar-excel@yahoogroups.com
>>> Subject: [belajar-excel] Membuat Form Popup gambar [1 Attachment]
>>>
>>>
>>> Dear Para Master,
>>> Mohon bantuannya, saya membuat Menu Popup namun belum berhasil (attach), pada saat saya klik tiap cell yang berisi item code akan muncul menu popup gambar sesui dengan item code pada tiap2 cell seperti terlampir.
>>>
>>> Atas bantuannya saya ucapkan banyak terima kasih.

>>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (12) . Top ^

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Visit Your Group

New Members 1

• Privacy • Unsubscribe • Terms of Use

Jumat, 03 Februari 2017

13 Messages

Messages

1a

Thu Feb 2, 2017 10:03 pm (PST) . Posted by:

1b

Fri Feb 3, 2017 12:13 am (PST) . Posted by:

1c

Fri Feb 3, 2017 1:18 am (PST) . Posted by:

1d

Fri Feb 3, 2017 2:17 am (PST) . Posted by:

1e

Fri Feb 3, 2017 2:33 am (PST) . Posted by:

1f

Fri Feb 3, 2017 3:11 am (PST) . Posted by:

1g

Fri Feb 3, 2017 3:20 am (PST) . Posted by:

1h

Fri Feb 3, 2017 11:00 am (PST) . Posted by:

1i

Fri Feb 3, 2017 1:24 pm (PST) . Posted by:

2a

Fri Feb 3, 2017 12:58 am (PST) . Posted by:

2b

Fri Feb 3, 2017 5:14 am (PST) . Posted by:

3a

Fri Feb 3, 2017 6:49 am (PST) . Posted by:

4a

Fri Feb 3, 2017 7:03 am (PST) . Posted by:

Tidak ada komentar:

Posting Komentar

Followers

Blog Archive

About Me