• 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 from one Excel Workbook to another automatically

Frederik Grund

New Member
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
 

Attachments

Marc L

Excel Ninja
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 ?
 

Marc L

Excel Ninja

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 …​
 

Frederik Grund

New Member
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 :)
 

Frederik Grund

New Member
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.
 

Attachments

Marc L

Excel Ninja

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

Marc L

Excel Ninja
So according to your last attachment
it just looks like a Copy & Transpose :​
Code:
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 !
 

Frederik Grund

New Member
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?
 

Attachments

Marc L

Excel Ninja
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:
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 !
 

Frederik Grund

New Member
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 :)
 

Attachments

Marc L

Excel Ninja
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 …
 

Frederik Grund

New Member
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
 

Marc L

Excel Ninja

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

Poor attachment, poor code …​
 

Frederik Grund

New Member
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.
 

Attachments

Marc L

Excel Ninja
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.
It would be smarter to directly work with source data xml file,
avoiding workbook1 …

8. Whenever data are missing (DisplayValue) in Workbook1
DisplayValue can't miss but a key yes …


Still the same with your last workbook1 attachment :
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.
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 …
 

Frederik Grund

New Member
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.
 

Attachments

Marc L

Excel Ninja
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:
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
Do you like it ?
Thanks to click on bottom right Like !
 
Top