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

 

[mumps]

Your links take me to the Sign-In page not directly to the files.

 

[ssbhatban]

6-sem-mldthy.rar

drive.google.com

combine-final.rar

drive.google.com

 

[ssbhatban]

Dear friend
I think I found out probably one the problem, but not found the solution.
It is giving error message when there is a data mismatch between column G & column I.
In that case it should give an error message saying that particular data is not found and proceed further. Even the data not found or error cell can be highlighted in some colour

 

[ssbhatban]

Dear Friend
The problem is with the value G0625684 on the marks file. That value is not there in the master file. Hence it is giving error message. What I need is when such problem occurs it should give a message to user, then highlight the cell in some colour & move to next operation

 

[mumps]

Please upload copies of your files that are not zipped.

 

[ssbhatban]

master.xlsx

drive.google.com

marks.xlsx

drive.google.com

combined.xlsx.xlsm

drive.google.com

 

[mumps]

Try this version:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim master As Workbook, marks As Workbook, combined As Worksheet
    Dim Arr As Variant, i As Long, srcRng As Range, x As Long
    Set master = Workbooks("Master.xlsx")
    Set marks = Workbooks("Marks.xlsx")
    Set combined = ThisWorkbook.Sheets("combined")
    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
    Application.ScreenUpdating = True
End Sub

This will highlighted the barcode in red in marks. I did not include the message because if you have many barcodes that don't match, the many messages could be bothersome. If you want the message included, just insert it below the "Else" line of code.

 

[ssbhatban]

Dear Friend
A very big thanks for you for spending your time & effort in giving solution to me . It is working very beautifully. Lot of my work has reduced because of this code

 

[mumps]

You are very welcome.

 

[ssbhatban]

?????