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]

I can send the actual files if u can try on it

 

[mumps]

You could upload a copy of your files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

 

[ssbhatban]

combine.zip

drive.google.com

 

[ssbhatban]

Sent thru google drive. Have zipped the folder

 

[mumps]

I'll give it a try.

 

[ssbhatban]

Ok thanks

 

[mumps]

Try:

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(, 3).Value
    End With
    With combined
        master.Sheets("Sheet1").UsedRange.Cells.Copy .Range("A1")
        marks.Sheets("Sheet1").Range("B1:D1").Copy .Range("J1")
        Set srcRng = .Range("H2", .Range("H" & .Rows.Count).End(xlUp))
        For i = LBound(Arr) To UBound(Arr)
            If Arr(i, 1) <> "" Then
                x = Application.Match(Arr(i, 1), srcRng, 0)
                If Not IsError(x) Then
                    .Range("J" & x + 1).Resize(, 3).Value = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3))
                End If
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[ssbhatban]

Dear Friend

A big thanks for you. it is working superbly.
was this the problem ("J" & x + 1) ?

I could not understand properly the following syntax in your code. if you don't mind can you just tell about it

Set srcRng = .Range("H2", .Range("H" & .Rows.Count).End(xlUp))
For i = LBound(Arr) To UBound(Arr)
If Arr(i, 1) <> "" Then
x = Application.Match(Arr(i, 1), srcRng, 0)
If Not IsError(x) Then
.Range("J" & x + 1).Resize(, 3).Value = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3))
End If

Thanks once again for the effort you have taken to solve my problem

 

[mumps]

You are very welcome. Here is the code with some explanatory comments. I hope they make sense.

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(, 3).Value 'this array contains the values in columns B, C and D in marks
    End With
    With combined
        master.Sheets("Sheet1").UsedRange.Cells.Copy .Range("A1")
        marks.Sheets("Sheet1").Range("B1:D1").Copy .Range("J1")
        Set srcRng = .Range("H2", .Range("H" & .Rows.Count).End(xlUp)) 'this sets the range containing the bar codes in combined
        For i = LBound(Arr) To UBound(Arr) 'loops through the array 'Arr'
            If Arr(i, 1) <> "" Then 'checks for blank cells in column B of marks
                x = Application.Match(Arr(i, 1), srcRng, 0) 'finds the location of each bar code in srcRng
                If Not IsError(x) Then 'if the bar code is found, 'x' represents its position in scrRng
                    'for example - bar code G0607721 is the third barcode in srcRng so 'x' will be equal to 3
                    .Range("J" & x + 1).Resize(, 3).Value = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3)) 'x + 1 would be equal to 3 + 1 which corresponds to the
                        'row number of bar code G0607721 which is row 4. J4 is resized to include 3 columns (J4:L4) which is made equal to
                        'Arr(i, 1), Arr(i, 2), Arr(i, 3) which are B4, C4 and D4 in marks.
                End If
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[ssbhatban]

Dear friend
I am extremely sorry for troubling you once again.
I repeated the same code with samet set of files, columns etc but only with different values in the column. All the data from master file is getting copied to combine file, but only some data is getting copied from marks file & i am getting debug error at this point in the code
x=application.match(i,1),srcrng,0)
What does that mean, when it is working well in other files why it is working partly only in this file
I have attached both working and not working files for your reference if you are interested to see
files in the combine folder final are working well, while files in the 6-sem-mldthy folder is working partially.
Links for both zipped folders are here

6-sem-mldthy.rar

drive.google.com

combine-final.rar

drive.google.com

It would be very much helpful for me if u can give the solution to this.