1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Retrieve key cols from multiple workbook and prepare report

Discussion in 'VBA Macros' started by Guruprasad1987, Apr 4, 2017.

  1. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Hi guess, I have posted my query earlier in the wrong window . Reposting the same under macros.
    I have folder that contains 100 excel workbooks - countries and systems . In each of these workbook I need to retrieve few columns and make a key in the master table . retrieve the chosen columns along with needed data for all the workbooks consolidate in master table .Have attached the sample of files country , system wise .I have also placed the expected result in the summary sheet . the macro must look up the files populate the respective data in the summary sheet as mentioned. It will be great if the code is not hardcoded . The number of rows for each month varies . Have also jumbled the columns as is the way we get the data . Seeking your kind help and support . Thanks .

    Attached Files:

  2. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    Hi !

    Yes it's far better to post in appropriate forum …

    Source workbooks start always with two letters and a space,
    never 3 letters ?
    And name always ends like "CoreBanking.xlsx" or "Payment .xlsx"
    (with always a space between Payment and .xlsx) ?
  3. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Hi Mark , Thanks for the quick revert . Yes the country files contain only 2 character followed by a space . The name can end with any combinations not just corebanking , Payment , coule be any generic string . For example names could be SG Retaildata , SG <Space> any string ( generic codes max 6 -10 character names )
  4. Marc L

    Marc L Excel Ninja

    Messages:
    2,891

    So how to differentiate a "CoreBanking" file from a "Payment" file
    or these strings are always within source workbooks names ?​
  5. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Yes , the files can be differentiated based on their names for example , core banking files would be under the names SG Corebanking , similarly payment would be under SG Payment . however , the names are input by various teams . the revceiver out of his experience knows that the files pertain to which system . Usual format of the file i.e <country> space < EBBS > ( RLS , SECCURE , GPBS , RCMS etc ) so based on these names we can identify the files pertains to which system .

    The exercise is to recon various systems mentioned above feeding into corebanking system ( EBBS) at the key level . so requriement is to match the currency , GL account for a particular country against country corebanking file and generate the summary report as mentioned.
  6. Marc L

    Marc L Excel Ninja

    Messages:
    2,891

    So only files starting with 2 characters then with a space
    with later CoreBanking or Payment and ending with .xlsx
    will be in summary workbook, correct ?
  7. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Yes to make it simple you can consider the folder to contain files related to payment and corebanking across various markets .
    I can replicate the code to compare against corebanking system with other systems such as trade etc at my end by segreagting folder wise . This will minimize the complexity . Yes at present you can compare just payment vs corebanking and the summary report .
  8. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    Next attachment must be saved in source workbooks folder
    and try it first with your initial source attachment …

    Do you like it ? So thanks to click on bottom right Like !​

    Attached Files:

  9. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    This is too great Mark !!!! :) Thanks the code is working perfect . I can customize this accordingly . Many thanks .
  10. Marc L

    Marc L Excel Ninja

    Messages:
    2,891


    I just mod previous attachment for variables minor optimization …​
    Guruprasad1987 likes this.
  11. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Hi Marc , am unable to rechange the system names in the code and run the macro in some other folder attaching the details for better clarity .

    It will be great if i can cusomize the code to change the systems names and run the macro in someother folder . e..g collections etc .

    Can you please help me creating a pivot macro having the following in place , Month , Country in Row labels , systems ( payment , corebanking etc) in the column .

    Thanks .

    Attached Files:

    Last edited by a moderator: Apr 6, 2017
  12. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    Placing summary workbook in source files folder avoids selecting folder !
    If you wanna to select a folder use FileDialog object
    as in VBA inner help sample or like in thread
    Export multiple worksheets in a file to multiple csv files

    Next demonstration puts values to Payment column
    if source files names do not contain CoreBanking
    (so working for Payment as well for Collection files) :​
    Code (vb):
    Sub Demo1()
        Const E = ";Extended Properties=""Excel 12.0;HDR=Yes"""
        Dim oCn As Object, P$, F$, V, C%, R&, K$, VA
              P = ThisWorkbook.Path & "\"
              F = Dir(P & "?? *.xlsx"):  If F = "" Then Beep: Exit Sub
              Me.UsedRange.Offset(1).Clear
              [E2].Value = "    Wait  …"
              P = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & P
        Set oCn = CreateObject("ADODB.Connection")
    With CreateObject("Scripting.Dictionary")
        Do
                 oCn.Open P & F & E
            With oCn.Execute("SELECT month,currency,[gl account],revenue FROM [Sheet1$]")
                V = .GetRows
                    .Close
            End With
                 oCn.Close
                C = -(F Like "*CoreBanking*")
                F = vbTab & Left(F, 2) & vbTab
            For R = 0 To UBound(V, 2)
                K = V(0, R) & F & V(1, R) & vbTab & V(2, R)
                If .Exists(K) Then VA = .Item(K) Else ReDim VA(1)
                   VA(C) = VA(C) + V(3, R)
                .Item(K) = VA
            Next
                   F = Dir
        Loop Until F = ""
              R = .Count
        Set oCn = Nothing
        [A2].Resize(R).Value = Application.Transpose(.Keys)
        [A2].Resize(R).TextToColumns Tab:=True
        [E2:F2].Resize(R).Value = Application.Index(.Items, 0)
        .RemoveAll
    End With
        With [A2:F2].Resize(R).Columns
            .Item("E:G").NumberFormat = Cells(7).NumberFormat
                .Item(8).NumberFormat = Cells(8).NumberFormat
                     .Item(7).Formula = "=F2-E2"
                     .Item(8).Formula = "=IF(F2=0,""-"",E2/F2)"
                     .Item(9).Formula = "=IF(E2=F2,""R"",""Not r"")&""econciled"""
                 .Item("G:I").Formula = .Item("G:I").Value
        End With
    End Sub
    You may Like it !
    Guruprasad1987 likes this.
  13. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Thanks Marc. Would like to place a pivot button to create a pivot table for the dynamic range of cells next to the summary button you mentioned . Is it possible ?
  14. Marc L

    Marc L Excel Ninja

    Messages:
    2,891


    I think so but that sounds like a great idea for a new thread !​
  15. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Noted . Will create a thread " Pivot Macro with dynamic range cells "
  16. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    The country files for the systems such as payment , corebaniing are maintained separately in the folder and the above codes pick the first two digit character and matches the contents in the two system . can we have two
    Hi , i am having difficulty in maintaining country wise sheet .Is it possible to eliminate the countries from the worksheet names and place a column called country inside the sheets .
    payment , core banking as the only two excel workbooks . The code should be able to pick the match key based on country , currency , GL account in the two workbooks and to display the same result as expected . Also I came across scenario where the names of the columns also has changed for some countries the above code is not working .

    Attached Files:

    Last edited: Apr 8, 2017
  17. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    Your post is unclear with quoting my post twice
    and your sentence not complete ‼

    If columns headers are not the same between source worksheets,
    it may be possible to work only with columns fixed order
    but it is not the case within your last attachment !

    So you must first think about a strategy how to detect columns to import …

    As in my first post in this thread I clearly asked about workbooks names,
    I can't waste time anymore, you must fix & set a definitive context,
    clearly and completely present it here (columns headers,
    workbooks names, attach files exactly reflecting real ones, …)
    and never change it after !

    Before to present your last will, take your time to well think about it …
  18. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Sorry for the inconvenience . Most of the cases the header or the column names were fixed and hence your earlier code was perfectly working . As I worked with more countries I realized , the column names were changing and also the positions . My sincere apologies for my inability to list the complete requirements and not all the requirements could be captured initially hence the tweek and the revert for need of change of code . Thanks for your time and timely help .
  19. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    From all countries files,
    what is the maximum number of data numeric columns ?
  20. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    The maximum number of columns in each of the files could range from A1 to AS column . Since I am in a position not to upload the orginal file due to security reasons , I had sent the payment ,Corebanking files with mocked up columns , while the column names , column positions of these attributes can vary i.e country , currency , Gl account , the underlying data needed for comparison remains the same.

    There are situations for certain countries the currency coloumn comes as currency_details etc similary the GL account comes as GL _account_num , GL _ACC etc , but the ultimately the contents need to be matched between two files irrespective of where the columns are placed , what the coloumn names are .
    Last edited: Apr 8, 2017
  21. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    You lost me ! Simple is my question, so again:
    in real files what is the maximum data numeric columns ?

    As coding is not possible without a way to identify expected columns …

    I can post a working code according to your last attachment
    but as it not respects real files layout the code won't work with them,
    so I won't waste time for such kind of code based upon bad attachment !

    As any code reflects the quality of explanation & attachment …
  22. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    Hi Marc , the data given in the earlier attachment stands good , there are umpteen columns which are not utilized while making the comparison . The total columns in each file are 20 . The file sent in the previous attachment suits the best possible columns that would be taken . you can give the working model of that . My last request :)
  23. Marc L

    Marc L Excel Ninja

    Messages:
    2,891
    Nope without any answer to my question
    neither a way to precisely identify expected columns !

    So at least explain what is your thinking, your strategy, …
    to pick up good columns from source workbooks ?!

    Coding just respects Logic …
  24. Guruprasad1987

    Guruprasad1987 Member

    Messages:
    30
    My strategy from the input files
    a) pick the month , country_code ,currency , gl account , the revenue/non funded income from payment file
    b) compare the corresponding country code , (currency/Curr/Curr_details) , gl account , revenue /non funded income in the core banking file
    c) the look up between the two files should be at the country + currency + Gl account level combinations
    d) the layout of the report is as mentioned in the summary sheet attachment .

    so since the names of the col in the input file varies from country to country u may use
    *curr* to denote currency columns ,
    revenue /Non _funded_income /NFI to denote the amount columns ,
    *country* to denote the country columns ,

    my report is what it should look in the summary sheet .
  25. Marc L

    Marc L Excel Ninja

    Messages:
    2,891

    Can't work as there is no gl account column in Corebanking file !

    Month, country and currency are always first columns in this order
    in all real files ?

Share This Page