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

  • 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

G

Guruprasad1987

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

Attachments

  • Country system files.zip
    35.2 KB · Views: 8
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) ?
​
 
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 )
 

So how to differentiate a "CoreBanking" file from a "Payment" file
or these strings are always within source workbooks names ?​
 
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.
 

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 ?
​
 
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 .
 
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 !​
 

Attachments

  • Summary .xlsb
    14.3 KB · Views: 9
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 .
 

Attachments

  • Country system files.zip
    116.7 KB · Views: 2
Last edited by a moderator:
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: 
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 !
​
 
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 ?
 
Marc L said:
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: 
Private Sub Demo()
    Const E = ";Extended Properties=""Excel 12.0;HDR=Yes"""
    Dim oCn As Object, P$, F$, V, C%, R&, K$, VA
          Me.UsedRange.Offset(1).Clear
          P = ThisWorkbook.Path & "\"
          F = Dir(P & "?? *.xlsx")
       If F = "" Then Beep: Exit Sub Else [E2].Value = "    Wait  …"
          P = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & P
    Set oCn = CreateObject("ADODB.Connection")
With CreateObject("Scripting.Dictionary")
    While F > ""
             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
    Wend
          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"")&""econcilled"""
             .Item("G:I").Formula = .Item("G:I").Value
    End With
End Sub
You may Like it !
​
Click to expand...

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
Marc L said:
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: 
Private Sub Demo()
    Const E = ";Extended Properties=""Excel 12.0;HDR=Yes"""
    Dim oCn As Object, P$, F$, V, C%, R&, K$, VA
          Me.UsedRange.Offset(1).Clear
          P = ThisWorkbook.Path & "\"
          F = Dir(P & "?? *.xlsx")
       If F = "" Then Beep: Exit Sub Else [E2].Value = "    Wait  …"
          P = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & P
    Set oCn = CreateObject("ADODB.Connection")
With CreateObject("Scripting.Dictionary")
    While F > ""
             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
    Wend
          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"")&""econcilled"""
             .Item("G:I").Formula = .Item("G:I").Value
    End With
End Sub
You may Like it !
​
Click to expand...
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 .
 

Attachments

  • Payment.xlsx
    9.4 KB · Views: 7
  • Corebanking.xlsx
    9.4 KB · Views: 5
  • Summary .xlsb
    16.1 KB · Views: 5
Last edited:
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 …
​
 
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 .
 
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:
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 …
​
 
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 :)
 
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 …
​
 
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 .
 

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 ?
​
 
You must log in or register to reply here.
Back
Top