pulling one coumn & related data from one file to other in excel

[ssbhatban]

Dear friends

I have two excel files
1. master
2. marks

There are multiple columns in each file . . now i am creating one more file called combine. my reference column is barcode column in both master & marks file. i want to bring all the data in master file to combine file as well as data from marks file to combine file . data from master file should come as it is , but the barcode in marks file will not be arranged as in master file. i want it to get arranged as in master file when it is imported to combine file and same way marks & bundle columns should also should get arranged in combine file

i am attaching image files & excel sample file for reference. i will be having hundreds of data. i will be having all the file in the same folder
what is the best & simple way to do it

thanks in advance

 

[ssbhatban]

Dear friend
The code you have given is working fine, I just wanted to refine it some more for better user expierence & data management & avoid mistakes since there is going to be lot of file.
Hope you can help me in this as you have done before. Sorry for troubling you again

1. Right now we have to keep both maste. Xls & marks. Xls to merge.
Can it be done without opening the files
2. Is there any possibility to change the code so that it is possible to select the file through a browse dialog box to select the master. Xls & marks. Xls since the file names may vary as per subject & it may be in different folders too

 

[mumps]

When you say that the file names will vary, will both file names vary or just the marks file? If they both vary, that is problematic because the macro in the combined workbook will have no way of identifying the files. If they both vary, is there a pattern in how they are named, for example Master1, Master2, Marks1 Marks2, etc. ?

 

[ssbhatban]

Dear friend
Actually they both vary because there are different people who will be creating the files in the specified format. They will be giving the subject name for the files so that it will be easy to identify without opening the files and subject names vary. I will be having around 70 different subjects. So that means 140 files, 70 master & 70 marks file.
Just for example there will ba English master & English marks file which I can put it in a folder along with combined file & merge
But next subject may be maths, so he will name it maths master & maths marks file & so on for different files. Now if I have to change the file names in the code for each subject it will be difficult & even if I create 70 combined file with specific names in the code, if there is a spelling mistake in the master or marks file then there will be error.
So it will be easy if there is a provision in the code to create a 'browse files to merge ' or some thing like that option when merge button is pressed then I can choose the required file without worrying about the spelling or location of the file

 

[mumps]

Choosing the files will not be a problem. The problem is that the macro needs a way to identify at least one of the two files. If the file names vary, will there be a pattern in the naming? You mentioned "maths master & maths marks". Will the master workbook name always contain the word "master" and will the marks workbook name always contain the word "marks"? If this is the case, then there is no problem to do what you want.

 

[ssbhatban]

Dear friend
Thanks, is it not possible to use like how we used to do in DOS *. Xls. or create a file open dialog box like how we open when opening a regular excel or word file & select the file from there
I can rename each file ending with master & marks. But as i said earlier there may be spelling mistakes while naming as many people will be doing it & error may happen.
If there is no other option then I have to settle for it & is it possible to combine without opening that master & marks file

 

[mumps]

As I mentioned before, using a file open dialog box is not the problem. The file names are the problem. If each file name can contain the words master and marks, that would solve the problem and I can suggest a macro. Please advise.

 

[ssbhatban]

Dear Friend ok, then I will put master & marks at the end of each file. For example it can be like this, maths-1-master.xls & maths-1-marks.xls.
Can it be merged withou opening these two file in the combine file

 

[mumps]

Try this macro. I have added code that makes sure that the user opens only two files and that those two files contain the text "master" and "marks".

Sub CopyData()
    Application.ScreenUpdating = False
Sub CopyData()
    Application.ScreenUpdating = False
    Dim master As Workbook, marks As Workbook, combined As Worksheet, y As Long
    Dim Arr As Variant, i As Long, srcRng As Range, x As Long, fd As FileDialog, lRow As Long, vSelectedItem As Variant
    Set combined = ThisWorkbook.Sheets("combined")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = ("Please select two files: 'master' and 'marks'.")
        .AllowMultiSelect = True
        If .Show = -1 Then
            If .SelectedItems.Count <> 2 Then
                MsgBox ("Please select two files: 'master' and 'marks'.")
                Exit Sub
            Else
                For Each vSelectedItem In .SelectedItems
                    If vSelectedItem Like "*master*" Or vSelectedItem Like "*marks*" Then
                        y = y + 1
                    End If
                Next vSelectedItem
                If y <> 2 Then
                    MsgBox ("You have selected the wrong file(s)." & Chr(10) & "Please select two files: 'master' and 'marks'.")
                    Exit Sub
                End If
            End If
            For Each vSelectedItem In .SelectedItems
                If vSelectedItem Like "*master*" Then
                    Set master = Workbooks.Open(vSelectedItem)
                ElseIf vSelectedItem Like "*marks*" Then
                    Set marks = Workbooks.Open(vSelectedItem)
                End If
            Next vSelectedItem
        End If
        If Application.Workbooks.Count <> 3 Then Exit Sub
    End With
    With marks.Sheets("Sheet1")
        Arr = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Resize(, 4).Value
    End With
    With combined
        .UsedRange.ClearContents
        master.Sheets("Sheet1").UsedRange.Cells.Copy .Range("A1")
        marks.Sheets("Sheet1").Range("B1:E1").Copy .Range("I1")
        Set srcRng = .Range("G2", .Range("G" & .Rows.Count).End(xlUp))
        For i = LBound(Arr) To UBound(Arr)
            If Arr(i, 1) <> "" Then
                If Not IsError(Application.Match(Arr(i, 1), srcRng, 0)) Then
                    x = Application.Match(Arr(i, 1), srcRng, 0)
                    .Range("I" & x + 1).Resize(, 4).Value = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3), Arr(i, 4))
                Else
                    marks.Sheets("Sheet1").Cells(i + 1, 2).Interior.ColorIndex = 3
                End If
            End If
        Next i
    End With
    marks.Close False
    master.Close False
    Application.ScreenUpdating = True
End Sub

 

[ssbhatban]

Dear Friend
Ok thank you very much , i will try & let you know tomorrow

 

[ssbhatban]

Dear Friend thank you very much. It has s