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

Copy data to another workbook with a unique identifier or key

komlakingsley

New Member
Hi Team, I need help with 2 excel sheets as attached. I need a VBA script to copy data from columns H,I,J,K,L,M,N,O,P in Data 1 excel sheet to columns G,H,I,J,K,L,M,N,P in Data 2 excel sheet using column E as unique key. Thanks in advance.
 

Attachments

  • Data 1.xlsx
    392.4 KB · Views: 5
  • Data 2.xlsx
    489 KB · Views: 5
Hi !​
Hi Team, I need help with 2 excel sheets as attached
As you forgot to elaborate at least where are the files, are they closed or yet opened ?​
Where should be located the VBA procedure ?​
The better explanation, the less mod in code you may have to proceed …​
If I post a code according to your explanation you will have to 'fill holes' so are you enough confident with your Excel / VBA skills ?!​
 
komlakingsley
... with a unique identifier or key
Which one? ... later You wrote as unique key.
How many rows should copy based Your sample file?
Could You show Your expected results?


Apologies dear friends. Let me elaborate

Data 1 has inputs gathered from multiple sources by different people. Data 2 is the final compilation of these data collected. At the moment we are copying and transferring the data manually for over 2000 entries. This consumes time.

We require the data in Data 1 to be copied to Data 2 using the 'Serial number column' as the unique key to match same column in Data 2; the VBA procedure can be on Data 1.

The expected results is to have data in Data 1 sheet copied to Data 2 sheet.


Hope this helps.

Gracias.
 

Attachments

  • Data 1.xlsx
    392.3 KB · Views: 0
  • Data 2.xlsx
    133.1 KB · Views: 0
Hi !​

As you forgot to elaborate at least where are the files, are they closed or yet opened ?​
Where should be located the VBA procedure ?​
The better explanation, the less mod in code you may have to proceed …​
If I post a code according to your explanation you will have to 'fill holes' so are you enough confident with your Excel / VBA skills ?!​

Hi Marc,

Files attached. And yes they are both opened.

As explained above;

Data 1 has inputs gathered from multiple sources by different people. Data 2 is the final compilation of these data collected. At the moment we are copying and transferring the data manually for over 2000 entries. This consumes time.

We require the data in Data 1 to be copied to Data 2 using the 'Serial number column' as the unique key to match same column in Data 2; the VBA procedure can be on Data 1.

Thanks.
 

Attachments

  • Data 1.xlsx
    392.3 KB · Views: 5
  • Data 2.xlsx
    133.1 KB · Views: 3
komlakingsley
You seems to copy paste same writings two times ...
Did You try to answer my two questions?
... those are sentences which ends with ?

With the last one You wrote
The expected results is to have data in Data 1 sheet copied to Data 2 sheet.
and You have written
columns H,I,J,K,L,M,N,O,P in Data 1 excel sheet to columns G,H,I,J,K,L,M,N,P in Data 2 excel sheet using column E as unique key.

This consumes time.
... Yes, as well as if You've missed to give answers.
 
komlakingsley
You seems to copy paste same writings two times ...
Did You try to answer my two questions?
... those are sentences which ends with ?

With the last one You wrote
The expected results is to have data in Data 1 sheet copied to Data 2 sheet.
and You have written
columns H,I,J,K,L,M,N,O,P in Data 1 excel sheet to columns G,H,I,J,K,L,M,N,P in Data 2 excel sheet using column E as unique key.

This consumes time.
... Yes, as well as if You've missed to give answers.


Hi Vletm,

Answer to your question is as below;

... with a unique identifier or key
Which one? ... later You wrote as unique key.
Answer: using the 'Serial number column' as the unique key to match. - Column E is the serial number column

How many rows should copy based Your sample file?
Answer: All rows that contains data in sample data. There are 19 rows in sample data

Could You show Your expected results?
Answer: The expected results is to have data in Data 1 sheet copied to Data 2 sheet.
 
According to post #5 a VBA demonstration as a beginner starter to paste to the Master Data worksheet module :​
Code:
Sub Demo1()
        Const B = "Data 2.xlsx", S = "AFR"
        If Not Application.IfError(Evaluate("ISREF('[" & B & "]" & S & "'!A1)"), False) Then Beep: Exit Sub
    With Workbooks(B).Sheets(S).[A1].CurrentRegion.Columns
        For R& = 2 To [A1].CurrentRegion.Rows.Count
                V = Application.Match(Cells(R, 5).Value2, .Item(5), 0)
            If IsNumeric(V) Then
                .Item("G:N").Rows(V).Value2 = [H:O].Rows(R).Value2
                .Cells(V, 16).Value2 = Cells(R, 16).Value2
            End If
        Next
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Back
Top