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

help to create macro for combined report

mohan08

Member
Hi All,
Can you help me with combined report with two input file.
Wanted to create macro to extract input file 1 and input file 2 to have one combined report comparing value against each store
  • Input 1 – We have 5 column
  • Input 2 – We need to add 2 helper column to have the same format month as input1
  • Output – to import input 1 data and match and update input 2 data by comparing date and child store
 

Attachments

  • Input1.xlsx
    8.7 KB · Views: 4
  • Input2.xlsx
    9.5 KB · Views: 5
  • output.xlsx
    13.1 KB · Views: 4
Hi !​

  • Considering to allocate output workbook without any formula via a VBA procedure
    so Input2 workbook does not need any helper column (if ok Input2 columns A & B - at least B - could be deleted) …

  • output workbook : Column L is superfluous as column K already does exactly the same : TRUE if result is 0 …
    Column H is superfluous too as column G … So if ok columns L & H must be deleted.
Despite the many result errors in output workbook, if you agree with both points, I'll post later a procedure.​
If not, don't worry as it's almost at beginner level, wait for another helper …​
 
Hi Marc,

so Input2 workbook does not need any helper column (if ok Input2 columns A & B - at least B - could be deleted) --> added helper column for input2 as I get raw data which will start from column C to G,

for second point its ok, to be deleted.

Can you help me with the code for the above.
 
I do not well 'catch' for Input2 workbook so the easy way is to attach the last version if different than the initial attachment​
or just confirm it stays like in the initial attachment, thanks.​
If you receive this workbook like that, just tell us but I just wanna say if your original workbook has no helper column​
a VBA procedure does not need you create any before …​
Column A maybe usefull if the year is not the current year or in case of multiple year like 2018M12 & 2019M01 for example.​
But column B is useless if you create it manually before executing the code.​
 
Here attached the raw file with no helper column A and B.
regarding the multiple year, the extract will be for last 3 months and it will be having multiple years
 

Attachments

  • Input2.V2.xlsx
    8.7 KB · Views: 2
So according to these multiple years, what is the logic when this helper column is created ?​
If this logic can be well explained so it should be automatized within the VBA procedure.​
If not possible, so the helper column must be created manually before executing the VBA procedure …​
 
As data will be last 3 months, I use 2019M+Month (05) to create helper column manually

instead we use last digit on column B in output file to match and extract value from input file. I have modified the formula on column F in the output file
 

Attachments

  • output.xlsx
    13.1 KB · Views: 2
Ok so for Input2 just needs an helper column for the year/month so a total of 6 columns.​
As I wrote « Considering to allocate output workbook without any formula via a VBA procedure »​
meaning output workbook does not need any formula … Do you agree ?​
 
Directions to apply before executing the demonstration :​
  • Input2 workbook must be saved with 6 columns with a helper for the year/month in column A.

  • The demonstration expects the input workbooks in the same output workbook folder.
    If it's not the case, just amend the path in the F1 variable in the code …

  • output workbook :
  1. Delete column L then delete column H in order column J is the last used.
  2. Save this workbook as .xlsb binary format or as .xlsm macro format.
  3. Paste this demonstration to the Sheet1 worksheet module :
Code:
Sub Demo1()
     Dim F1$, F2$, R&, V, Rw As Range, W
         F1 = ThisWorkbook.Path & Application.PathSeparator & "Input1.xlsx"
         F2 = Replace(F1, "1", "2")
         If Dir(F1) = "" Or Dir(F2) = "" Then Beep: Exit Sub
         Me.UsedRange.Offset(1).Clear
    With GetObject(F1).Worksheets(1).UsedRange.Rows
        .Range("A2:D" & .Count).Copy [B2]
        .Range("E2:E" & .Count).Copy [H2]
        .Parent.Parent.Close False
    End With
    With Me.UsedRange.Columns
         R = .Rows.Count
         V = Evaluate(.Item(2).Address & "&"" ""&" & .Item(4).Address)
    End With
         Application.ScreenUpdating = False
    With GetObject(F2).Worksheets(1).UsedRange.Rows
        For Each Rw In .Item("2:" & .Count)
                W = Application.Match(Rw.Cells(1).Value2 & " " & Rw.Cells(4).Value2, V, 0)
            If IsError(W) Then
                R = R + 1
                Cells(R, 2).Value2 = Rw.Cells(1).Value2
                Rw.Columns("C:D").Copy Cells(R, 3)
                W = R
            End If
                Rw.Cells(5).Copy Cells(W, 6)
                Rw.Cells(6).Copy Cells(W, 9)
        Next
           .Parent.Parent.Close False
    End With
    With Me.UsedRange.Rows("2:" & R).Columns
         .Item(1).Value = Date
        .Item(7).Value2 = Evaluate(.Item(5).Address & "-" & .Item(6).Address)
       .Item(10).Value2 = Evaluate(.Item(8).Address & "-" & .Item(9).Address)
    End With
         Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top