Lookup value with multiple criteria - Data Range in multiple files

Why not all data in 1 worksheet and after that analyse the data?

Is this a task you have to do often for this (same) files, or just 1 time.

If you have to do this ofter with the same time, I would go for 1 workbook, with all data in 1 worksheet. After that I would analyse the data.

If it was just one time, I would open the files. Rightclick in the tab-box and choose (click) for Make a copy. Then in To file, click on new file => a copy of the data are stored in a new workbook. Repeat this action for the other files.

I am not able to open the xls files at the moment. Since you use excel 2010 you could have post the xlsx files.

You probably don't get my point. Since you can only download 6 months, it would be easier if you add those new information (download) in a file which already contains the values of the earlier 5 years (minus the new 6 months).

If you do it your way, you always have to collect 9 files (4,5 year).
Those values are already available in your earlier collection.

Did you understand my comment?

You did not respond on that.

Maybe you will get trouble with the merged cells in A1 and B1.

My advice would be don't use merged cells.

Which version of Excel are you using?

Sir, I didn't have previously saved compiled data of past years in a single file.

How you add those information to the excel file, since it is not available?

Sir, merged cells in data files are coming in every exported files by default.

If you use a formula for your request, the merged cells will cause problems (sooner or later).

You can use the file, with the directory in cell A1.

You can run the code below, which is in this file.

Option Explicit


Sub MergeAllWorkbooks_0903()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, FNum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long

 
MyPath = Range("data!a1")           'Where do you find the directory

    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xls*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    'Fill the array(myFiles)with the list of Excel files in the folder
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    'Loop through all files in the array(myFiles)
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next

                With mybook.Worksheets(1)                     'sheet1
                    Set sourceRange = .Range("A1:J100")       '2) You can change this range
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(FNum)
                        End With

                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)

                        'we copy the values from the sourceRange to the destrange
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If

        Next FNum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

Yes, that is the idea, and after that you can analyse the data.

There is no sheet2 in the files you added to the question.

edit

The filename is sheet2 (which is confusing).

But what needs to be done with that file named sheet2.

It is not clear (at least not clear to me).

See the question in #19

But what needs to be done with that file named sheet2.

You question is (was) how to add the 3 (or more files) in 1 sheet.

You get code from me, how you can achieve that.

I'm asking about how I can add that data in my Sales Trend.xlsx file.

You did not mentioned earlier Sales Trend.xlsx file

Neither you did not posted this file (without confidential information).

How should I be expected to know what needs to be done with that data / file if you can't explain it.

In my earlier explanation I missed 1 step.

first use this code in each file with data.

Before you run this code make sure:
1) Change the name of the sheet in Data
2) Copy the data in row 2 (the green row)

The result of the code is in sheet output.

Sub CONVERTROWSTOCOL_Oeldere_revisted_new()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

'set the data
                 

Set mr = Sheets("DATA")                                  'this is the name of the source sheet
 
Set ms = Sheets("Output")                                       'this is the name of the destiny sheet

col = 5
'End set the data

    With ms
     .UsedRange.ClearContents
     .Range("A1:F1").Value = Array("Offie Code", "Product code", "LYr Avg", "Sales Qty Plan", "Month", "Value")

    End With
    
    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
    
    
    With mr
          rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
          For i = 4 To rsht1
                Do While .Cells(2, col).Value <> "" 'And .Cells(I, col).Value <> ""
                rsht2 = rsht2 + 1
               
                ms.Range("A" & rsht2).Value = .Range("A" & i).Value
                ms.Range("B" & rsht2).Value = .Range("B" & i).Value
                ms.Range("C" & rsht2).Value = .Range("C" & i).Value
                ms.Range("D" & rsht2).Value = .Range("C" & i).Value
                
                ms.Range("E" & rsht2).Value = .Cells(2, col).Value
                
                ms.Range("F" & rsht2).Value = .Cells(i, col).Value
         
                col = col + 1
            Loop
            col = 5
        Next
    End With
    
  With ms
  
  

'    .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
  
    .Columns("A:Z").EntireColumn.AutoFit
    
    End With
    
End Sub

After you have done that with all files.

Use the code I gave you earlier (also in this file).

After that a pivot table.

See the attached file.

A) 1files-data1
B) 2020-07-05 (oeldere) abhinavbinkar mergefile

The endresult is in file 2020-07-05 (oeldere) abhinavbinkar mergefile sheet PT Oeldere.

Set mr = Sheets("DATA")     'this is the name of the source sheet

1) That is why you need to change the name of the file in data. The code takes the data from sheet DATA.

HTML Code: 

HTML Code: 

2) You need to have the data (the green row) in row 2. (this is the row for the headers).


3) After that you can create a pivot table to analyse the data like I did.