Rabu, 16 September 2015

[belajar-excel] Digest Number 3568

5 Messages

Digest #3568
1a
1b
Re: Menggabungkan Vlookup pada beberapa kolom by "Hendrik Karnadi" hendrikkarnadi
2a
Re: Text to Columns by "Toby kenzo" tobykenzo
3a
Menggabungkan data by "hendrik karnadi" hendrikkarnadi
3b

Messages

Tue Sep 15, 2015 2:58 am (PDT) . Posted by:

"Firmanto Setiyantoro"

Dear All Master

Bagaimana cara menggabungkan dari beberapa kolom menjadi 1 fungsi vlookup

Thanks..
Firman

Tue Sep 15, 2015 3:19 am (PDT) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

Coba share dummy filenya pak.

Salam,
HK

Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: "Firmanto Setiyantoro fsetiyan@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:15/09/2015 16:18 (GMT+07:00) </div><div>To: belajar-excel@yahoogroups.com </div><div>Subject: [belajar-excel] Menggabungkan Vlookup pada beberapa kolom </div><div>
</div>Dear All Master

Bagaimana cara menggabungkan dari beberapa kolom menjadi 1 fungsi vlookup

Thanks..
Firman

Tue Sep 15, 2015 6:25 am (PDT) . Posted by:

"Toby kenzo" tobykenzo

Dear Pak ASL,

Berikut filenya, semoga bisa membantu.

It's work in Office 2013.

TerimaKasih.

Sent from Yahoo Mail on Android

Tue Sep 15, 2015 8:39 pm (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Dear Be-Exceller,
Sering kita harus menggabungkan beberapa tabel yang strukturnya (jumlah kolomnya) sama dari beberapa file dalam satu folder.
File terlampir yang saya dapat dari internet ini mungkin bisa membantu.Kuncinya ada pada kolom 1 dan bisa bersifat unik (Union) atau tidak unik (Union All).
Macronya seperti ini :Option Explicit
Sub Consolidate()
    Dim sSQL As String      'SQL String    Dim oLr  As ListRow     'Worksheets Row    Dim cn   As Object      'Connection    Dim rs   As Object      'Recordset
'   Create SQL    For Each oLr In Sheet1.ListObjects("Worksheets").ListRows        If sSQL <> "" Then sSQL = sSQL & vbCr & "Union " & vbCr        sSQL = sSQL & "Select * From " & oLr.Range(1)    Next    sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path)    '   Create Connection Objects    Set cn = CreateObject("ADODB.Connection")    Set rs = CreateObject("ADODB.Recordset")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _            "Data Source=" & ThisWorkbook.FullName & ";" & _            "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"    rs.Open sSQL, cn    Debug.Print sSQL
    If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete    Sheet1.ListObjects.Add( _        SourceType:=xlSrcQuery, _        Source:=rs, _        Destination:=Sheet1.Range("C6")).QueryTable.Refresh
    rs.Close    cn.Close
    Set rs = Nothing    Set cn = Nothing
End Sub
Mengenai bahasa manusianya mungkin Mr. Kid yang bisa membantu menjelaskannya.Terima kasih.
Salam,HK

Tue Sep 15, 2015 9:05 pm (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Untuk menjalankannya, Extract dulu foldernya ke suatu Directory, mis. D:\ atau F:\,lalu buka file Consolidate.
Salam.HK

----- Forwarded Message -----
From: "hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Sent: Wednesday, 16 September 2015, 10:39
Subject: [belajar-excel] Menggabungkan data [1 Attachment]

  Dear Be-Exceller,
Sering kita harus menggabungkan beberapa tabel yang strukturnya (jumlah kolomnya) sama dari beberapa file dalam satu folder.
File terlampir yang saya dapat dari internet ini mungkin bisa membantu.Kuncinya ada pada kolom 1 dan bisa bersifat unik (Union) atau tidak unik (Union All).
Macronya seperti ini :Option Explicit
Sub Consolidate()
    Dim sSQL As String      'SQL String    Dim oLr  As ListRow     'Worksheets Row    Dim cn   As Object      'Connection    Dim rs   As Object      'Recordset
'   Create SQL    For Each oLr In Sheet1.ListObjects("Worksheets").ListRows        If sSQL <> "" Then sSQL = sSQL & vbCr & "Union " & vbCr        sSQL = sSQL & "Select * From " & oLr.Range(1)    Next    sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path)    '   Create Connection Objects    Set cn = CreateObject("ADODB.Connection")    Set rs = CreateObject("ADODB.Recordset")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _            "Data Source=" & ThisWorkbook.FullName & ";" & _            "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"    rs.Open sSQL, cn    Debug.Print sSQL
    If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete    Sheet1.ListObjects.Add( _        SourceType:=xlSrcQuery, _        Source:=rs, _        Destination:=Sheet1.Range("C6")).QueryTable.Refresh
    rs.Close    cn.Close
    Set rs = Nothing    Set cn = Nothing
End Sub
Mengenai bahasa manusianya mungkin Mr. Kid yang bisa membantu menjelaskannya.Terima kasih.
Salam,HK
#yiv9480386457 #yiv9480386457 -- #yiv9480386457ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9480386457 #yiv9480386457ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9480386457 #yiv9480386457ygrp-mkp #yiv9480386457hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9480386457 #yiv9480386457ygrp-mkp #yiv9480386457ads {margin-bottom:10px;}#yiv9480386457 #yiv9480386457ygrp-mkp .yiv9480386457ad {padding:0 0;}#yiv9480386457 #yiv9480386457ygrp-mkp .yiv9480386457ad p {margin:0;}#yiv9480386457 #yiv9480386457ygrp-mkp .yiv9480386457ad a {color:#0000ff;text-decoration:none;}#yiv9480386457 #yiv9480386457ygrp-sponsor #yiv9480386457ygrp-lc {font-family:Arial;}#yiv9480386457 #yiv9480386457ygrp-sponsor #yiv9480386457ygrp-lc #yiv9480386457hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9480386457 #yiv9480386457ygrp-sponsor #yiv9480386457ygrp-lc .yiv9480386457ad {margin-bottom:10px;padding:0 0;}#yiv9480386457 #yiv9480386457actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9480386457 #yiv9480386457activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9480386457 #yiv9480386457activity span {font-weight:700;}#yiv9480386457 #yiv9480386457activity span:first-child {text-transform:uppercase;}#yiv9480386457 #yiv9480386457activity span a {color:#5085b6;text-decoration:none;}#yiv9480386457 #yiv9480386457activity span span {color:#ff7900;}#yiv9480386457 #yiv9480386457activity span .yiv9480386457underline {text-decoration:underline;}#yiv9480386457 .yiv9480386457attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9480386457 .yiv9480386457attach div a {text-decoration:none;}#yiv9480386457 .yiv9480386457attach img {border:none;padding-right:5px;}#yiv9480386457 .yiv9480386457attach label {display:block;margin-bottom:5px;}#yiv9480386457 .yiv9480386457attach label a {text-decoration:none;}#yiv9480386457 blockquote {margin:0 0 0 4px;}#yiv9480386457 .yiv9480386457bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9480386457 .yiv9480386457bold a {text-decoration:none;}#yiv9480386457 dd.yiv9480386457last p a {font-family:Verdana;font-weight:700;}#yiv9480386457 dd.yiv9480386457last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9480386457 dd.yiv9480386457last p span.yiv9480386457yshortcuts {margin-right:0;}#yiv9480386457 div.yiv9480386457attach-table div div a {text-decoration:none;}#yiv9480386457 div.yiv9480386457attach-table {width:400px;}#yiv9480386457 div.yiv9480386457file-title a, #yiv9480386457 div.yiv9480386457file-title a:active, #yiv9480386457 div.yiv9480386457file-title a:hover, #yiv9480386457 div.yiv9480386457file-title a:visited {text-decoration:none;}#yiv9480386457 div.yiv9480386457photo-title a, #yiv9480386457 div.yiv9480386457photo-title a:active, #yiv9480386457 div.yiv9480386457photo-title a:hover, #yiv9480386457 div.yiv9480386457photo-title a:visited {text-decoration:none;}#yiv9480386457 div#yiv9480386457ygrp-mlmsg #yiv9480386457ygrp-msg p a span.yiv9480386457yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9480386457 .yiv9480386457green {color:#628c2a;}#yiv9480386457 .yiv9480386457MsoNormal {margin:0 0 0 0;}#yiv9480386457 o {font-size:0;}#yiv9480386457 #yiv9480386457photos div {float:left;width:72px;}#yiv9480386457 #yiv9480386457photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9480386457 #yiv9480386457photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9480386457 #yiv9480386457reco-category {font-size:77%;}#yiv9480386457 #yiv9480386457reco-desc {font-size:77%;}#yiv9480386457 .yiv9480386457replbq {margin:4px;}#yiv9480386457 #yiv9480386457ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9480386457 #yiv9480386457ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9480386457 #yiv9480386457ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9480386457 #yiv9480386457ygrp-mlmsg select, #yiv9480386457 input, #yiv9480386457 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9480386457 #yiv9480386457ygrp-mlmsg pre, #yiv9480386457 code {font:115% monospace;}#yiv9480386457 #yiv9480386457ygrp-mlmsg * {line-height:1.22em;}#yiv9480386457 #yiv9480386457ygrp-mlmsg #yiv9480386457logo {padding-bottom:10px;}#yiv9480386457 #yiv9480386457ygrp-msg p a {font-family:Verdana;}#yiv9480386457 #yiv9480386457ygrp-msg p#yiv9480386457attach-count span {color:#1E66AE;font-weight:700;}#yiv9480386457 #yiv9480386457ygrp-reco #yiv9480386457reco-head {color:#ff7900;font-weight:700;}#yiv9480386457 #yiv9480386457ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9480386457 #yiv9480386457ygrp-sponsor #yiv9480386457ov li a {font-size:130%;text-decoration:none;}#yiv9480386457 #yiv9480386457ygrp-sponsor #yiv9480386457ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9480386457 #yiv9480386457ygrp-sponsor #yiv9480386457ov ul {margin:0;padding:0 0 0 8px;}#yiv9480386457 #yiv9480386457ygrp-text {font-family:Georgia;}#yiv9480386457 #yiv9480386457ygrp-text p {margin:0 0 1em 0;}#yiv9480386457 #yiv9480386457ygrp-text tt {font-size:120%;}#yiv9480386457 #yiv9480386457ygrp-vital ul li:last-child {border-right:none !important;}#yiv9480386457

============================================================
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
---------------------------------------------------------------------
Diposting oleh EXCEL FEED LEARNING di 02.06

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)