[belajar-excel] Digest Number 4061

Milis Belajar Microsoft Excel Group

3 Messages

Digest #4061

1a

Bls: [belajar-excel] Cara menghitung Durasi [1 Attachment] by "Alfan Atas Pujianto" al_mito

2a

Re: merubah data vertical to data horizontal [1 Attachment] by "Muhammad Iqbal" iq_bal76id

2b

Re: merubah data vertical to data horizontal by "Sony Ario"

Messages

1a

Bls: [belajar-excel] Cara menghitung Durasi [1 Attachment]

Sun Jan 8, 2017 5:10 am (PST) . Posted by:

"Alfan Atas Pujianto" al_mito

Thanks ya Om ....


Pada Jumat, 6 Januari 2017 17:40, "Deszan Manihuruk mdeszan@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  pakai rumus =(tanggal_kirim-tanggal_order)*24
semoga membantu ya om. ^_^


On Friday, January 6, 2017 12:57 AM, "Alfan Atas Pujianto alfanatas@yahoo.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:


  Mohon bantuan rumus untuk menghitung durasi, jika diketahui data sbb:

Formula yg harus saya tulis di kolom "durasi" seperti apa ya, tks

#yiv9396702721 #yiv9396702721 -- #yiv9396702721ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9396702721 #yiv9396702721ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9396702721 #yiv9396702721ygrp-mkp #yiv9396702721hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9396702721 #yiv9396702721ygrp-mkp #yiv9396702721ads {margin-bottom:10px;}#yiv9396702721 #yiv9396702721ygrp-mkp .yiv9396702721ad {padding:0 0;}#yiv9396702721 #yiv9396702721ygrp-mkp .yiv9396702721ad p {margin:0;}#yiv9396702721 #yiv9396702721ygrp-mkp .yiv9396702721ad a {color:#0000ff;text-decoration:none;}#yiv9396702721 #yiv9396702721ygrp-sponsor #yiv9396702721ygrp-lc {font-family:Arial;}#yiv9396702721 #yiv9396702721ygrp-sponsor #yiv9396702721ygrp-lc #yiv9396702721hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9396702721 #yiv9396702721ygrp-sponsor #yiv9396702721ygrp-lc .yiv9396702721ad {margin-bottom:10px;padding:0 0;}#yiv9396702721 #yiv9396702721actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9396702721 #yiv9396702721activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9396702721 #yiv9396702721activity span {font-weight:700;}#yiv9396702721 #yiv9396702721activity span:first-child {text-transform:uppercase;}#yiv9396702721 #yiv9396702721activity span a {color:#5085b6;text-decoration:none;}#yiv9396702721 #yiv9396702721activity span span {color:#ff7900;}#yiv9396702721 #yiv9396702721activity span .yiv9396702721underline {text-decoration:underline;}#yiv9396702721 .yiv9396702721attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9396702721 .yiv9396702721attach div a {text-decoration:none;}#yiv9396702721 .yiv9396702721attach img {border:none;padding-right:5px;}#yiv9396702721 .yiv9396702721attach label {display:block;margin-bottom:5px;}#yiv9396702721 .yiv9396702721attach label a {text-decoration:none;}#yiv9396702721 blockquote {margin:0 0 0 4px;}#yiv9396702721 .yiv9396702721bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9396702721 .yiv9396702721bold a {text-decoration:none;}#yiv9396702721 dd.yiv9396702721last p a {font-family:Verdana;font-weight:700;}#yiv9396702721 dd.yiv9396702721last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9396702721 dd.yiv9396702721last p span.yiv9396702721yshortcuts {margin-right:0;}#yiv9396702721 div.yiv9396702721attach-table div div a {text-decoration:none;}#yiv9396702721 div.yiv9396702721attach-table {width:400px;}#yiv9396702721 div.yiv9396702721file-title a, #yiv9396702721 div.yiv9396702721file-title a:active, #yiv9396702721 div.yiv9396702721file-title a:hover, #yiv9396702721 div.yiv9396702721file-title a:visited {text-decoration:none;}#yiv9396702721 div.yiv9396702721photo-title a, #yiv9396702721 div.yiv9396702721photo-title a:active, #yiv9396702721 div.yiv9396702721photo-title a:hover, #yiv9396702721 div.yiv9396702721photo-title a:visited {text-decoration:none;}#yiv9396702721 div#yiv9396702721ygrp-mlmsg #yiv9396702721ygrp-msg p a span.yiv9396702721yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9396702721 .yiv9396702721green {color:#628c2a;}#yiv9396702721 .yiv9396702721MsoNormal {margin:0 0 0 0;}#yiv9396702721 o {font-size:0;}#yiv9396702721 #yiv9396702721photos div {float:left;width:72px;}#yiv9396702721 #yiv9396702721photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9396702721 #yiv9396702721photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9396702721 #yiv9396702721reco-category {font-size:77%;}#yiv9396702721 #yiv9396702721reco-desc {font-size:77%;}#yiv9396702721 .yiv9396702721replbq {margin:4px;}#yiv9396702721 #yiv9396702721ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9396702721 #yiv9396702721ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9396702721 #yiv9396702721ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9396702721 #yiv9396702721ygrp-mlmsg select, #yiv9396702721 input, #yiv9396702721 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9396702721 #yiv9396702721ygrp-mlmsg pre, #yiv9396702721 code {font:115% monospace;}#yiv9396702721 #yiv9396702721ygrp-mlmsg * {line-height:1.22em;}#yiv9396702721 #yiv9396702721ygrp-mlmsg #yiv9396702721logo {padding-bottom:10px;}#yiv9396702721 #yiv9396702721ygrp-msg p a {font-family:Verdana;}#yiv9396702721 #yiv9396702721ygrp-msg p#yiv9396702721attach-count span {color:#1E66AE;font-weight:700;}#yiv9396702721 #yiv9396702721ygrp-reco #yiv9396702721reco-head {color:#ff7900;font-weight:700;}#yiv9396702721 #yiv9396702721ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9396702721 #yiv9396702721ygrp-sponsor #yiv9396702721ov li a {font-size:130%;text-decoration:none;}#yiv9396702721 #yiv9396702721ygrp-sponsor #yiv9396702721ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9396702721 #yiv9396702721ygrp-sponsor #yiv9396702721ov ul {margin:0;padding:0 0 0 8px;}#yiv9396702721 #yiv9396702721ygrp-text {font-family:Georgia;}#yiv9396702721 #yiv9396702721ygrp-text p {margin:0 0 1em 0;}#yiv9396702721 #yiv9396702721ygrp-text tt {font-size:120%;}#yiv9396702721 #yiv9396702721ygrp-vital ul li:last-child {border-right:none !important;}#yiv9396702721

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

2a

Re: merubah data vertical to data horizontal [1 Attachment]

Sun Jan 8, 2017 5:10 am (PST) . Posted by:

"Muhammad Iqbal" iq_bal76id

Dear Mas Ario

Terima kasih atas supportnya, sangat membantu sekali, namun saya belum memahami macro sama sekali, bisa di bantu mas untuk description tiap syntax nya? Sehingga saya bisa mengembangkan dalam bentuk record data yang saya gunakan.

(maaf jadi melebar pertanyaannya)

Sub Button1_Click()

Dim a, b, c, rws, cols As Long

Dim Data As String

a = WorksheetFunction.CountA(Rows(1)) - 1

b = WorksheetFunction.CountA(Range("A:A")) - 1

c = b + 3

Range("A1").Select

For cols = 1 To b

For rws = 1 To a + 1

Range("A" & c).Value = ActiveCell.Offset(0, cols).Value

Range("B" & c).Value = ActiveCell.Offset(rws, 0).Value

Range("C" & c).Value = ActiveCell.Offset(rws, cols).Value

c = c + 1

Next rws

Next cols

End Sub

Thanks

BR
Muhammad Iqbal

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: January 7, 2017 9:11 AM
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] merubah data vertical to data horizontal [1 Attachment]

Assalamualaikum Muhammad.

Terlampir file nya menggunakan Macro seadanya.

Maaf kalau belum seperti kemauan.

Wassalam

Sony

2016-12-28 16:09 GMT+07:00 'Muhammad Iqbal' iq_bal76id@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

Dear be excel

Mohon saran untuk logika/formulasi untuk merubah data dari bentuk horizontal menjadi bentuk vertical dibawah ini

Data awal :

Item

1-Jan-16

2-Jan-16

3-Jan-16

4-Jan-16

Alfa

10

10

11

13

beta

12

12

11

10

gama

14

13

14

11

omega

17

16

17

16

tetra

14

13

14

14

Akan di ubah menjadi bentuk seperti ini

Tanggal

Item

1-Jan-16

Alfa

1-Jan-16

Beta

1-Jan-16

Gama

1-Jan-16

Omega

1-Jan-16

Tetra

2-Jan-16

Alfa

2-Jan-16

Beta

2-Jan-16

Gama

2-Jan-16

Omega

2-Jan-16

Tetra

3-Jan-16

Alfa

3-Jan-16

Beta

3-Jan-16

Gama

3-Jan-16

Omega

3-Jan-16

tetra

4-Jan-16

Alfa

4-Jan-16

beta

4-Jan-16

gama

4-Jan-16

omega

4-Jan-16

tetra

Selama ini masih saya lakukan secara manual dengan copy paste.

Matursuwun sebelumnya.

BR
Muhammad Iqbal

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

2b

Re: merubah data vertical to data horizontal

Mon Jan 9, 2017 1:46 am (PST) . Posted by:

"Sony Ario"

hi Iqbal,

Maaf baru sempat respon.
Maksudnya bahasa manusia dari skrip itu ya ? :D inti nya si pertama saya
coba hitung berapa banyak baris data dan kolom nya. Proses itu diwakili
dengan variabel a untuk hitung Kolom. Dan b Untuk hitung baris data (alpha,
beta, gama dst..).

Setelah dapat nilai b, selalu ditambahkan 3 baris baru untuk meletakan
transpose data nya. Proses ini diwakili dengan isi variabel B yang selalu
ditambah 3 (c = b + 3). Berikutnya barulah proses merubah baris jadi kolom,
dan kolom jadi baris melalui proses looping.

Gitu aja si kira2. Menjawab gak ya ? :D

Sub Button1_Click()

Dim a, b, c, rws, cols As Long

Dim Data As String

a = WorksheetFunction.CountA(Rows(1)) - 1

b = WorksheetFunction.CountA(Range("A:A")) - 1

c = b + 3

Range("A1").Select

For cols = 1 To b

For rws = 1 To a + 1

Range("A" & c).Value = ActiveCell.Offset(0, cols).Value

Range("B" & c).Value = ActiveCell.Offset(rws, 0).Value

Range("C" & c).Value = ActiveCell.Offset(rws, cols).Value

c = c + 1

Next rws

Next cols

End Sub

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

2017-01-08 11:30 GMT+07:00 'Muhammad Iqbal' iq_bal76id@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Dear Mas Ario
>
> Terima kasih atas supportnya, sangat membantu sekali, namun saya belum
> memahami macro sama sekali, bisa di bantu mas untuk description tiap syntax
> nya? Sehingga saya bisa mengembangkan dalam bentuk record data yang saya
> gunakan.
>
> (maaf jadi melebar pertanyaannya)
>
> Sub Button1_Click()
>
> Dim a, b, c, rws, cols As Long
>
> Dim Data As String
>
>
>
> a = WorksheetFunction.CountA(Rows(1)) - 1
>
> b = WorksheetFunction.CountA(Range("A:A")) - 1
>
> c = b + 3
>
>
>
> Range("A1").Select
>
>
>
> For cols = 1 To b
>
> For rws = 1 To a + 1
>
> Range("A" & c).Value = ActiveCell.Offset(0, cols).Value
>
> Range("B" & c).Value = ActiveCell.Offset(rws, 0).Value
>
> Range("C" & c).Value = ActiveCell.Offset(rws, cols).Value
>
> c = c + 1
>
> Next rws
>
> Next cols
>
> End Sub
>
>
>
> Thanks
>
> BR
> Muhammad Iqbal
>
>
>
> *From:* belajar-excel@yahoogroups.com [mailto:belajar-excel@
> yahoogroups.com]
> *Sent:* January 7, 2017 9:11 AM
> *To:* belajar-excel@yahoogroups.com
> *Subject:* Re: [belajar-excel] merubah data vertical to data horizontal
> [1 Attachment]
>
>
>
>
>
> Assalamualaikum Muhammad.
>
>
>
> Terlampir file nya menggunakan Macro seadanya.
>
> Maaf kalau belum seperti kemauan.
>
>
>
> Wassalam
>
> Sony
>
>
>
>
>
> 2016-12-28 16:09 GMT+07:00 'Muhammad Iqbal' iq_bal76id@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Dear be excel
>
> Mohon saran untuk logika/formulasi untuk merubah data dari bentuk
> horizontal menjadi bentuk vertical dibawah ini
>
>
>
> Data awal :
>
> Item
>
> 1-Jan-16
>
> 2-Jan-16
>
> 3-Jan-16
>
> 4-Jan-16
>
> Alfa
>
> 10
>
> 10
>
> 11
>
> 13
>
> beta
>
> 12
>
> 12
>
> 11
>
> 10
>
> gama
>
> 14
>
> 13
>
> 14
>
> 11
>
> omega
>
> 17
>
> 16
>
> 17
>
> 16
>
> tetra
>
> 14
>
> 13
>
> 14
>
> 14
>
>
>
> Akan di ubah menjadi bentuk seperti ini
>
> Tanggal
>
> Item
>
> 1-Jan-16
>
> Alfa
>
> 1-Jan-16
>
> Beta
>
> 1-Jan-16
>
> Gama
>
> 1-Jan-16
>
> Omega
>
> 1-Jan-16
>
> Tetra
>
> 2-Jan-16
>
> Alfa
>
> 2-Jan-16
>
> Beta
>
> 2-Jan-16
>
> Gama
>
> 2-Jan-16
>
> Omega
>
> 2-Jan-16
>
> Tetra
>
> 3-Jan-16
>
> Alfa
>
> 3-Jan-16
>
> Beta
>
> 3-Jan-16
>
> Gama
>
> 3-Jan-16
>
> Omega
>
> 3-Jan-16
>
> tetra
>
> 4-Jan-16
>
> Alfa
>
> 4-Jan-16
>
> beta
>
> 4-Jan-16
>
> gama
>
> 4-Jan-16
>
> omega
>
> 4-Jan-16
>
> tetra
>
>
>
> Selama ini masih saya lakukan secara manual dengan copy paste.
>
>
>
> Matursuwun sebelumnya.
>
>
>
> BR
> Muhammad Iqbal
>
>
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . 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