1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data from one Excel Workbook to another automatically

Discussion in 'VBA Macros' started by Frederik Grund, Apr 19, 2018.

  1. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    Hi Chandoo.

    I would like to copy data from Excel Workbook1 to Excel Workbook2 automatically. However, Workbook1 contains output data from another program which varies from day to day. This means that I need a code that transfers and transposes data matching column/row names (column "xx" in workbook1 to row "xx" in workbook2) rather than matching column A1 to row 1.

    I have uploaded two Workbooks which illustrates the problem:

    - Day1(sheet1 example) I have output for all my parameters (A-Q) and want to transfer data from Workbook1 B2-B18 (parameter A-Q) till Workbook2 B2-R2 (parameter A-Q).

    - Day2 (sheet2 example) I only have output for parameter F-Q and thus want to transfer data from Workbook1 B2-B13 (parameter F-Q) till Workbook2 B2-R2 leaving cells blank when parameters don't match F-Q.

    - Day3 (sheet3 example) I only have output for parameter G and Q and want to transfer data from Workbook1 B2-B3 till Workbook2 B2-R2 leaving cells blank when parameters don't match G and Q.

    Can this be done?

    Best regards,
    Frederik

    Attached Files:

  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,183
    Hi !

    Where code should be located :
    in one of these workbooks or within a third one ?
    (My two cents for the first one but maybe I'm wrong …)

    Workbook2 is pre-formated ?
    Nothing to create but just check for matching headers, right ?
  3. Marc L

    Marc L Excel Ninja

    Messages:
    4,183

    Notice you even do not need any VBA code just using Excel basics :
    an easy worksheet formula at beginner level
    which is more automatic than launching any code …​
  4. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    Hi.

    The uploaded workbooks are just examples and may be pre-formated by mistake.

    Nothing needs to be created in Workbook2, just check for matching headers.

    Does it matter where the code is located? If that is the case Workbook2 will be best.

    If Excel basics can help me solve the problem that's just great :)
  5. Marc L

    Marc L Excel Ninja

    Messages:
    4,183

    You can use an easy VLOOKUP formula.
    In case of no match, combine with IFERROR …​
  6. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    That is just what I have been looking for!

    Will VLOOKUP also do the trick if I have 3 columns and want the search criteria to match both column 1 and 2?

    Column 1: Parameter name
    Column 2: Result nr
    Column 3: Display Value

    Is Vlookup able to differentiate between Parameter name "A" Result Nr "0" Display Value "1" and Parameter name "A" Result Nr "1" Display Value "1,5"?

    I have uploaded a revised workbook1 to illustrate the example.

    Attached Files:

  7. Marc L

    Marc L Excel Ninja

    Messages:
    4,183

    So what should be workbook #2 result according to the revised #1 ?
    As actual can't match with this revised version …​
  8. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    Sorry. I have now uploaded workbook1 and workbook2 revised and matching.

    Attached Files:

  9. Marc L

    Marc L Excel Ninja

    Messages:
    4,183
    So according to your last attachment
    it just looks like a Copy & Transpose :​
    Code (vb):
    Sub Demo1()
        With ThisWorkbook.ActiveSheet
            F$ = .Parent.Path & "\workbook1 revised.xlsx"
            If Dir(F) = "" Then Beep: Exit Sub
            A$ = .[A1].Address(External:=True)
            W$ = .Name
        End With
        With GetObject(F)
            .Worksheets(W).[A1].CurrentRegion.Copy
             Range(A).PasteSpecial Transpose:=True
            .Close False
        End With
            [A1].Select
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
  10. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    It is basically just "copy and transpose" i'm looking for, but I need a code that applies to different outputs or days. See attached workbooks below with day1-3.
    Day1 contains full dataset.

    Day2 output is missing Display Value for Parameter C+D+E Resultnr. 1.

    Day3 output is missing Display Value for Parameter A-E Resultnr. 1 and Parameter F-L Resultnr. 0.

    Is it possible to code this?

    Attached Files:

  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,183
    It's the classic issue when explanation & attachment
    do not reflect the real workbook …

    Last try according to your last attachment, as a starter :​
    Code (vb):
    Sub Demo2()
             Dim F$, oWs As Worksheet
             F = ThisWorkbook.Path & "\workbook1 revised.xlsx"
             If Dir(F) = "" Then Beep: Exit Sub
        With GetObject(F)
            For Each oWs In ThisWorkbook.Worksheets
                .Worksheets(oWs.Name).UsedRange.Copy
                 oWs.[A1].PasteSpecial Transpose:=True
            Next
                .Close False
        End With
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
  12. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    Hi Marc.

    Your code works just as intended based on the workbooks that I uploaded and I do apologize for making this such a complicated matter. I hope that you will try to help me out one last time.

    I have now uploaded the full workbook with all sensitive data removed (n/a). I need the code to match cell names (parameterID and ResultNo) in Workbook1+2 and only copy+transpose respective DisplayValue. Whenever there is a no-match for ParameterID and ResultNo between Workbook1+2 the code should write "." in row 3 (DisplayValue) Workbook2.

    Workbook1 contains new data each day which needs to be transposed and overwrite data in row 3 Workbook2 (that is DisplayValue).

    Workbook2 revised contains fixed values for row 1 (parameter ID) and row 2 (ResultNo) which never changes. Row 3 (DisplayValue) contains new data each day and needs to be overwritten. Some cells are left blank (eg AX) on purpose.

    The Vlookup function almost did the trick but it didn't manage to lookup / check 2 columns (parameterID AND resultno) before returning DisplayValue

    For the sake of simplicity, I have named the clients data Day1 + Day2, but in practice I will only work in Day1 sheets in Workbook1+2 by simply overwriting data for each new client. Therefore, the code only needs to apply for Day1 sheet in each workbook, but it needs to take into account missing data.

    For example (new uploads):

    Workbook1 day1 contains all data and a simple copy/transpose function is sufficient to extract the DisplayValue to Workbook2.

    Workbook2 day2 however is missing all data for Parameter A-G / ResultNo1 and Parameter W / ResultNo0. The code needs to take this into account before copying and transposing DisplayValue and at the same time fill the missing cells in Workbook2 Day1 with ".".

    Hope this makes sense to you :)

    Attached Files:

  13. Marc L

    Marc L Excel Ninja

    Messages:
    4,183
    Now it's a new question as you never mentioned it like this …
    As initial request is yet solved, as I can't waste time to trash previous code
    each time you add something you forgot to explain,
    as I don't see any code attempt in your side,
    wait if someone else can try to help …

    If no answer after a couple of days, first well think about your need,
    do not forget anything, then create a new thread for your new need
    with a crystal clear & complete explanation and attachment as
    the best initial explanation and attachment, the better answer …
  14. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    I really appreciate the time and effort you put into this work and I do apologize again. I'm new to VBA and continue to learn as we speak (hence no code attempt). This is also the reason for the new questions as I didn't know it was possible to create a VBA code this complicated.

    The latest workbooks I uploaded are the final ones, and I do hope someone is able to solve the problem :)


    Best regards,
    Frederik
  15. Marc L

    Marc L Excel Ninja

    Messages:
    4,183

    At least explain what to do with duplicates in workbook #1 ?!

    Poor attachment, poor code …​
  16. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    Here is a better explanation of the problem with workbooks attached.


    Workflow:

    1. Program data is outputted as .xml which is opened in Excel and transferred to Workbook1 day1. Workbook1 day1 measurements (DisplayValue) are then copied and transposed overwriting existing data (DisplayValue) in Workbook2 day2.


    2. As of now, I do manually overwrite DisplayValue in workbook2 for each new client, but I hope to make this automatic.


    3. Workbook1 contains all available measurements for 1 client named A-G and A-OOOOOOOO under ParameterID sorted by Resultnr. Their respective values are found in column I called DisplayValue. Row 1 is the only fixed row containing variables names.


    4. Workbook2 contains a fixed row1 (ParameterID) and row2 (Resultnr.) and a variable row 3 (DisplayValue) in which measurements are being copy/transposed overwriting existing data from Workbook1.


    5. Some of the measurements are measured twice (A-G) and therefore sorted by resultnr. Resultnr. 0 equals the first measurement and resultnr. 1 equals the second measurement.


    6. Day1: Shows a client with all measurements available in Workbook1 and a simple copy / transpose / overwrite function of the 202 measurements/cells in column I (Displayvalue) to Workbook2 row 3 (DisplayValue) will work perfectly fine.


    7. Day2: However, sometimes some of the measurements aren't available for a specific client. For example, no data are available for ParameterID A-G result nr. 1 and B result nr. 0 on day2 in Workbook1 Day2 (and will therefore not show up in Workbook1) resulting in a total of 194 available measurements/cells.


    8. Whenever data are missing (DisplayValue) in Workbook1 the Displayvalue in Workbook2 should always state “.”. Using the example above, the DisplayValue/cells for ParameterID A-G resultnr. 1 and B resultnr. 0 in workbook2 should be overwritten with a “.”. This is why I need something similar to a Vlookup function that matches search criteria to both ParameterID and Resultnr.


    9. Some measurements under Displayvalue are intentionally left blank by the output program and should stay this way.


    10. I have attached Workbook1 and Workbook2 to illustrate the problem.

    Day1 shows a client with all data available.

    Day2 shows a client missing some data.


    11. Note that day2 is only shown to improve the understanding of the problem. Day1 worksheets are the only ones being used in real life and the code only needs to apply to Workbook1 Day1 and Workbook2 Day1.

    Attached Files:

  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,183
    It would be smarter to directly work with source data xml file,
    avoiding workbook1 …

    DisplayValue can't miss but a key yes …


    Still the same with your last workbook1 attachment :
    Considering ParameterId and ResultNo as keys,
    is it normal the 12 duplicates in Day1 worksheet ?!
    For example the key XXXXXX0 has 2 DisplayValue in rows #134 & 160 …
  18. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    1. It would indeed be much easier to skip Workbook1 and work directly from the xml file. However, the xml is named after the client and the ParameterID/Resultnr/DisplayValue columns differs from client to client meaning the code has to fetch data from a column named "DisplayValue" instead of always fetching data from column I as in workbook1. If it is possible to extract data directly from the xml file this would be much preferred.

    2. That is correct. Keys can be missing but DisplayValue will always be there.

    3. I have now removed all duplicates except A-G resultnr1 A-G resultnr0.

    Attached Files:

  19. Marc L

    Marc L Excel Ninja

    Messages:
    4,183
    It should be a great idea to remove duplicates in workbook2 !
    Anyway, all those workbook2 duplicates will receive
    the same unique value from workbook1
    according to your attachment as a last demonstration :​
    Code (vb):
    Sub Demo3()
        Dim F$, oDic As Object, L&
            F = ThisWorkbook.Path & "\workbook1 revised.xlsx"
            If Dir(F) = "" Then Beep: Exit Sub
            Set oDic = CreateObject("Scripting.Dictionary")
        With GetObject(F).Worksheets(1).[A1].CurrentRegion
            For L = 2 To .Rows.Count
                F = .Cells(L, 1).Value & .Cells(L, 4).Value
             If oDic.Exists(F) Then F = "#": Exit For
                oDic(F) = .Cells(L, 9).Value
            Next
                .Parent.Parent.Close False
        End With
    If F = "#" Then
        MsgBox "Duplicate in row #" & L, vbExclamation, " workbook1 Day1 :"
    Else
        With Range("DAY1!A1").CurrentRegion
            For L = 2 To .Columns.Count
                F = .Cells(1, L).Value & .Cells(2, L).Value
             If oDic.Exists(F) Then .Cells(3, L).Value = oDic(F) Else .Cells(3, L).Value = "¤"
            Next
        End With
    End If
                oDic.RemoveAll
            Set oDic = Nothing
    End Sub
    [​IMG] [​IMG] Do you like it ? [​IMG] [​IMG] Thanks to click on bottom right Like !

  20. Frederik Grund

    Frederik Grund New Member

    Messages:
    11
    Hi Marc.

    The code is working. Thank you so much for the time and effort you put into this project.

    Best regards,
    Frederik

Share This Page