+ Reply to Thread
Results 1 to 122 of 122

Compare and Find missing rows in two sheets with different Headings and Formats

  1. #1
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Compare and Find missing rows in two sheets with different Headings and Formats

    Hi, Plz help me I am trying to compare 2 workbooks: wb1 et wb2. I want to import CSV file (wb1) in the excel file having vba code and compare it with wb2, but the code didn't work at all! Where is the problem in my code?
    Attached Files Attached Files
    Last edited by zbor; 06-17-2016 at 09:53 AM.

  2. #2
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Can someone help me plz!

  3. #3
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Your Function, Get_Header_Dico(ByVal header As Variant, ByVal header_line As Long) As Dictionary, appears to be used twice by your main program, Sub Find_Differences().
    The first time it takes in data1 as header
    The second time it takes in data 2 as header

    You have declared data1, data2 by
    Dim data1, data2
    However they do not appear to be Set or used in any other way before the Function calls.
    So they are Empty.
    Hence the Fuction errors at this line
    For i = LBound(header, 2) To UBound(header, 2)
    as you cannot have an
    LBound
    or
    UBound
    of an Empty Variant variable.

    I assume you wanted data1 and data2 to be Arrays. If they are never filled then they are not Arrays. They are Empty Variant variables.
    I get then the error of Type incompatibility, ( not as you suggested "Empty array error" ). This makes sense, as UBound or LBound refers to an Array. Your variables data1 and data2 are not Arrays. In your code they are Empty Variant variables.
    Last edited by JackSheet_1; 04-18-2016 at 09:26 AM.

  4. #4
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Thanks but how can I correct it?
    Last edited by shinpencil; 04-18-2016 at 09:48 AM.

  5. #5
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    So how can I correct it?


    You must make those variables Arrays containing the data you wish them to have..

  6. #6
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    How can I do if array contain the alphabet instead of numbers like "0 to 100"?
    Last edited by shinpencil; 04-18-2016 at 10:12 AM.

  7. #7
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    How can I do if array contain the alphabet instead of numbers like "0 to 100"?
    Currently you are making no attempt what so ever to create those Arrays. So "instead of" makes no sense???

    Currently you just declare those variables as Variant and not as Arrays. ( Arrays() ).
    If you creat them ( as Arrays ) by some code, than they will become types to suit the data you give them.

    _..............

    Which data should be in those Arrays?
    Last edited by JackSheet_1; 04-18-2016 at 11:08 AM.

  8. #8
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Post Re: Compare 2 workbooks - Empty array error

    Those arrays contain data in multiple columns including numbers and letters, that's why it's difficult to me to declare it. I declare it like that but it didn't work
    ReDim data1(header("Transaction Type"), header("ISIN Code"), header("NAV Date"), header("Value Date"), header("Investment Type"), header("Share Nb."), header("Fund Amount (Client Cur.)"))
    Last edited by shinpencil; 04-19-2016 at 04:21 AM.

  9. #9
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    Those arrays contain data in multiple columns including numbers and letters, that's why it's difficult to me to declare it. .....)
    Hi shinpencil
    Quote Originally Posted by shinpencil View Post
    Those arrays contain data in multiple columns including numbers and letters, that's why it's difficult to me to declare it.
    I think because of the language barrier you are not able to understand what I have been trying to say. Sorry I could not explain better ( or speak in French ! ).
    I try again.
    Possibly there are two issues here.



    _1 ) Declaring Arrays ( "Dim" - ing )
    Your delcaration such as
    1a):
    Dim data1
    is the same as
    Dim data1 As Variant ( VBA asigns Variant for you in this case - this is the default declaration done for you by VBA when you give none specific )
    This is fine if you want to create an Array with different types ( numbers and Letters, 0, 2, A, Anything...etc. )
    A Variant variable can become an Array containing different Types.

    This alternative is also OK
    1b):
    Dim data1() As Variant
    This declares an Array of Variant Types ( The extra () means data1 is an Array )
    This is also fine if you want to create an Array with different types ( numbers and Letters, 0, 2, A, Anything... ( String or Number ( Long, Double etc ) Types etc..). )
    You are here declaring the Array Element Types as Variant, so they may become String or Number Types etc..

    1a) and 1b) are almost identical
    I prefer 1b) as it reminds me that I am dealing with Arrays. Occaisionally there may be a good reason to use 1a) or 1b), but often it is just personal preferrence.
    _..........

    _2) Creating an Array ( data1 = ______ )
    In the codes in the Files you supplied I do not see at all any of the Arrays to which you are referring to. So it makes no sense for you to talk about the contents of an Array or Arrays that do not appear to exist.

    the main point is again 2) - you are not making any attempt anywhere that I can see to create any of those Arrays...
    I can see no
    data1 = ____
    anywhere in your codes !!!!

    Jack

    P.s. Once again I ask:
    What / where is the data you wish to put in Arrays
    Last edited by JackSheet_1; 04-28-2016 at 01:13 PM. Reason: Typos

  10. #10
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    How could I write it if the array contains multiple columns? I declare the array like below but it didn't work!
    Dim data1(header("Transaction Type"), header("ISIN Code"), header("NAV Date"), header("Value Date"), header("Investment Type"), header("Share Nb."), header("Fund Amount (Client Cur.)")) As Variant

  11. #11
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil

    It seems to me You are missing the very basic understanding of Arrays and / or VBA altogether.
    It is difficult for me to really help as it sounds like the original codes in the files you supplied were not written by you. Forgive me if I am wrong, but it appears you have no idea what is going on or how to write VBA, at least as regards Arrays.
    You need to do , for example, a Google search ( in your language ) on the basics of VBA and Arrays. I cannot teach you all that from this distance in the limited size of a Forum Thread!!! And you would likely need more time to translate what I try to say into your language than the time you need to look for learning material in your own language!!
    ( _.....This is a good You Tube Video series, for example, ( but it is in English! )
    https://www.youtube.com/watch?v=ABXP...S5k4zsvnu2mkJC
    Here the Array Videos from that searies:
    https://www.youtube.com/watch?v=e47F...2mkJC&index=18
    https://www.youtube.com/watch?v=t07y...S5k4zsvnu2mkJC

    http://www.mrexcel.com/forum/excel-q...ml#post4379209

    _......_ )
    _.....................................................

    But I do my best once again to help.

    _1) Dim ing or Declaring an Array
    This prepares the Variable to allow you to “fill it”.

    For example (1i)
    Dim data1(1 To 3, 1 To 3) As Variant
    This is a static Array ( fixed size ) whose elements will be Variant Types. As I said before, this allows the elements of the Array to be Numbers, Letters, - in fact almost anything – The elements themselves can even be Arrays!!!!.
    (The size of the Array here is 3 x 3 = 9 Elements) You cannot change the size later of a Static Array

    For example (1ii)
    Dim data1() As Variant
    This is a dynamic Array ( undetermined size ) whose elements will be Variant. As I said before, this allows the elements of the Array to be Numbers, letters, - in fact almost anything – The elements themselves can even be Arrays!!.
    Change size of Dynamic Array
    For Example,
    ReDim data1(1 to 3, 1 to 3)
    _ - This will change the size of a Dynamic Array to be 3 x 3 = 9 Elements. This changes the size, but not the Type. You can change the size again later in the code if you wish.
    _.................................

    _2) Creating an Array ( data1 = ______ )
    _2(i) For a static Array you would normally assign each element with an assigning line like
    Data1(1,1) = “Anything”
    Data1(1,2) = 3
    Etc.
    You might do this in a loop to speed things up.

    _2(ii) For a dynamic Array you would normally do this using the VBA .Value Property, which when applied to a Spreadsheet Range ( of greater than one cell ) will return a field of Elements of Variant Types which may be assigned directly to an Array.
    For example, for your “Find differences.xlsm” File there are two such code lines in the Code i give at the end of this Thread which return you an Array which looks like this:

    Using Excel 2007
    MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. Investment Type Fund Amount (Client Cur.) Client Cur.
    Redemption OUT
    5703770
    IE00B9D
    05.04.2016
    12.04.2016
    21
    Unit
    2079.4
    EUR
    Redemption OUT
    5739748
    IE00B9D
    05.04.2016
    12.04.2016
    44.2033
    Unit
    4443.44
    GBP
    Subscription IN
    5740386
    IE00B9D
    05.04.2016
    12.04.2016
    366.0865
    Amount
    36800
    GBP
    Subscription IN
    5748625
    IE00B9D
    05.04.2016
    12.04.2016
    140
    Unit
    15429.92
    USD
    Subscription IN
    5751430
    IE00B9D
    05.04.2016
    12.04.2016
    100
    Unit
    11021.37
    USD
    Subscription IN
    5772817
    IE00B9C
    05.04.2016
    12.04.2016
    350
    Unit
    35182.94
    GBP
    Subscription IN
    5778029
    IE00B9C
    05.04.2016
    12.04.2016
    53
    Unit
    5248
    EUR
    Subscription IN
    5785485
    IE00B9C
    05.04.2016
    12.04.2016
    205
    Unit
    20607.15
    GBP
    Redemption OUT
    5793799
    IE00B8B
    05.04.2016
    12.04.2016
    1462.86
    Unit
    145204.4
    EUR
    Subscription IN
    5794037
    IE00B8B
    05.04.2016
    12.04.2016
    5037.245
    Amount
    500000
    EUR
    Redemption OUT
    5794101
    IE00B8H
    05.04.2016
    12.04.2016
    525.9238
    Unit
    50659.56
    USD
    Redemption OUT
    5794213
    IE00B8B
    05.04.2016
    12.04.2016
    2518.623
    Amount
    250000
    EUR
    Redemption OUT
    5794491
    IE00B8B
    05.04.2016
    12.04.2016
    2014.898
    Amount
    200000
    EUR
    Subscription IN
    5794590
    IE00B9C
    05.04.2016
    12.04.2016
    102
    Unit
    10253.32
    GBP
    Redemption OUT
    5794658
    IE00B9C
    05.04.2016
    12.04.2016
    180
    Unit
    18094.09
    GBP

    _...

    Here the code to create that Array:

    Please Login or Register  to view this content.
    Last edited by JackSheet_1; 04-19-2016 at 06:55 PM.

  12. #12
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Thank you so much but I still have get mismatch error at the line "For i = LBound(header, 2) To UBound(header, 2)"...

  13. #13
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil
    Quote Originally Posted by shinpencil View Post
    Thank you so much but I still have get mismatch error at the line "For i = LBound(header, 2) To UBound(header, 2)"...
    I assume you have made some attempt to change the code so that the Arrays you want are created and filled?

    So

    _1) Please Post your new modified code and / or Files

    _2) I ask again for you to try to explain, as best you can, what specific data you want in those Arrays. By this I mean which Spredsheet Range or Ranges values should be in the Arrays?

    Jackalane
    Last edited by JackSheet_1; 04-20-2016 at 03:22 AM.

  14. #14
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    It's the same files but it didn't work on my pc somehow
    data1 should be in workbook1 range ("A12:AZ100"), data2 in workbook 2 range("A1:AZ100")

  15. #15
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    It's the same files but it didn't work on my pc somehow
    data1 should be in workbook1 range ("A12:AZ100"), data2 in workbook 2 range("A1:AZ100")
    So please supply the modified code you have written where you are attepting to create and Fill those Arrays.

    (_...... Please use code tags [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]
    Please read the forum rules***** ( especially rule 3 ) : http://www.excelforum.com/forum-rule...rum-rules.html
    and practice posting here: ( you must be logged in to use this Sub Forum) http://www.excelforum.com/development-testing-forum/
    _.......)

    Or

    Supply new files with your new modified code in

    Jack

    _......
    ******P.s.:
    Edit: Forum Rules are also given in French:
    http://www.excelforum.com/forums-rul...-francais.html
    Last edited by JackSheet_1; 04-20-2016 at 04:39 AM. Reason: Found Rules in French!!!

  16. #16
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Thanks I'm new to the forum that's why! I just changed a bit and wrote it like that:
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil,
    Quote Originally Posted by shinpencil View Post
    Thanks I'm new to the forum that's why! ....
    You are welcome
    _...........................................


    Quote Originally Posted by shinpencil View Post
    I just changed a bit and wrote it like that:
    Please Login or Register  to view this content.
    _1) I assume Worksheets "Board" is "sheet1" in the original file "Find differences.xlsm" that you supplied? Is that correct. (_.... Note that this alternative would always refer to the first sheet tab counting from the left, regardless of its name:
    Worksheets.Item (1)
    so that would be the same as
    Worksheets("Board")
    if "Board" was the name of your first sheet
    _.....)

    _2) Could you please possibly supply new Files with reduced data, that is to say smaller Ranges? ( Ranges A12:AZ100 and A1:AZ100 are a bit difficult to work with, but if we get the code working on smaller ranges then it should still work on lerger ranges )

    _3) Please supply the full modified code, ( or include it in the Files if you supply them as i asked for in _2)

    ( _4 Did you write all the original codes? Or are you trying to use someone elses codes?? )


    Jack
    Last edited by JackSheet_1; 04-20-2016 at 06:56 AM.

  18. #18
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Yes "Board" is sheet1 in the original file. And it's true that someone help me to write the code but it didn't work anymore so I need to modify it.
    A1:AZ100 is all data in the sheet but the data I need is in ranges A1: Z70. I am trying to declare it as you told but I always got the same error, I think "header" is also empty.
    The full code is below:
    Please Login or Register  to view this content.
    Last edited by shinpencil; 04-20-2016 at 09:19 AM.

  19. #19
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil,
    Thanks for the new code.

    I think I have gone a long way to getting your code to work.
    So I explain the modifications that i have made, then Post the “working” Code.
    Note i am using the original Files that you uploaded in post #1 , and i am modifying the original codes, as you have done some additional changes at the end of Sub Find_Differences() which are not relevant to the original problem....
    _.............................................................

    Explanations:

    _1) You are confusing Workbooks and Worksheets.

    (_.. These code lines are wrong:
    Let data2() = wb2.Range("A1").CurrentRegion.Value
    Let data2() = wb2.Range("A1:AZ100").Value
    wb2 is a Workbook, not a Worksheet, so it can not have a Range
    You need
    ws2.Range
    where ws2, I assume, is the first Worksheet in your Workbook 2 _....)

    _1(b)
    In the small code i gave you i was demonstrating just as one example the Range Values Capture for what i assumed was one of the ranges you wanted, that being Range A12:K27 in the Workbook in which the code was., hence.

    ws = ThisWorkbook.Worksheets("sheet1")

    This could also have been written as

    ws = wb1.Worksheets("sheet1")
    _ . this is because in your code you have already**
    Set wb1 = ThisWorkbook
    _......................................

    _1(c)

    Better to avoid confusion would be

    Dim ws1 As Worksheet

    Let ws1 = wb1.Worksheets("sheet1")

    _........

    _1(d)
    You are dealing in the full code with 2 worksheets ( I think ? ) from two different Workbooks.

    So at the start of your code, this would be sensible:

    Please Login or Register  to view this content.
    _...
    _1(e)
    Then later in your code at the appropriate places you need
    Set wb1 = ThisWorkbook ( you already have this**)
    Set wb2 = Workbooks(sBook) ( you already have this also !!!! )

    And also you need at the appropriate places in your code

    Set ws1 = wb1.Worksheets.Item(1)
    And
    Set ws2 = wb2.Worksheets.Item(1)

    _...............................

    _2 ) I gave you two alternatives to capture your range.
    Let data1() = ws.Range("A12").CurrentRegion.Value
    Let data1() = ws.Range("A12:K27").Value

    You only need 1 !!!!
    Let data1() = ws.Range("A12").CurrentRegion.Value is better if you know where your range starts, but its size may change.
    Let data1() = ws.Range("A12:K27").Value is better if you always have a Range of the same size and position. (and if you have empty columns ###)
    _...................................

    _2(b) At appropriate places in your code you need code lines of, for example, this form

    Let data1() = ws1.Range("A12").CurrentRegion.Value
    And
    Let data2() = ws2.Range("A1:P17").Value
    Note the in the last A1:P17 is appropriate if you have empty columns###, as the CurrentRegion property will not capture past an isolating empty column.
    _.........................................


    So i have done all the above modifications to your code, Sub Find_Differences().
    The important Code lines are shown thus ' ' ' ' ' '

    The code now progresses and does not error at
    "For i = LBound(header, 2) To UBound(header, 2)".
    This is because header now is an Array ( data1() ) or data2() )

    The code appears now to “work” without erroring with the original Files you supplied in Post #1

    It appears to give this result in the second Sheet

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Redemption IE00B9D 05.04.2016 12.04.2016 Unit
    21
    2
    Redemption IE00B9D 05.04.2016 12.04.2016 Unit
    44.2033
    3
    Subscription IE00B9D 05.04.2016 12.04.2016 Amount
    36800
    4
    Subscription IE00B9D 05.04.2016 12.04.2016 Unit
    140
    5
    Subscription IE00B9D 05.04.2016 12.04.2016 Unit
    100
    6
    Subscription IE00B9C 05.04.2016 12.04.2016 Unit
    350
    7
    Subscription IE00B9C 05.04.2016 12.04.2016 Unit
    53
    8
    Subscription IE00B9C 05.04.2016 12.04.2016 Unit
    205
    9
    Redemption IE00B8B 05.04.2016 12.04.2016 Unit
    1462.86
    10
    Subscription IE00B8B 05.04.2016 12.04.2016 Amount
    500000
    11
    Redemption IE00B8H 05.04.2016 12.04.2016 Unit
    525.9238
    12
    Redemption IE00B8B 05.04.2016 12.04.2016 Amount
    250000
    13
    Redemption IE00B8B 05.04.2016 12.04.2016 Amount
    200000
    14
    Subscription IE00B9C 05.04.2016 12.04.2016 Unit
    102
    15
    Redemption IE00B9C 05.04.2016 12.04.2016 Unit
    180
    16
    17
    Subscription IE00B9J 05.04.2016 12.04.2016 Unit
    82
    18
    Redemption IE00B9J 05.04.2016 12.04.2016 Unit
    1750
    19
    Redemption IE00BR8 05.04.2016 12.04.2016 Amount
    7000000
    20
    Redemption IE00BR8 05.04.2016 12.04.2016 Unit
    1500
    21
    Subscription IE00BR8 05.04.2016 12.04.2016 Amount
    2484.33
    22
    Redemption IE00BR8 05.04.2016 12.04.2016 Amount
    76527.2
    23
    Subscription IE00BS7 05.04.2016 12.04.2016 Amount
    14900.06
    24
    Subscription IE00BS7 05.04.2016 12.04.2016 Amount
    9999.9
    25
    Redemption IE00BS7 05.04.2016 12.04.2016 Unit
    29
    26
    Redemption IE00BS7 05.04.2016 12.04.2016 Amount
    26873.45
    27
    Subscription IE00BS7 05.04.2016 12.04.2016 Amount
    17500.02
    28
    Subscription IE00BS7 05.04.2016 12.04.2016 Amount
    50000
    29
    Subscription IE00BS7 05.04.2016 12.04.2016 Unit
    530.57
    30
    Redemption IE00BS7 05.04.2016 12.04.2016 Unit
    62.57
    31
    Redemption IE00BS7 05.04.2016 12.04.2016 Unit
    1000
    32
    Subscription IE00BRJ 05.04.2016 12.04.2016
    1000
    Sheet2

    Jack

    ( _.. code to follow in next Post, Post # 20 )
    Last edited by JackSheet_1; 04-20-2016 at 09:42 AM.

  20. #20
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Here the “working” codes for Post # 19:

    Function Get_Header_Dico(____________
    and
    Sub Find_Differences()

    ( Note I have also added a check in Function Get_Header_Dico to not report duplicate if you have multiple empty Column headings )

    Please Login or Register  to view this content.
    Au Revoir
    _... Jack ...
    ..

  21. #21
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Omg thanks a lot! You really are my lifesaver!
    Nice to meet you here. Au revoir and see you next time maybe

  22. #22
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    Omg thanks a lot! You really are my lifesaver!
    Nice to meet you here. Au revoir and see you next time maybe
    Glad we got there.
    Thanks for the feedback.


    Goodbye.
    Jack

  23. #23
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    P.s.

    please mark Thread as solved, Thanks:
    Last edited by JackSheet_1; 04-21-2016 at 09:01 AM.

  24. #24
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Hello, I have a problem with the code, the result I got is false! Could you help me please!

  25. #25
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    Hello, I have a problem with the code, the result I got is false! Could you help me please!

    Hi shinpencil
    You really must try to explain in much more detail what the problem is!!!
    Then maybe i can take a look tomorrow

    Jack

  26. #26
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    I tried to run the code, it supposed to have no difference between 2 files but I got 5 lines of difference which is very weird...

  27. #27
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    I tried to run the code, it supposed to have no difference between 2 files but I got 5 lines of difference which is very weird...
    I will need to see the data used, to produce the results, and the in the results the lines which you believe are in error should be highlighted.

    I cannot really give other help "working blind" at this distance, other than to say the obvious ,
    _ . - such as checking exact spellings, UPPER and lower case, and check that there are no extra spaces that could make a value different literally to that to which it appears at first glance..

    Jack

  28. #28
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    The files are below. It supposed to have no difference between wb1 and wb2 but there is
    Attached Files Attached Files
    Last edited by shinpencil; 04-25-2016 at 05:45 AM.

  29. #29
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil,

    I am really struggling again too understand !

    You gave me a File
    “Compare Caceis.xlsm”
    And
    “wb2.xls”

    You also gave me a File
    “Wb1.csv‎"
    I think I do not need “Wb1.csv‎" because the data from there is already in file “wb2.xls” ???

    _......................................



    In File “Compare Caceis.xlsm” sheet “Board” It has lots Data in it.

    In File “Compare Caceis.xlsm” sheet Differences it has this in it

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    2
    Missing order in Lomax
    3
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    261
    4
    Subscription IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    185
    5
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    327
    6
    Subscription IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    4000
    7
    Redemption IE00B9CB6D86 26/04/2016
    05.04.2016
    Unit
    464.4591
    8
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Amount
    51000
    Differences

    _...........................................................

    Now i open just Files wb2.xls and “Compare Caceis.xlsm”

    Then I run code Sub Find_Differences() from File “Compare Caceis.xlsm”

    Then sheet “Board” in File “Compare Caceis.xlsm” changes to this

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    2
    Missing order in Lomax
    3
    Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    195
    4
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    61.0714
    5
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    240
    6
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    3900
    7
    Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount
    19835
    8
    Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit
    177.7072
    9
    Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit
    3946
    10
    Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit
    261
    11
    Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit
    2399
    12
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    230
    13
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    495.5411
    14
    Subscription IE00B905SX62 26.04.2016 04.05.2016 Unit
    185
    15
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    210
    16
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    45
    17
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    85
    18
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    700
    19
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    167
    20
    Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit
    327
    21
    Subscription IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    27.5395
    22
    Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    58.181
    23
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    13.503
    24
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    23.466
    25
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    209.7632
    26
    Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    32
    27
    Subscription IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    44.5259
    28
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    50000
    29
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    165000
    30
    Subscription IE00B905SX62 26.04.2016 04.05.2016 Unit
    4000
    31
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    464.4591
    32
    Subscription IE00B8BS6228 26.04.2016 04.05.2016 Amount
    3330.44
    33
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    15.323
    34
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    40200
    35
    Subscription IE00BRJG7170 26.04.2016 04.05.2016 Unit
    2480
    36
    Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit
    3.6405
    37
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    15000
    38
    Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit
    439
    39
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    605.998
    40
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    875
    41
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    29
    42
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    9404.3
    43
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    41.0542
    44
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    72.5862
    45
    Redemption IE00BRJG6Z44 26.04.2016 04.05.2016 Amount
    82700
    46
    Redemption IE00B905SX62 26.04.2016 04.05.2016 Amount
    51000
    47
    Subscription IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    108
    48
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    8.191
    49
    Subscription IE00BRJG7170 26.04.2016 04.05.2016 Unit
    14700
    50
    Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit
    213
    51
    Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit
    1000
    52
    Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit
    798.7968
    53
    Redemption IE00BRJG6Z44 26.04.2016 04.05.2016 Unit
    8358
    54
    Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    5.737
    Differences

    _.............................



    But i have no idea what it is you want.

    I have no idea what should happen.

    What is the problem ???

    _.................................

    I am guessing all the time what it is you want to do.

    _.................................

    You must tell me clearly what it is that is to be done. ( What program am i supposed to run ? )
    And
    You must tell me clearly what the problem is?
    And
    You must tell me ( show ) me what you should get when you rin the macro. Tell me and show me what should happen.

    I am really confused. Sorry !!

    Jack

    ( And pleas try may be to reduce the amount of data )


    See if maybe you can post a screen shot like i did above,
    but showing what should happen when the macro is run

    See if you can learn to use these screenshot tools, for example:

    http://www.excelforum.com/suggestion...ml#post4501034

    http://www.mrexcel.com/forum/test-he...ml#post4501034
    Last edited by JackSheet_1; 04-25-2016 at 04:48 PM.

  30. #30
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Sorry I didn't explain it well. Because I need to compare 2 wb (wb1 is a csv file and wb2 is an xls file) so I use "Compare caceis" file to import csv file and modify the data (some data in wb1 are not in the same format with wb2), then find differences between 2 wb.
    It supposed to have no difference but apparently there have.

  31. #31
    Registered User
    Join Date
    12-07-2015
    Location
    Germany
    MS-Off Ver
    2007 2010 2013
    Posts
    58

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil,

    Yes, I think maybe i guessed that you compare “Compare Caceis.xlsm” sheet “Board”
    With
    “wb2.xls” sheet 1

    _............................

    Sorry but the rest I still do not understand.

    Quote Originally Posted by JackSht_1 View Post
    .......
    You must tell me clearly what it is that is to be done. ( What program am i supposed to run ? )
    And
    You must tell me clearly what the problem is!!!
    And
    You must tell me ( show ) me what you should get when you run the macro. Tell me and show me what should happen.
    ..........
    _.................................................

    I have no idea what this means of from where it come.

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    2
    Missing order in Lomax
    3
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    261
    4
    Subscription IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    185
    5
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    327
    6
    Subscription IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    4000
    7
    Redemption IE00B9CB6D86 26/04/2016
    05.04.2016
    Unit
    464.4591
    8
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Amount
    51000
    Differences

    What is this. What does it mean ?
    How is it related to your problem ?

    _...................................................

    I am very sorry but i do not think I will be able to help if you cannot explain and show better
    I am very sorry.
    Maybe you must find somebody who can translate to English for you
    Most of what you say is totally in understandable.
    Up until now i have mostly just guessed

    Jack

    Maybe i try tomorrow a very last time to help

    You must Try very hard in the meantime to explain better

    and
    show me

    A better file or files showing

    Before

    and
    After

    or screenshots

    http://www.excelforum.com/suggestion...ml#post4501034

    http://www.mrexcel.com/forum/test-he...ml#post4501034
    Last edited by JackSheet_1; 04-25-2016 at 05:44 PM.

  32. #32
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Yes this is the orders in wb1 which are missing in wb 2, because I only need the difference in wb1 so I erased the different part of wb 2.
    Sorry you need to erase the table that you mentioned above, it is related to others files
    The problem is that it supposed to have no difference in 2 files I sent you
    Last edited by shinpencil; 04-26-2016 at 03:49 AM.

  33. #33
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    Yes this is the orders in wb1 which are missing in wb 2, because I only need the difference in wb1 so I erased the different part of wb 2.
    Sorry you need to erase the table that you mentioned above, it is related to others files
    The problem is that it supposed to have no difference in 2 files I sent you
    _..................................

    _.........................................................

    Hi shinpencil,
    I am Alan. ( Jack, ( Jacky ) is may Wife! )

    We still do not fully understand...
    I try to help...

    _1)
    Is File “Wb1.csv‎"
    Irrelevant to the current problem?.
    We do not need it?

    _2)
    Is this Irrelevant to the current problem?.
    We do not need it?

    Row\Col
    A
    B
    C
    D
    E
    F
    2
    Missing order in Lomax
    3
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    261
    4
    Subscription IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    185
    5
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    327
    6
    Subscription IE00B905SX62 26/04/2016
    05.04.2016
    Unit
    4000
    7
    Redemption IE00B9CB6D86 26/04/2016
    05.04.2016
    Unit
    464.4591
    8
    Redemption IE00B905SX62 26/04/2016
    05.04.2016
    Amount
    51000


    _.............................

    Please if you want more help from someone:

    Either
    _a) learn to use screen shot tools
    http://www.excelforum.com/suggestion...ml#post4501034
    http://www.mrexcel.com/forum/test-he...ml#post4501034
    Or
    _b) Give me more Files and / or sheets

    and with those to show me:

    _..............

    _1) You Show me What does everything ( all relavant data in all relavent Files ) look like before you run any macro

    _2) You Tell me what should then be done ( what macro is it that should be run ?).

    _3) You Show me what you get after that macro runs. (This will be give the results you get when you run the macro which are wrong) ( I will then repeat that on the data from _1) to see if I get the same results as you )

    _4) You Show me what is right and what is wrong
    Either
    4a)
    For example like this with screenshots

    Redemption IE00B9C98164 26.04.2016 04.05.2016 Ptang
    85
    Subscription IE00B9CB586 26.04.2017 04.05.2016 Pftang
    700
    Subscription IE003837546D86 26.04.2016 04.05.2016 OLE
    4365
    Redemption IE00B9988375X62 26.04.2016 04.05.2016 wigwam
    78
    Subscription IE0047SJ09 26.04.2016 04.05.2016 biscuitbarrel
    63
    This Colour This Colour
    means Correct means wrong
    CORRECT FAUX
    Differences

    Or
    4b) you give me correct and wrong sheets in a File or Files ( upload Files again )

    4b(i)
    This is what is got with the code currently and it is wrong
    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Code Gives This But it is Wrong
    2
    Missing order in Lomax
    3
    Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    195
    4
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    61.0714
    5
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    240
    6
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    3900
    7
    Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount
    19835
    8
    Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit
    177.7072
    9
    Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit
    3946
    10
    Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit
    261
    11
    Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit
    2399
    DifferencesWrong

    _.....

    4bii)
    You Show me how everything should look like. what you want the code to give you

    Correct Sheet ( Filled in by hand by you. - You Type the results in yourself to show excactly what you want the code to give you )
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    This is what I want the code to give me
    2
    Missing order in Lomax
    3
    Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
    32
    4
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    3900
    5
    Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount
    19835
    6
    Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit
    177.7072
    7
    Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit
    3946
    8
    Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit
    261
    9
    Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit
    2399
    10
    Subscription IE00B905SX62 26.04.2016 04.05.2016 Unit
    185
    11
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
    210
    DifferencesCorrect

    _.............................................................................................


    Try to reduce the data as i have done ( also in “wb2.xls” sheet 1 ) to make it easier for us to work with




    _...........................

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  34. #34
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    1) Yes we need the wb1 because we need to compare wb1 and wb2
    2) No we don't need this result, it relate to another file
    3) Before I run any macro, there has nothing in the "Compare" file. Firstly I need to import the wb1 in the sheet 1 of "Compare" file then compare it to wb2. For 2 wb I have sent, it supposed display the difference but there have no difference at all
    Last edited by shinpencil; 04-26-2016 at 11:06 AM.

  35. #35
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil,
    I do not think I, or my Wife, can help you anymore.
    I did some tests for you on reduced data.

    I changed your data to this ( Compare___ File ( in code wb1 )

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    12
    MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. Investment Type Fund Amount (Client Cur.) Client Cur. Fund Amount (Trade cur.)
    13
    Redemption OUT
    6151784
    IE00B9DCSJ09
    26.04.2016
    04.05.2016
    195.00
    Amount
    19193.46
    EUR
    19193.46
    14
    Redemption OUT
    6174269
    IE00B9CB6D86
    26.04.2016
    04.05.2016
    61.07
    Amount
    6113.25
    GBP
    6113.25
    15
    Redemption OUT
    6179051
    IE00B9CB6D86
    26.04.2016
    04.05.2016
    240.00
    Amount
    24024.00
    GBP
    24024
    16
    Subscription IN
    6187108
    IE00B9CB6D86
    26.04.2016
    04.05.2016
    38.96
    Amount
    3900.00
    GBP
    3900
    17
    Subscription IN
    6198301
    IE00BX7RQ732
    26.04.2016
    04.05.2016
    224.00
    Amount
    19835.00
    EUR
    19835
    18
    19
    20
    Board
    _.......

    And this in file wb2.xls ( in code wb2 )

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    S/R type Order status Fund share code Fund share name Fund name Pricing Date Value Date Quantity Nature Net amount
    2
    Redemption Estimated IE00B9DCSJ09
    26.04.2016
    04.05.2016
    224
    Amount
    19193.46
    3
    Redemption Estimated IE00B9CB6D86
    26.04.2016
    04.05.2016
    2,480.00
    Unit
    6113.25
    4
    Subscription Estimated IE00BRJ
    26.04.2016
    04.05.2016
    14,700.00
    Amount
    1,501,287.48
    5
    Redemption Estimated IE00BS7
    26.04.2016
    04.05.2016
    3.64
    Amount
    369.15
    6
    Subscription Estimated IE00BS7
    26.04.2016
    04.05.2016
    798.8
    Amount
    80,999.59
    7
    Redemption Estimated IE00BS7
    26.04.2016
    04.05.2016
    439
    Amount
    44,515.48
    8
    Subscription Estimated IE00BS7
    26.04.2016
    04.05.2016
    2,399.00
    Amount
    243,263.40
    9
    Sheet1

    _..................

    Then i run the codes given at referenced at the end of this Post

    You then get these results ( in file “Compare_______ ( in code wb1 ))

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    2
    Missing order in Lomax
    3
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    6113.25
    4
    Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    24024
    5
    Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount
    3900
    6
    Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount
    19835
    Differences

    _..........

    Also i added a new sections which pastes out your two Dictionaries to a new sheet ( in file “Compare_______ ( in code wb1) ) ( which it creates the first time around ) so that you can check you data


    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    1
    key data1_Dico.Item(key) key data2_Dico.Item(key)
    2
    Redemption#IE00B9DCSJ09#26.04.2016#04.05.2016#Amount#19193.4600
    2
    Redemption#IE00B9DCSJ09#26.04.2016#04.05.2016#Amount#19193.4600
    2
    3
    Redemption#IE00B9CB6D86#26.04.2016#04.05.2016#Amount#6113.2500
    3
    Redemption#IE00B9CB6D86#26.04.2016#04.05.2016#Unit#2480.0000
    3
    4
    Redemption#IE00B9CB6D86#26.04.2016#04.05.2016#Amount#24024.0000
    4
    Subscription#IE00BRJ#26.04.2016#04.05.2016#Amount#1501287.4800
    4
    5
    Subscription#IE00B9CB6D86#26.04.2016#04.05.2016#Amount#3900.0000
    5
    Redemption#IE00BS7#26.04.2016#04.05.2016#Amount#369.1500
    5
    6
    Subscription#IE00BX7RQ732#26.04.2016#04.05.2016#Amount#19835.0000
    6
    Subscription#IE00BS7#26.04.2016#04.05.2016#Amount#80999.5900
    6
    7
    Redemption#IE00BS7#26.04.2016#04.05.2016#Amount#44515.4800
    7
    8
    Subscription#IE00BS7#26.04.2016#04.05.2016#Amount#243263.4000
    8
    DebugSheet
    _....................

    I hope that helps a little.
    Goodbye, Au Revoir
    Alan. ( and Jacky )


    Codes are here:
    Sub Find_Differences()
    http://www.excelforum.com/showthread...t=#post4372884
    _.....
    And here:
    Function Get_Header_Dico(ByVal header As Variant, ByVal header_line As Long) As Dictionary
    http://www.excelforum.com/showthread...t=#post4372887
    Last edited by Doc.AElstein; 04-26-2016 at 01:42 PM.

  36. #36
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Okay thank you and your wife anw for your help!

  37. #37
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Compare 2 workbooks - Empty array error

    shinpencil, you have asked for this thread to be deleted.

    We do not delete threads, if you are done with a thread you can Close it using he Thread Tools at the top of the page
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  38. #38
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil,
    Quote Originally Posted by FDibbins View Post
    shinpencil, you have asked for this thread to be deleted......
    _..You did not get a full answer maybe, but there may be still some useful info here, so no need to delete the Thread. Otherwise the efforts of people helping are totally lost.

    If you try to narrow down a bit / isolate the problem , reduce the test data , then you should be able to get easier at the problem. That will also aid in you getting help.
    In the end I always still guessing exactly what code to run and on which data!!

    _.....
    _ I appreciate that it is very hard for you due to the Language barrier. One last time i will try to help ( This is All similar to what i tried to say before )
    _...........

    Possibly if you still need more help, you could try again to clarify the exact problem – narrow it down, so confine to:

    _ Reduce ( and desensitise possibly ) your test data

    _ Give a code which is giving you a problem.

    _ Say specifically what data the code is running on. Show the results the code gives you that are wrong and give some hand filled in results to show what the code should give you.

    _...........
    If you are able to do that, it may be valid to say the question is not then exactly that of this thread ( Which none of us could determine anyway , lol )

    So you could “start a fresh” in a new Thread.
    Possibly post your new Thread in this Forum., as you did before, if you are able to explain a little better in English.
    Or you could try out your luck in the “Other Languages” Sub Forums
    http://www.excelforum.com/non-english-excel/
    http://www.mrexcel.com/forum/questions-other-languages/

    Important: Include a link in any new Thread to this Thread ( copied from Link in your browser window ) to confirm with Forum Rules
    HTML Code: 
    If you should start a new Thread related to this one, then if you Private Message me to give a link to the new Thread , then I will try to take another look. ( The final codes i did for you appeared to work anyway. – but as mentioned i was still unclear exactly what you wanted ! )

    Alan
    Last edited by Doc.AElstein; 04-28-2016 at 11:46 AM.

  39. #39
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    No sorry Alan and Jacky! Thank you so much for your help but I just want to delete a bit of data because some data could be confidentiels.
    In the end I managed to fix it and it worked with 2 files that I posted here, but there another problem with another file. I just want it can work well with all the files I have
    The problem is that the code I have to import the CSV file (35 columns, 80 rows in general) into Excel, it worked well at the beginning but it didn't work this time. There error in the last line. Could you help me plz?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by shinpencil; 04-28-2016 at 08:40 AM.

  40. #40
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Hi
    Quote Originally Posted by shinpencil View Post
    .....
    In the end I managed to fix it and it worked with 2 files that I posted here, ......
    It is always coutious to feedback , tell us, and share your working solution to a problem you have solved with our help
    _...___________________________________________

    Quote Originally Posted by shinpencil
    .......
    I just want to delete a bit of the data because some data could be confidential.
    Sorry if it make you misunderstanding,
    I not me misunderstanging became,
    as I guessed that might be the case, hence my recommendation always:
    Reduce ( and desensitise possibly ) your test data


    To your new problem, ( which as i suggested might of been better in a new Thread, but i give up trying to explain that again !! )....

    So Your Current problem as detailed in Post 39
    http://www.excelforum.com/showthread...t=#post4374374
    _......
    Again you are making it difficult, not giving precise information or a full code.
    But once again I try..
    you have many problems in your code given in that post #39:

    _ 1) you are using one method of three that i know of to “get at” a workbook.
    http://www.excelforum.com/excel-prog...ml#post4227458

    You chose the Third way referrenced in that above Thread. This one:
    Set WB = GetObject(__________________)

    This by default make the file invisible. ( you cannot see it ) Maybe you want that.? – But in any case it does make it difficult to see what is going on!!!

    1a) If you must use that then change this to

    Wb.Close False

    to

    Wb.Close False
    Set Wb = Nothing

    This is good practice, and you will also see that after Set Wb = Nothing the File is no longer to be seen in the VBA Project Window.

    _.....................

    _2 ) I would recommend instead you do:
    Either:
    Please Login or Register  to view this content.

    Or

    Use a different method to “get at” your file,
    http://www.mrexcel.com/forum/excel-q...xcel-file.html

    such as this:

    Please Login or Register  to view this content.
    _................................

    If you use either of those two options you will see immediately your Current problem:
    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    1
    MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. Investment Type Fund Amount (Client Cur.) This Fund Amount (Trade cur.) Last NAV Date NAV Exch. Rate client / trade
    2
    SUB IN
    6326091
    IE00B6
    20160427
    20160503
    28
    M
    340
    entire
    340
    20160422
    118.4668
    1
    3
    SUB IN
    6353232
    IE00B5
    20160427
    20160503
    60.4
    P
    71
    column
    71
    20160425
    118.1284
    1
    4
    RED OUT
    6353300
    IE00B6
    20160427
    20160504
    66
    P
    875
    is empty.
    8755.28
    20160425
    132.6557
    1
    5
    RED OUT
    6355465
    IE00B6
    20160427
    20160504
    11
    P
    1539
    So CurremtRegion
    153
    20160425
    132.6557
    1
    6
    SUB IN
    6355997
    IE00B6
    20160427
    20160503
    24
    M
    2928
    will Fail
    2928
    20160425
    118.0422
    1
    7
    8
    9
    10
    11
    12
    Yous have no Data near here !!!
    Sheet1

    _3 ) So, Look at that screen shot above and consider:

    _3a) (i)
    You are attempting to get at that data using the .CurrenRegion Property applied to the Cell A12.
    Range("A12").CurrentRegion
    I explained previously to you that
    .CurrentRegion
    Returns a new Range object of a “box” encompassing all cells “connected” to the original Range object, which is
    "A12"
    In this case. There is clearly no cells with data connected to A12 !!! so you will not “Capture” Any data. !!!

    To refer to your data Range
    You must use
    Either
    Wb.ActiveSheet.Range("A1").CurrentRegion ' ' But also, as i said before, this will not work, when as in this case you have a complete empty column which effectively “isolates” what it after the empty column
    Or alternatively
    Wb.ActiveSheet.UsedRange ‘Note for this to work the only data ( ever ) in your file should be that which you wish to “capture”
    so better for your files may be
    Please Login or Register  to view this content.
    _.....

    _3a)(ii) It is very unwise to rely on the Implicit default of a Range object in such a code line which is to return Values. You should specifically use the .Value Property thus
    Insted of
    CurrentRegion or UsedRange
    You are wise to do this:
    Currentregion.Value or UsedRange.Value

    _...................................................................


    _4) You have a typo in your. Resize Property part at the end when pasting out.
    .Resize(UBound(Arr), UBound(Arr, 2))
    Is incorrect. The following is correct
    .Resize(UBound(Arr, 1), UBound(Arr, 2))
    ( by luck this may have worked, but only in this case as
    UBound(arrIn(), 1)
    defaults to
    UBound(arrIn())


    _..........
    So making all those corrections for you in the code I give you below: ( Note I assume at the time of running the macro your Active Sheet is in the Workbook in which the macro is in, and also the Active Sheet is that in which you paste the data.)

    Please Login or Register  to view this content.
    The code given here
    http://www.excelforum.com/showthread...t=#post4374563
    now seems to work. It does not error and pastes your data as shown in next Post: Post #41

    Alan
    Last edited by Doc.AElstein; 04-28-2016 at 12:14 PM.

  41. #41
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Results For post #40 above.

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    12
    MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. Investment Type Fund Amount (Client Cur.) Fund Amount (Trade cur.) Last NAV Date NAV Exch. Rate client / trade
    13
    SUB IN
    6326091
    IE00B6
    20160427
    20160503
    28
    M
    340
    340
    20160422
    118.4668
    1
    14
    SUB IN
    6353232
    IE00B5
    20160427
    20160503
    60.4
    P
    71
    71
    20160425
    118.1284
    1
    15
    RED OUT
    6353300
    IE00B6
    20160427
    20160504
    66
    P
    875
    8755.28
    20160425
    132.6557
    1
    16
    RED OUT
    6355465
    IE00B6
    20160427
    20160504
    11
    P
    1539
    153
    20160425
    132.6557
    1
    17
    SUB IN
    6355997
    IE00B6
    20160427
    20160503
    24
    M
    2928
    2928
    20160425
    118.0422
    1
    CSVImport

    _.......................

    Code Again:
    Please Login or Register  to view this content.
    Alan
    Last edited by Doc.AElstein; 04-28-2016 at 01:38 PM.

  42. #42
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Hi, sorry for the late reply, the code worked well at the beginning but another problem appears: I have the subscript out of range error and it highlight this line
    "nature = data1(i, header("Investment Type"))"
    Could you view it for me plz?
    Last edited by shinpencil; 05-04-2016 at 09:10 AM.

  43. #43
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error



    Once again you are not giving enough information for anyone to be able to help you.
    Encore une fois, vous ne donnez pas assez d'information pour quiconque d'être en mesure de vous aider.

    If you want help
    Si vous voulez aider

    You must post the complete code that is not working
    Vous devez envoyer le code complet qui ne fonctionne pas

    You must give us test data on which to run that code. The test data should be reduced in size to the minimum to demonstrate the problem and the test data should be desensitized.
    Vous devez nous donner des données de test sur lequel exécuter ce code. Les données d'essai doivent être réduites en taille au minimum pour démontrer le problème et les données d'essai doivent être désensibilisés.

  44. #44
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Sorry here is the code. It's basically the same code I just modified it a little:
    Please Login or Register  to view this content.

  45. #45
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    I hit the debug button and got the subscript out of range error, it highlight the line "nature = data1(i, header("Investment Type"))" but none of the element is empty.
    And here the input code:
    Please Login or Register  to view this content.
    Last edited by shinpencil; 05-04-2016 at 09:43 AM.

  46. #46
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Forum Software initiated Duplicate post
    Last edited by Doc.AElstein; 05-04-2016 at 09:43 AM.

  47. #47
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Forum Software initiated Duplicate post
    Last edited by Doc.AElstein; 05-04-2016 at 09:43 AM.

  48. #48
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Spoon Feed Wank in French

    Quote Originally Posted by shinpencil View Post
    Sorry here is the code; ........
    Thankyou
    Merci
    _.....


    But
    Mais

    Also
    Aussi

    Quote Originally Posted by Doc.AElstein View Post
    .......

    You must give us test data on which to run that code. The test data should be reduced in size to the minimum to demonstrate the problem and the test data should be desensitized.
    Vous devez nous donner des données de test sur lequel exécuter ce code. Les données d'essai doivent être réduites en taille au minimum pour démontrer le problème et les données d'essai doivent être désensibilisés.

  49. #49
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Sorry I didn't precise it, it's the same data that I posted in post #28
    Last edited by shinpencil; 05-04-2016 at 09:59 AM.

  50. #50
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    French Wonks

    Quote Originally Posted by shinpencil View Post
    Sorry I didn't precise it, it's the same data that I posted in post #28
    Thankyou
    Merci


    _.........................................................

    I have forgot now what to do.
    Please precise it again
    Je l'ai oublié maintenant ce qu'il faut faire.
    Merci de préciser à nouveau





    So I have from you 3 Files from Post #28
    Je dois donc de vous 3 Fichiers de Post # 28

    And I also have 2 new Codes from you from Posts #44 and #45
    Et je dois aussi 2 nouveaux codes de vous de messages # 44 et # 45


    Please remind me what I should do:
    Which codes do I run on which data.

    S'il vous plaît me rappeler ce que je dois faire:
    Quels codes dois-je courir sur lequel des données.

  51. #51
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Firstly I run the Input code to import wb1 to the "compare" file then run the find differences code to compare 2 wb, but I got "subscript out of range" error in the find differences code
    P/S: Thanks for your translation, I can understand it, I'm just a bit suck at writing
    Last edited by shinpencil; 05-04-2016 at 10:16 AM.

  52. #52
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Spoon Feed A Wonks Wolly in French. En français ;)

    Quote Originally Posted by shinpencil View Post
    .......
    P/S: Thanks for your translation, I can understand it, I'm just a bit suck at writing
    Bitte

    You are Welcome

    Vous êtes les bienvenus

    I cheat !

    Je triche!

    https://translate.google.de/?hl=de

    I speak German

    Je parle allemand

    ...............


    Ok

    I open the File, “Compare Caceis.xlsm”

    I copy this code from Post #45 into the File, “Compare Caceis.xlsm”
    Please Login or Register  to view this content.
    I run this code and I choose Wb1.csv when prompted for a csv File to import

    But then I get a load of old bollox, as there was already data in Worksheet “Board”

    So I Clear ( empty data ) from Worksheet “Board” and try again.

    Now Worksheet Board contains just the imported data , the first bit of which looks like this:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    5
    6
    MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. InvestAmountent TyUnite Fund Amount (Client Cur.) Client Cur.
    7
    Redemption OUT
    6151784
    IE00B9D
    26.04.2016
    04.05.2016
    195
    Unit
    19193.46
    EUR
    8
    Redemption OUT
    6174269
    IE00B9C
    26.04.2016
    04.05.2016
    61.0714
    Unit
    6113.25
    GBP
    9
    Redemption OUT
    6179051
    IE00B9C
    26.04.2016
    04.05.2016
    240
    Unit
    24024
    GBP
    10
    Subscription IN
    6187108
    IE00B9C
    26.04.2016
    04.05.2016
    38.961
    Amount
    3900
    GBP
    11
    Subscription IN
    6198301
    IE00BX7
    26.04.2016
    04.05.2016
    223.9967
    Amount
    19835
    EUR
    Board


    _.....
    Now i open wb2.xls

    I copy this code from Post #44 into the File, “Compare Caceis.xlsm”
    Please Login or Register  to view this content.
    I run it in debug ( F8 ). I get the error you do... error here
    nature = data1(i, header("Investment Type"))
    The error is that the Index is outside the valid range. This is because

    header("Investment Type") does not exist!

    Please look carefully at the Header at column I in my first screen shot above:
    You have
    InvestAmountent TyUnite
    So
    header("InvestAmountent TyUnite ") exists instead!

    Your problem is in your Code Sub Input_CSV()
    The problem is at this point:
    Please Login or Register  to view this content.
    This is because you are also changing the Header, which I presume you do not wish to?

    So you need to make this change
    Please Login or Register  to view this content.
    Then you get this after clearing Worksheet “Board” and re-running Sub Input_CSV() ( again I choose Wb1.csv when prompted for a csv File to import ) :
    Using Excel 2007
    Row\Col
    H
    I
    J
    5
    6
    Share Nb. Investment Type Fund Amount (Client Cur.)
    7
    195
    Unit
    19193.46
    8
    61.0714
    Unit
    6113.25
    Board
    _.......................

    Subsequently the code Sub Find_Differences() no longer errors
    But I get nothing as the Worksheet “Differences” has something in it and your code does not paste the Output Array out in that case. So I clear that sheet and re- run Sub Find_Differences() .
    Then the code produces the following:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    2
    Missing order in Lomax 04.05.2016
    3
    4
    Redemption IE00B9C 26.04.2016 04.05.2016 Unit
    61.0714
    5
    Redemption IE00BS7 26.04.2016 04.05.2016 Unit
    177.7072
    6
    Redemption IE00B9C 26.04.2016 04.05.2016 Unit
    495.5411
    7
    Subscription IE00B9D 26.04.2016 04.05.2016 Unit
    27.5395
    8
    Redemption IE00B9D 26.04.2016 04.05.2016 Unit
    58.181
    9
    Redemption IE00B8B 26.04.2016 04.05.2016 Unit
    13.503
    10
    Redemption IE00B8B 26.04.2016 04.05.2016 Unit
    23.466
    11
    Redemption IE00B9C 26.04.2016 04.05.2016 Unit
    209.7632
    12
    Subscription IE00B9D 26.04.2016 04.05.2016 Unit
    44.5259
    13
    Redemption IE00B9C 26.04.2016 04.05.2016 Unit
    464.4591
    14
    Redemption IE00B8B 26.04.2016 04.05.2016 Unit
    15.323
    15
    Redemption IE00BS7 26.04.2016 04.05.2016 Unit
    3.6405
    16
    Redemption IE00B9C 26.04.2016 04.05.2016 Unit
    605.998
    17
    Subscription IE00B9C 26.04.2016 04.05.2016 Unit
    41.0542
    18
    Redemption IE00B8B 26.04.2016 04.05.2016 Unit
    72.5862
    19
    Redemption IE00B8B 26.04.2016 04.05.2016 Unit
    8.191
    20
    Subscription IE00BS7 26.04.2016 04.05.2016 Unit
    798.7968
    21
    Redemption IE00B9D 26.04.2016 04.05.2016 Unit
    5.737
    22
    Differences

    That do look better I think do.

    Alan.
    Last edited by Doc.AElstein; 05-04-2016 at 02:28 PM. Reason: Added a few profanities Wonk poo I do ! You too ;)

  53. #53
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Oh so that's the problem! I changed the range but didn't pay attention to this line. Thank you so much!
    Nice to meet you and your wife here! Sadly I don't speak german but I'll do my best to improve my writing skill

  54. #54
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Compare 2 workbooks - Empty array error Faire en français;)

    You are welcome, thanks for the feedback.

    Vous êtes les bienvenus, merci pour les commentaires

    Alan

  55. #55
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Hi, I have another problem: the result I got is false.
    Firstly I run the Input code to import wb1 to the "compare" file then run the find differences code to compare 2 wb, but I got 5 lines of differences instead of 1 line (it supposed to have only 1 difference)
    Could you review it for me plz?
    Attached Files Attached Files

  56. #56
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil
    How are you?
    Comment allez-vous?
    OK. Me or the Wife will take a look for you later...


    Alan
    Last edited by Doc.AElstein; 05-26-2016 at 06:42 AM.

  57. #57
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    I'm fine thank you, and you?
    Thank you for your kindness!

  58. #58
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Find missing rows comparing data range row info. allowing for data format differences

    OK here we go !

    OK allons y !

    _1 ) Your code is very badly ‘commented I have to start again every time. That makes it very hard. I forget in the meantime what is going on.

    Votre code est très mal », a commenté que je dois commencer à nouveau à chaque fois. Cela rend très difficile. J'oublie dans l'intervalle, ce qui se passe.

    _2) I have put my ‘Comments back in. If you wish me to help in the future, then please do not remove these comments. Text in a code module is very cheap and does not affect memory space too much. You may wish, however to correct my Google French Translations, which are not perfect.

    Je mets mes 'Commentaires avant. Si vous voulez que je aider à l'avenir, alors s'il vous plaît ne pas retirer ces commentaires. Texte dans un module de code est très pas cher et n'a aucune incidence sur l'espace mémoire trop. Vous voudrez peut-être, mais pour corriger mes Google Traductions françaises, qui ne sont pas parfaits.

    _3) I have also put back in my code section, Rem 7) , which creates and / or fills extra sheet to aid in debugging, “DebugSheet”

    J'ai également remis dans ma section de code, Rem 7), qui crée et / ou remplit une feuille supplémentaire pour faciliter le débogage, "DebugSheet"

    _4) I think it is worth doing a quick summary in English. This will help others following this thread or those that may later find it when coming here on a Google Search. You may rewrite it in French.

    Je pense que cela vaut la peine de faire un rapide résumé en anglais. Cela aidera les autres suivant ce fil ou ceux qui peuvent ensuite trouver en venant ici sur une recherche Google. Vous pouvez réécrire en français.

    Summary of what is going on here:
    _...........................................

    “Find missing rows when comparing data range row information allowing for data format differences


    Similar row information is contained in a data range in a Compare WorkSheet in
    a
    Main Workbook ( which also includes all the code discussed here),
    and a
    Selected File.

    Formats and columns used for the row information in those two may vary slightly.
    Initially a code
    Sub Input_CSV()
    Imports from a CSV File the compare data into the first sheet** of the Main Compare File.
    This is a minor part of the discussions in this Thread. That code has usually performed correctly.
    .( In the main code discussed below, the relevant Worksheet here is referred to as Worksheet
    ws1,
    and
    data1()
    as the data range of interest ( captured to an Array ) )

    _...................................

    The second code
    Sub Find_Differences____ ()
    This is the main Issue of the Thread and it is the Main Code

    Here a quick run through my ‘Commented versiond :
    Rem 1) Some worksheet, and data range info

    Rem 2) Rem 3 ) some checking of what, where, when Files are there and or opened etc..

    Rem 4) The code allows selection and opening of a File, ( in the Main Code the Worksheet of interest in this opened Workbook, is referenced as worksheet
    wb2,

    the data range is referred to as
    data2() ( captured to an Array ) ).

    Rem 6)
    and rem 5)
    Two Microsoft Scripting Runtime Dictionaries are made, whereby partial row information ( some of the column cell values for each row ) is adjusted into similar formats for both data ranges, before being concatenated into similar formatted strings, containing the important Row value information. This concatenated string is generally used throughout the code as the unique keys of the Dictionaries.

    An interesting neat technique used in the Main two Dictionaries is that initially for all headers another Dictionary is made, using a function
    Function Get_Header_DickOh(ByVal header As Variant, ByVal header_line As Long) As Dictionary

    This returns each unique heading as the Key of this Dictionary
    ( Dim header As Dictionary: header = Get_Header_DickOh(data__(), 1) )
    And the item as a number ( coincidently the item number of the dictionary ) , 1, 2, 3 ... etc...

    Returned DickOh is of this form :
    __Key___________Item
    Header1___________1
    Header2___________2
    Header3___________3
    Etc..
    _____Etc......

    Such that we can then obtain the column number in the data__() Array for any heading, ( for example as heading “Header1” as the first column Header ) through accessing the item of this header_DickOH by referencing it from its key thus
    = Header(“Header3”)
    = __3


    Dictionary data1_DickOh is for the main Compare data range ( Rem 6 ) in the main Code. ( it was imported from the CSV File using code Sub Input_CSV() )

    Dictionary data2_DickOh is for the data range in the opened file
    ***** Note: The comparison is the following: We are looking to see if each "row" of
    data1_DickOh, ( built from the range imported Each row in the data range imported from the CSV File )
    is in
    Dictionary data2_DickOh , ( which is built from the data range in the File wb2. ) This , wb2, is one that you select near the start of the program, Rem 4).
    Note: In Rem 5) , the Dictionary built, Dictionary data2_DickOh, may well include things not in the data Dictionary data1_DickOh, ( from range of data1(), built in Rem 6)

    Rem 7 ) In my code version this is an extra section which creates and / or fills an extra “DebugSheet” to aid in checking that the Main code is finally working correctly.

    After Rem 7 ) , Rem 8) makes a final Dictionary
    different_DickOh
    of row information which contains the keys form
    Dictionary data1_DickOh ( from data1(), ( the data range imported to the Main file ) )
    which do not exist in
    Dictionary data2_DickOh ( from data2() ( the data range in the opened book ) )

    Rem 9) An output Array , result(), is made from the keys in the final Difference Dictionary, different_DickOh


    Rem 10 ) Output Difference data Array, results(), ti the second sheet ( named emptily typically “Differences” )

    _...........................

    Note, within Dictionary data2_DickOh , could have been unwanted rows, such as the header row and an extra final row with just some small bit in it.
    This was not a problem to the running of the code. But it did make debugging difficult. So I also did a very minor change here:
    Please Login or Register  to view this content.
    This ensures only rows with Redemption or Subscription in first column are considered

    _...........................

    Next post gets bacK to the Problem, and request for help from Post #55................

  59. #59
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    DickOh ..... En français .............. ;)

    Quote Originally Posted by shinpencil View Post
    Hi, I have another problem: the result I got is false.
    Firstly I run the Input code to import wb1 to the "compare" file then run the find differences code to compare 2 wb, but I got 5 lines of differences instead of 1 line (it supposed to have only 1 difference)
    Could you review it for me plz?
    Now I run my modified code , which includes the making of an extra “debug” Sheet to check what information is held within the Two large Dictionaries
    Dictionary data2_DickOh
    And
    Dictionary data1_DickOh


    ( Before this I have did run Sub Input_CSV() ... This gives me in the first sheet of the main file......


    Excel 2007 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    4
    MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. Investment Type Fund Amount (Client Cur.) Client Cur. Fund Amount (Trade cur.)
    5
    Redemption OUT
    6847251
    IE00B64
    25.05.2016
    31.05.2016
    426.39
    Amount
    50000
    EUR
    50000
    6
    Subscription IN
    6852481
    IE00B64
    25.05.2016
    31.05.2016
    1320.77
    Unit
    155214.08
    EUR
    155214.08
    7
    Subscription IN
    6852517
    IE00B64
    25.05.2016
    31.05.2016
    7232.92
    Amount
    850000
    EUR
    850000
    8
    Redemption OUT
    6871812
    IE00B64
    25.05.2016
    01.06.2016
    69.52
    Unit
    8170.34
    EUR
    8170.34
    9
    Subscription IN
    6871813
    IE00B64
    25.05.2016
    31.05.2016
    455.88
    Amount
    53574
    EUR
    53574
    10
    Subscription IN
    6871815
    IE00B53
    25.05.2016
    31.05.2016
    18.4
    Unit
    2165.39
    USD
    2165.39
    11
    Redemption OUT
    6871816
    IE00B53
    25.05.2016
    01.06.2016
    3092.8
    Unit
    363974.62
    USD
    363974.62
    12
    Redemption OUT
    6873333
    IE00B64
    25.05.2016
    01.06.2016
    3.82
    Unit
    448.33
    EUR
    448.33
    13
    Subscription IN
    6874821
    IE00B64
    25.05.2016
    31.05.2016
    2000.00
    Unit
    235036.60
    EUR
    235036.6
    14
    Subscription IN
    6874925
    IE00B64
    25.05.2016
    31.05.2016
    161.15
    Amount
    18937.69
    EUR
    18937.69
    15
    Redemption OUT
    6874948
    IE00B61
    25.05.2016
    01.06.2016
    2908.59
    Unit
    383909.37
    EUR
    383909.37
    16
    Redemption OUT
    6875004
    IE00B64
    25.05.2016
    01.06.2016
    1.63
    Amount
    191.13
    EUR
    191.13
    Sheet: Board

    _.................)
    _...........

    I now do run the main code
    Sub Find_DifferencesPost_55()

    When prompted I select wb2.CSV, which is then opened and shows in its first sheet 1 the following:

    Excel 2007 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    1
    S/R type Order status Fund share code Fund name Pricing Date Value Date Import Date Settlement Date Publication Date Nature Quantity Net amount
    2
    Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated
    3
    Redemption Estimated IE00B61 25/05/2016 01/06/2016 25/05/2016 Unit
    2,908.59
    383,909.37
    4
    Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Amount
    161.15
    18,937.69
    5
    Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Amount
    1.63
    191.13
    6
    Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Amount
    426.39
    50,000.00
    7
    Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Amount
    7,232.92
    850,000.00
    8
    Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Unit
    69.52
    8,170.34
    9
    Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Unit
    1,320.77
    155,214.08
    10
    Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Unit
    3.82
    448.33
    11
    Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Amount
    455.88
    53,574.00
    12
    Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Unit
    2,000.00
    235,036.60
    13
    Redemption Estimated IE00B53 25/05/2016 01/06/2016 25/05/2016 Unit
    3,092.80
    363,974.62
    14
    Subscription Estimated IE00B53 25/05/2016 31/05/2016 25/05/2016 Unit
    18.40
    2,165.39
    Sheet: Sheet1

  60. #60
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: DickOh ..... En français .............. ;)

    In this Post I show both the results from my extra made “DebugSheet” and the produced “Differences” worksheet. (_..... Both these are in the Main file
    “Compare 1fl.xlsm” ...._)


    Here are the results:

    A
    B
    C
    D
    E
    F
    1
    Imported CSV File, Dictionary data1_DickOh: key ; Item Compare File, wb2. Dictionary data2_DickOh: Key ; Item
    2
    Redemption#IE00B64#25.05.2016#31.05.2016#Amount#50000.0000
    2
    Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908.5938
    3
    3
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320.7700
    3
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937.6900
    4
    4
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000.0000
    4
    Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191.1300
    5
    5
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69.5200
    5
    Redemption#IE00B64#25.05.2016#01.06.2016#Amount#50000.0000
    6
    6
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574.0000
    6
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000.0000
    7
    7
    Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18.4000
    7
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69.5240
    8
    8
    Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092.8000
    8
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320.7652
    9
    9
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3.8200
    9
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3.8150
    10
    10
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000.0000
    10
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574.0000
    11
    11
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937.6900
    11
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000.0000
    12
    12
    Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908.5900
    12
    Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092.8000
    13
    13
    Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191.1300
    13
    Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18.4000
    14
    14
    Sheet: DebugSheet

    _.......................................

    A
    B
    C
    D
    E
    F
    G
    1
    2
    Missing order in Lomax 26.05.2016
    3
    4
    Redemption IE00B64 25.05.2016 31.05.2016 Amount
    50000
    5
    Subscription IE00B64 25.05.2016 31.05.2016 Unit
    1320.77
    6
    Redemption IE00B64 25.05.2016 01.06.2016 Unit
    69.52
    7
    Redemption IE00B64 25.05.2016 01.06.2016 Unit
    3.82
    8
    Redemption IE00B61 25.05.2016 01.06.2016 Unit
    2908.59
    9
    Sheet: Differences

    _...


    I see no problem. The code appears to be functioning correctly


    ?? I try again in next post.......

  61. #61
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    DickOh ..... En français ..... ;)

    I try again---

    Possibly you are wanting that such lines
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320.7700
    and
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320.7652
    are compared as the same ??

    In which case you need to adjust the formatting adjustments here:
    '6c(ii) Do some Format changes to allow compatibility with other data range

    Please Login or Register  to view this content.
    _........
    And here
    '5c(ii) Some format changing so that both data ranges have similar formats for a comparison to "work"
    Please Login or Register  to view this content.
    _...

    Then you get:
    A
    B
    C
    D
    E
    1
    Imported CSV File, Dictionary data1_DickOh: key ; Item Compare File, wb2. Dictionary data2_DickOh: Key ; Item
    2
    Redemption#IE00B64#25.05.2016#31.05.2016#Amount#50000
    2
    Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908
    3
    3
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320
    3
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937
    4
    4
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000
    4
    Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191
    5
    5
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69
    5
    Redemption#IE00B64#25.05.2016#01.06.2016#Amount#50000
    6
    6
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574
    6
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000
    7
    7
    Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18
    7
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69
    8
    8
    Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092
    8
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320
    9
    9
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3
    9
    Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3
    10
    10
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000
    10
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574
    11
    11
    Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937
    11
    Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000
    12
    12
    Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908
    12
    Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092
    13
    13
    Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191
    13
    Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18
    14
    Sheet: DebugSheet

    And

    A
    B
    C
    D
    E
    F
    G
    2
    Missing order in Lomax 26.05.2016
    3
    4
    Redemption IE00B64 25.05.2016 31.05.2016 Amount
    50000
    Sheet: Differences

    Alan

    P.s You may wish to consider changing the title of the Thread
    http://www.excelforum.com/showthread.php?t=1125967

    would suggest a title such as
    “Find missing rows comparing data range row info. allowing for data format differences”

    Main code which I used is here
    http://www.excelforum.com/showthread...t=#post4397346
    And here
    http://www.excelforum.com/showthread...t=#post4397349


    IT IS ALL ONE CODE . Copy Second part directly under first part, in the same Code Module

    IL EST TOUT UN CODE. Copiez Deuxième partie directement sous la première partie, dans le même module de code





    _......

    Function
    Function Get_Header_Dico(ByVal header As Variant, ByVal header_line As Long) As Dictionary
    Is here
    http://www.excelforum.com/showthread...t=#post4397344
    Last edited by Doc.AElstein; 05-27-2016 at 04:59 AM.

  62. #62
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    DickOh ..... En français .............. ;)

    Here are a couple of Files, for my later reference..

    Voici quelques Files, pour ma référence ultérieure ..

    File:
    “DickOhEnfrançais.xlsm”

    Sub Find_DifferencesPost_55DickOhEnfrançais()

    DickOH.JPG








    _...........................................

    File:
    “Compare 1fl.xlsm”

    Sub Find_DifferencesPost_55()

    Attachment 463188
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Doc.AElstein; 05-27-2016 at 06:55 AM.

  63. #63
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Thank you so much! I just have a little problem: I want to have the error of each wb, but it appears too much values in the last column
    Here is my code for this part:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by shinpencil; 05-30-2016 at 04:42 AM.

  64. #64
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Trying to communicate Enfrançais

    Hi Shinpencil,
    _1 ) I am sorry but this makes no sense what so ever.
    Quote Originally Posted by shinpencil View Post
    .......I want to have the error of each wb, but it appears too much values in the last column........


    I am afraid that English is so bad no one, ( not even me!! ) can understand it !
    It makes no sense.
    I do not understand
    I have no idea what you are saying or asking?

    Please try again to explain the Problem again.

    _..............................



    _2 ) Posting Images to show me is not very helpful as I cannot copy them to a Spreadsheet.

    This is a better alternative:

    _a ) Copy this code here complete to any Normal Code Module
    http://www.excelfox.com/forum/showth...=9804#post9804

    _b) Activate the Worksheet that you want to show me

    _c) Select the Part you want to show me, like this....( Here you select what I have shown in Grey )

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    3
    4
    Redemption IE00B64 25.05.2016 31.05.2016
    5
    Differences

    _d) Run code
    Sub BB_Table_Clipboard_PikeFoarnts()

    _e) Open the Forum Reply Window

    _f) Paste into the Forum Reply Window. ( Ctrl + V ).
    _f(i) It should look like this in the Reply Window

    [color=lightgrey]Using Excel 2007[/color]
    [size=0][table="class:thin_grid"]
    [tr=bgcolor:powderblue][th][COLOR=black][sub]Row[/sub]\[sup]Col[/sup][/COLOR][/th][th][CENTER][COLOR=black]A[/COLOR][/CENTER][/th][th][CENTER][COLOR=black]B[/COLOR][/CENTER][/th][th][CENTER][COLOR=black]C[/COLOR][/CENTER][/th][/tr][tr][td="bgcolor:powderblue, align:center"][B]4[/B][/td]
    [td="bgcolor:#D8D8D8, align:LEFT"][COLOR="#FF0000"][Font="Calibri"]Redemption[/Font][/COLOR][/td]
    [td="bgcolor:#D8D8D8, align:LEFT"][COLOR="#FF0000"][Font="Calibri"]IE00B64[/Font][/COLOR][/td]
    [td="bgcolor:#D8D8D8, align:LEFT"][COLOR="#FF0000"][Font="Calibri"]25.05.2016[/Font][/COLOR][/td]
    [/tr][/table][/size][size=0][Table="width:, class:grid"][tr][td]Sheet: [b]Differences[/b][/td][/tr][/table][/size]
    _...................

    f(ii) After posting it should look like this

    Using Excel 2007
    Row\Col
    A
    B
    C
    4 Redemption IE00B64 25.05.2016
    Sheet: Differences


    _.........

    _g) Practice here: ( Start a New Thread - call it something like "Just Testieing" )
    http://www.excelforum.com/development-testing-forum/

    _...................................................

    ( h) I have also included the code in my latest version of your File
    “DickOhEnfrançais.xlsm”
    PikeFoarntsForShinpencil.JPG

    _...........................







    _3) Unfortunately we still have a little communication problem due to our different languages

    But i am sure we will get over it ......

    Alan and Jacky
    Attached Files Attached Files
    Last edited by Doc.AElstein; 05-30-2016 at 05:59 AM.

  65. #65
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Sorry I try to explain it again. I want to bring up the difference between the 2 wb. Apparently there have 1 error in each wb, but you can see in the image that there are 3 values in the column F instead of one (191,13; 50000 and 18,4) I don't know where it is coming from

  66. #66
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    DO NOT USE IMAGES!! En français: Ne pas utiliser Images

    Hi

    I cannot help you if you use Images
    Je ne peux pas vous aider si vous utilisez Images

    Images are bad
    Les images sont mauvais

    Do not use Images at Excel Forum when trying to show a Spreadsheet Range
    Ne pas utiliser Images à Excel Forum en essayant de montrer une feuille de calcul Excel Range

    Instead:
    Use the Method ( _2 ) I showed you in Post #64
    Or
    Upload a File.


    _..........................

    This is what I see from you Image

    Attachment 463493





    It is nonsense
    C'est du n'importe quoi

    I have no idea what I am looking at ???
    Je ne sais pas ce que je regarde ???

    DO NOT USE IMAGES AT EXCEL FORUM WHEN TRYING TO SHOW A SPREADSHEET RANGE
    NE PAS UTILISER IMAGES À EXCEL FORUM EN ESSAYANT DE MONTRER UNE FEUILLE DE CALCUL EXCEL RANGE

    _.........................................

    Please try again to show me ( NOT WITH IMAGES !!! )
    _1) what you are currently getting from the code ( The False Result )

    _2) what you want the code to give you. ( The correct ( True ) Results )


    _3) Tell me again exactly what you do - which codes you run and which data are you are using

    Alan

    _...........................












    The image below is no use to me. I have no idea what it means. Please do not send me any more Images of Excel Spreadsheet Ranges
    L'image ci-dessous ne sert à rien pour moi. Je ne sais pas ce que cela signifie. S'il vous plaît ne me renvoyez pas plus photos de plages de feuille de calcul Excel
    Attached Images Attached Images
    Last edited by Doc.AElstein; 05-30-2016 at 06:53 AM.

  67. #67
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Post a Screenshot but not an Image. Poster une capture d'écran, mais pas d'une image

    _2 ) Posting Images to show me is not very helpful as I cannot copy them to a Spreadsheet.
    Affichant des images pour montrer moi est pas très utile que je ne peux pas les copier sur une feuille de calcul.


    This is a better alternative:
    Ceci est une meilleure alternative:

    _a ) Copy this code here complete to any Normal Code Module
    Copiez ce code ici complète à tout module de code normal

    http://www.excelforum.com/showthread...t=#post4399573
    http://www.excelfox.com/forum/showth...=9804#post9804


    _b) Activate the Worksheet that you want to show me
    Activer la feuille de calcul que vous voulez me montrer

    _c) Select the Part you want to show me, like this....( Here you select what I have shown in Grey )
    Sélectionnez la partie que vous voulez me montrer, comme cela .... (Ici vous sélectionnez ce que je l'ai montré en gris)

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    3
    4
    Redemption IE00B64 25.05.2016 31.05.2016
    5
    Differences

    _d) Run this MAIN CODE
    Exécuter ce CODE PRINCIPAL
    Sub BB_Table_Clipboard_PikeFoarnts()
    ( That should have copied the Selected Range into your Clipboard ( in BB CODE TAG Format ))
    (Cela aurait copié la plage sélectionnée dans votre presse-papiers (en BB CODE TAG Format))

    _e) Open the Excel Forum Reply Window
    Ouvrez la fenêtre Répondre Excel Forum

    _f) Paste into the Forum Reply Window. ( Ctrl + V ).
    Coller dans la réponse Fenêtre Forum. (Ctrl + V).
    _f(i) It should look like this in the Reply Window
    Il devrait ressembler à ceci dans la fenêtre Répondre

    [color=lightgrey]Using Excel 2007[/color]
    [size=0][table="class:thin_grid"]
    [tr=bgcolor:powderblue][th][COLOR=black][sub]Row[/sub]\[sup]Col[/sup][/COLOR][/th][th][CENTER][COLOR=black]A[/COLOR][/CENTER][/th][th][CENTER][COLOR=black]B[/COLOR][/CENTER][/th][th][CENTER][COLOR=black]C[/COLOR][/CENTER][/th][/tr][tr][td="bgcolor:powderblue, align:center"][B]4[/B][/td]
    [td="bgcolor:#D8D8D8, align:LEFT"][COLOR="#FF0000"][Font="Calibri"]Redemption[/Font][/COLOR][/td]
    [td="bgcolor:#D8D8D8, align:LEFT"][COLOR="#FF0000"][Font="Calibri"]IE00B64[/Font][/COLOR][/td]
    [td="bgcolor:#D8D8D8, align:LEFT"][COLOR="#FF0000"][Font="Calibri"]25.05.2016[/Font][/COLOR][/td]
    [/tr][/table][/size][size=0][Table="width:, class:grid"][tr][td]Sheet: [b]Differences[/b][/td][/tr][/table][/size]
    _...................

    f(ii) After posting your Reply , it should look like this
    Après poster votre réponse, il devrait ressembler à ceci

    Using Excel 2007
    Row\Col
    A
    B
    C
    4 Redemption IE00B64 25.05.2016
    Sheet: Differences


    _.........

    _g) Practice here: ( Start a New Thread - call it something like "Just Testieing" )
    Pratiquer ici: (Commencer un nouveau fil - appeler quelque chose comme "Just Testieing" )

    http://www.excelforum.com/development-testing-forum/

    _...................................................

    ( h) I have also included the code in my latest version of your File
    J'ai également inclus le code dans ma dernière version de votre fichier
    “DickOhEnfrançais.xlsm”
    Attachment 463491
    Attached Files Attached Files
    Last edited by Doc.AElstein; 05-30-2016 at 07:27 AM.

  68. #68
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    I use this macro with 2 wb in post #55. You can see the problem in the "Differences" sheet..
    Attached Files Attached Files
    Last edited by shinpencil; 05-30-2016 at 08:34 AM.

  69. #69
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Hi
    Ok I will take a look later

    But in the meantime I am curious and would like some better feedback and comment to my Posts#59, Posts#60 and Posts#61,

    Please tell me Please, What was finally the Problem ???

    Was it that

    You indeed had 5 differences which you had overlooked ( as i showed in a screen shot in Post #60 ) ??

    I am asking as you are using the original code line

    Please Login or Register  to view this content.
    And not that which I suggested in Post #61

    Please Login or Register  to view this content.

    _..............................

    I am only asking, as it would be courteous to us if you gave some more feedback.

    Alan.
    Last edited by Doc.AElstein; 05-30-2016 at 09:09 AM.

  70. #70
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Sorry it's just because when I use this to compare others workbooks it only gave integer values because of the function "Int()", it isn't really what I want and it is way too complicated for me, so I try to change a little in my code.

  71. #71
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Quote Originally Posted by shinpencil View Post
    Sorry it's just because when I use this to compare others workbooks it only gave integer values because of the function "Int()", it isn't really what I want and it is way too complicated for me, so I try to change a little in my code.
    So was it correct that you had 5 differences and not 1 as you had originallly said ????

  72. #72
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    No it is correct that it has 1 difference, but the code didn't work well with other wb, that's why I tried to modify my code

  73. #73
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    feedback and comment to my Posts#59, Posts#60 and Posts#61,

    Hi

    Ok I see

    Schnitzel ____True bien
    Alan Wnks __ False mal

    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    I understand now, Thank you
    _..............................



    I check now your latest problem.

    Alan
    Last edited by Doc.AElstein; 05-30-2016 at 10:24 AM.

  74. #74
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    What a Dich_OH en français ( Great MSR Dicktionary Code .. ;)

    Hi shinpencil,
    This is your “latest” problem


    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    2
    Missing order in Lomax 30/05/2016
    3
    4
    Redemption IE00B64 25/05/2016 31/05/2016 Amount
    50000
    5
    6
    Missing order in Caceis 30/05/2016
    7
    Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated
    191.13
    unwanted row.. such as the header row
    8
    Redemption IE00B64 25/05/2016
    06.01.2016
    Amount
    50000
    9
    18.4
    unwanted row...…………. an extra final row with just some small bit in it.
    10
    11
    It appears 2 values out of nowhere
    Differences
    _.......

    I explained this potential problem already !! – See my post #58 from last week
    Quote Originally Posted by Doc.AElstein View Post
    .........
    Note, within Dictionary data2_DickOh , could have been unwanted rows, such as the header row and an extra final row with just some small bit in it.
    This was not a problem to the running of the code. But it did make debugging difficult. So I also did a very minor change here:
    Please Login or Register  to view this content.
    .................


    You capture too much!!!
    Let data2() = ws2.Range("A1:U300").Value

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    1
    S/R type Order status
    2
    Order status: Estimated Order status: Estimated
    3
    Redemption Estimated
    4
    Subscription Estimated
    5
    Redemption Estimated
    6
    Redemption Estimated
    7
    Subscription Estimated
    8
    Redemption Estimated
    9
    Subscription Estimated
    10
    Redemption Estimated
    11
    Subscription Estimated
    12
    Subscription Estimated
    13
    Redemption Estimated
    14
    Subscription Estimated
    15
    16
    17
    Sheet1

    _.....

    MY code
    Sub Find_DifferencesPost_55_68DickOhEnfrançais()
    In this file:
    https://app.box.com/s/2pox9n4cmdih8e5961vrq2am187qh4ft

    Still works!!

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    2
    Missing order in Lomax 31.05.2016
    3
    4
    Redemption IE00B64 25.05.2016 31.05.2016 Amount
    50000
    5
    6
    Missing order in Caceis 31.05.2016
    7
    Redemption IE00B64 25.05.2016 01.06.2016 Amount
    50000
    8
    Differences

    _.....................................................................

    Next Post I do fix your Code:
    Next Post-je faire réparer votre code:
    Last edited by Doc.AElstein; 05-30-2016 at 08:08 PM.

  75. #75
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Make good Wonks better. :) Modifications pour rendre votre travail de code

    I do fix your code now
    Je ne résous votre code maintenant



    This bad wonky is. It not wonk like we want wonk
    C'est mauvais. Il ne fonctionne pas comme nous le voulons

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    This code make good wonks better......
    C'est bon. Il fait ce que nous voulons qu'elle ..


    Please Login or Register  to view this content.

    Or this also wonking Colons good
    Ce Code alternatif fonctionne également
    Please Login or Register  to view this content.

    _Alan

    Please if you want me to help in the future learn to use the Forum Tool, as I explained in Post # 67
    S'il vous plaît, si vous voulez que je vous aide à l'avenir apprendre à utiliser l'outil de forme, comme je l'ai expliqué dans le message # 67
    http://www.excelforum.com/excel-prog...ml#post4399581
    Last edited by Doc.AElstein; 05-30-2016 at 08:26 PM. Reason: Did a Orrin Colon Wonk

  76. #76
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Thank you but it bring up the header line in the sheet. I tried to modify it like this:
    Please Login or Register  to view this content.
    But it still have value in the "order status" line (191,13)

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    2 Missing order in Lomax 31/05/2016
    3
    4 Redemption IE00B64 25/05/2016 31/05/2016 Amount 50000
    5
    6 Missing order in Caceis 31/05/2016
    7 Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated 191.13
    8 Redemption IE00B64 25/05/2016 06/01/2016 Amount 50000
    9
    Sheet: Differences

  77. #77
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Empty array error

    Hi shinpencil ,
    Thanks for using the Screen shot tools. That is very helpful.
    Merci d'utiliser les outils de capture d'écran. Cela est très utile.

    Please Post the code section that you modified, and I will look at it for you as soon as I can
    S'il vous plaît Publiez la section de code que vous avez modifié, et je vais regarder pour vous dès que possible


    Thanks once again for giving a good screenshot.
    Merci encore une fois pour donner une bonne capture d'écran.

    We can all see now clearly what the problem is.
    Nous pouvons tous voir maintenant clairement quel est le problème.

    Alan

    P.s.
    I expect that the problem is that you are now limiting the Top of your Range, but the building of your dictionary, “data2_Dico”, is still starting at row which catches the Header as a valid entry in that Dictionary, and subsequently is taken as a difference row in the later Differences Dictionary that is created later. But I will be able to see that when you show me the exact modifications you have done. I assume that the line you showed is not your only modification? If it is, then it should be obvious from my previous explanations what your problem is
    Post-scriptum
    Je pense que le problème est que vous êtes maintenant limitez le haut de votre Range, mais la construction de votre dictionnaire, "data2_Dico", est encore débutant à la ligne qui attrape la tête comme une entrée valide dans ce dictionnaire, puis est pris comme une ligne de différence dans le Dictionnaire des différences plus tard qui est créé plus tard. Mais je serai en mesure de voir que lorsque vous me montrer les modifications exactes que vous avez fait.

    Je suppose que la ligne que vous avez montré est pas votre seule modification? Si elle est, alors il devrait être évident de mes explications précédentes quel est votre problème



    Using Excel 2007
    Row\Col
    A
    26 Thankyou
    27 shinpencil
    Fiche Nom Utilisé: DebugSheet
    Code de Nom de la feuille: Tabelle1
    Last edited by Doc.AElstein; 05-31-2016 at 06:53 AM.

  78. #78
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Yes I just modified this line like that:
    Please Login or Register  to view this content.
    and I got the table like below. I would like to delete the value in order status line
    Last edited by shinpencil; 05-31-2016 at 08:08 AM.

  79. #79
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    I solved the problem! Here is my code:
    Please Login or Register  to view this content.

  80. #80
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    French Kissing in the EF 'ay ? .... Embrasser française en ay l'EF '? ... ;)

    Hi Schnipcel,
    I see you have another solution, good. But I just finished mine so I give you that also
    Je vois que vous avez une autre solution, bon. Mais je viens de finir le mien donc je vous donne cela aussi

    _.......................................................

    Quote Originally Posted by shinpencil View Post
    Yes I just modified this line like that:
    Please Login or Register  to view this content.
    and I got the table like below. I would like to delete the value in order status line
    OK, then the problem is obvious
    OK, alors le problème est évident

    As I said, you are correctly limiting the top of the data used to build the “data2_Dico” Dictionary
    This is good
    Comme je l'ai dit, vous êtes correctement limitez le haut des données utilisées pour construire le Dictionnaire "data2_Dico"
    C'est bon

    Let data2() = ws2.Range("A1").CurrentRegion.Value


    That limits the Top row such that no row appears with your added formatting to an otherwise empty row. You will have many of these extra rows by selecting 300 rows as you did previously. (_...... you only see one of those rows by virtue of this line
    Cela limite la rangée supérieure de telle sorte qu'aucune ligne apparaît avec votre mise en forme ajoutée à une ligne vide sinon. Vous aurez beaucoup de ces lignes supplémentaires en sélectionnant 300 lignes que vous avez fait précédemment. (_.....Vous voyez seulement une de ces lignes, en vertu de cette ligne
    If Not data2_Dico.Exists(key) Then
    That line will only allow unique entries into the “data2_Dico” Dictionary
    Cette ligne ne permettra entrées uniques dans le Dictionnaire "data2_Dico"
    _.....)

    You need to select the entire range starting at A1 so that the Function
    Vous devez sélectionner l'ensemble de la gamme à partir de A1 de sorte que la fonction
    Get_Header_Dico(data2, 1)

    Is able to get at the first row to get a ( unique ) header list, which is further used to aid in building the main Dictionary
    Est en mesure d'obtenir à la première rangée pour obtenir un (unique) liste d'en-tête, qui est en outre utilisé pour aider à la construction de la principale Dictionary
    “data2_Dico” Dictionary


    _..............
    However, if you use all that row data in building “data2_Dico” Dictionary, then the rows including headers will be considered
    What is therefore required is to start your rows used in building “data2_Dico” Dictionary where your data starts.
    Toutefois, si vous utilisez tout ce que les données de ligne dans la construction "data2_Dico" Dictionnaire, puis les lignes, y compris les en-têtes seront considérées
    Quel est donc nécessaire est de commencer vos rangs utilisés dans la construction Dictionnaire "data2_Dico" où vos données commence.


    Previously this was overcome ( by you or however originally wrote this code ) for a single heading Row by starting at row 2, rather than row 1 using this code line
    Auparavant, ce fut vaincu (par vous ou cependant à l'origine écrit ce code) pour une seule rubrique rangée en commençant à la ligne 2, plutôt que de la ligne 1 en utilisant cette ligne de code
    For i = 2 To UBound(data2, 1)


    However in your current data you have two header rows, so one simple solution would be to change to this, where 2 has been replaced by 3
    Cependant, dans vos données actuelles, vous avez deux lignes d'en-tête, donc une solution simple serait de changer à cela, où 2 a été remplacé par 3

    For i = 3 To UBound(data2, 1)


    However, this will not work if you have different data with a different number of header rows.
    Cependant, cela ne fonctionnera pas si vous avez des données différentes avec un nombre différent d'en-tête des lignes.

    _...........................................................

    My solutions in post #75 determine where your data starts ( the next row after any number of heading rows. ( Value held in Variable stR )
    The solutions work well
    I do not know why you did not choose to use them ??
    Never mind !!
    I give you now another solution which uses your code line
    Mes solutions en post # 75 déterminent où vos démarrages de données (la prochaine rangée après un nombre quelconque de la position des lignes. (Valeur tenue à la variable str)
    Les solutions fonctionnent bien
    Je ne sais pas pourquoi vous ne l'avez pas choisi de les utiliser ??
    Ça ne fait rien !!
    Je vous donne maintenant une autre solution qui utilise votre ligne de code

    Let data2() = ws2.Range("A1").CurrentRegion.Value


    Codes in next post:
    Codes en poste suivant:
    _.........................................

  81. #81
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: French Kissing in the EF 'ay ? .... Embrasser française en ay l'EF '? ... ;)

    Code variations which use
    variations de code qui utilisent

    Let data2() = ws2.Range("A1").CurrentRegion.Value




    This code is best. As with my previous codes from Post #75 it will determine where your data starts
    Ce code est le meilleur. Comme avec mes codes précédents de Postes n ° 75, il permettra de déterminer où vos données commence

    Please Login or Register  to view this content.
    _...........................................

    This code is not so good, it will always assume that your data starts on row 3. ( I expect that may not be the case )
    (‘ 3 is Start row where "Redemption" or "Subscription" starts FOR DATA WITH 2 HEADER ROWS )
    Ce code est pas si bon, il sera toujours supposer que vos données commence sur la ligne 3. (Je pense que peut-être pas le cas)
    ('3 est Démarrer ligne où «Rachat» ou «Abonnement» commence DONNÉES AVEC RANGS 2 HEADER)



    Please Login or Register  to view this content.


    All is well
    Tout est bien

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    2 Missing order in Lomax 31.05.2016
    3
    4 Redemption IE00B64 25.05.2016 31.05.2016 Amount 50000
    5
    6 Missing order in Caceis 31.05.2016
    7 Redemption IE00B64 25.05.2016 01.06.2016 Amount 50000
    8
    Fiche Nom Utilisé: Differences
    Code de Nom de la feuille: Sheet2



    Alan

  82. #82
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Hi,
    Yes I understand the problem as you pointed out. Thank you I am able to learn a lot here!

  83. #83
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    French Kissing in the EF 'ay ?..... Embrasser française en ay l'EF '?

    Hi
    Quote Originally Posted by shinpencil View Post
    ...... Thank you I am able to learn a lot here!
    Your Welcome
    Thanks for the Feedback
    Alan


    De rien
    Merci pour les commentaires
    Alan

  84. #84
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Hi Alan,
    Could you help me to resolve this problem please: I need to compare 2 others wb: "Wb1" (sheet1) and "Wb2" but there are some conditions:
    -In wb2, if Nature is "Unit" and S/R type is "Redemption" then I need to compare the "Quantity" value in Wb2 to "Quantity Red" in Wb1
    -if Nature is "Unit" and S/R type is "Subscription" then I need to compare the "Quantity" value in Wb2 to "Quantity Sub" in Wb1
    -if Nature is "Amount" and S/R type is "Redemption" then I need to compare the "Amount" value in Wb2 to "Amount Red class ccy" in Wb1
    -if Nature is "Amount" and S/R type is "Subscription" then I need to compare the "Amount" value in Wb2 to "Amount Sub class ccy" in Wb1

    I'm trying to write some codes in "Find differences" macro but it didn't work! Could you view it for me plz?
    Attached Files Attached Files

  85. #85
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Find missing rows comparing data range row info allowing for complex data format differenc

    Hi Shinpencil

    I think You are trying to do something a bit different before you know what you have been doing up until now.


    If you read post #58, I tried to explain for both of us for future reference what the your main code,
    Sub Find_Differences()
    Does!


    I also re titled the Post appropriately to
    “.....Find missing rows comparing data range row info. allowing for data format differences...”

    This is what you have been doing up until now:

    _1 ) You have two sheets with data rows to be compared in order to find rows that are not in both sheets.


    _2) Your code makes for a Worksheet, ws1, a unique set of rows in a particular format based on concatenation of certain cells in each row, and that particular format is so as to make a comparison possible with rows from another Worksheet, ws2, which will be similarly broken down into unique rows based on the concatenation of certain cells in each row, and that all in a particular format to suit that format of the first Worksheet, ws1.

    _3) So having done that above for ws1, your code then does the same for ws2, making a unique set of rows in a particular format based on concatenation of certain cells in each row, and that particular format is to suit that produced for ws1

    _4) Those unique rows are held in two Dictionaries.

    _5) Each row is checked for existence in the other dictionary in order to find any differences, that is to say missing rows.

    _........................

    In Post 84 you are asking for something similar but much more complicated. Possibly?? There may be a communications problem again!!!

    You appear to be asking now to compare the concatenated unique formatted cells from ws2 to one of four possible formatted Dictionaries in place of the current one Dictionary for ws1.

    So logically you need to create four Dictionaries of concatenated unique formatted cells for ws1.
    Then as each row in the one Dictionary for ws2 is compared, the 4 criteria you have given will need to be checked to see which of the four Dictionaries for ws1 is used in the comparison, that is to say is checked for the existence in it of the current concatenated unique formatted cells row from ws2.
    _..........................

    If you need help, you need to make some attempt to do that!! In the files you uploaded you do not appear to have made any attempt to solve the problem?

    Otherwise you are not asking for help. – You are asking for someone to do a lot of work for you!!!

    Hope that gives you at least a direction in which to try.


    Alan
    Last edited by Doc.AElstein; 06-14-2016 at 01:26 PM.

  86. #86
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Yes it is completely different data so I need to write another code, but it's more complicated than what I can do (don't know that I even need to create 4 dictionaries) I tried to do like you said but it didn't work that's why I'm asking for help...

  87. #87
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Compare and Find missing rows in two sheets with different Headings and Formats

    Compare and Find missing rows in two sheets with different Headings and Formats

    Comparez et trouvez des lignes manquantes dans deux feuilles avec différents titres et Formats
    _...........................................

    Hi shinpencil

    OK, I will try to find time to help. But I cannot promise anything as I am a bit busy. And you are asking for quite a lot of work!
    OK, je vais essayer de trouver le temps pour aider. Mais je ne peux pas promettre quoi que ce soit que je suis un peu occupé. Et vous demandez beaucoup de travail!

    In the meantime please provide screenshots as I showed you previously. ( see Post #67 )
    En attendant s'il vous plaît fournir des captures d'écran que je vous ai montré précédemment. (Voir Post # 67)

    In particular, please fill in a “Difference" Worksheet manually that should look exactly as you want it finally based on the Input Worksheets ws1 ( “Sheet1” in Wb1 ) and ws2 ( “Sheet1” in Wb2 )
    En particulier, s'il vous plaît remplir une «différence» Feuille manuellement qu'il devrait regarder exactement comme vous le voulez enfin sur la base des feuilles de travail d'entrée ws1 ( “Sheet1” in Wb1 ) et ws2 ( “Sheet1” in Wb2 )

    Please try to show as much of that information as you can in the Thread Post, using the tool as you did in Post #76
    S'il vous plaît essayer de montrer autant de cette information que vous pouvez dans le fil Post, en utilisant l'outil que vous avez fait dans le message # 76

    It may be easier for me to do it for you in my version of your code. So then you will need to learn from that and adjust your code appropriately
    Il peut être plus facile pour moi de le faire pour vous dans ma version de votre code. Ainsi donc, vous aurez besoin d'apprendre de cela et d'ajuster votre code de manière appropriée

    Alan

    P.s.1
    By the way. I am just curios? What is your real Name ?
    Au fait. Je suis juste curieux? Quel est ton vrai nom ?

  88. #88
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare 2 workbooks - Empty array error

    Yes thank you anyway. It should look like that in the "Differences" worksheet after removing the duplicate values:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    2 Missing order in Lomax 17/06/2016
    3
    4 LU051 31/05/2016 06/03/2016 7260.9
    5
    6
    7 Missing order in Caceis 17/06/2016
    8 Order status: Estimated
    9 LU107 31/05/2016 06/03/2016 Amount 18109.44
    10 LU107 31/05/2016 06/03/2016 Amount 1600708
    11 LU107 31/05/2016 06/03/2016 Amount 191746.3
    12 LU107 31/05/2016 06/03/2016 Amount 328689.8
    13 LU107 31/05/2016 06/06/2016 Unit 139.36
    14 Order status: Prevaluated Order status: Prevaluated 139.36
    15 LU051 31/05/2016 06/03/2016 Unit 240
    16 LU051 31/05/2016 06/03/2016 Unit 6780
    17 LU051 31/05/2016 06/03/2016 Unit 0.9
    18 Order status: Confirmed Order status: Confirmed 475
    19 LU128 31/05/2016 06/01/2016 Unit 15000
    20 LU025 31/05/2016 06/01/2016 Unit 25400
    21 LU139 31/05/2016 06/02/2016 Unit 400000
    22 LU053 31/05/2016 06/01/2016 Unit 8000
    23 LU053 31/05/2016 06/01/2016 Unit 5000
    24 LU095 31/05/2016 06/01/2016 Unit 195000
    Sheet: Differences


    By the way, I am Elie

  89. #89
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Comparez et trouvez des lignes manquantes dans deux feuilles avec différents titres et For

    Hi Elie,

    I have got some way into the code.
    But I am stuck at one point.
    J'ai une certaine façon dans le code.
    Mais je suis coincé à un moment donné.


    In this code part:
    Dans cette partie de code:


    Please Login or Register  to view this content.
    You make no attempt to give “nature” a value.
    Vous ne faites aucune tentative de donner la «nature» une valeur.

    So “nature” will always = ""
    Donc, la «nature» sera toujours = ""

    So there will never be any matching rows in both Worksheets.
    Donc, il n'y aura jamais de lignes correspondantes dans les deux feuilles de calcul.


    You will therefore never achieve a matching row from ws1 with ws2, as
    “nature” always has a value in ws2. ( “Amount” or “Unit” )
    And
    “nature” always = "" in ws1.
    Vous serez donc jamais atteindre une ligne correspondante de ws1 avec WS2, comme
    "Nature" a toujours une valeur en ws2. ( “Amount” or “Unit” )
    Et
    "Nature" toujours = "" dans ws1.




    Possible you need to remove “nature” from both your concatenation lines ??
    Possible vous devez supprimer la «nature» de vos deux lignes de concaténer ??

    So this
    Donc ça

    Please Login or Register  to view this content.
    must be replaced with this ?????
    doit être remplacé par ce ?????


    Please Login or Register  to view this content.


    Alan

  90. #90
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Comparez et trouvez des lignes manquantes dans deux feuilles avec différents titres et

    Hi Elie,

    I have spent some more time on the code.
    J'ai passé plus de temps sur le code.

    I think I could easily achieve exactly what you want.
    Je pense que je pourrais facilement obtenir exactement ce que vous voulez.

    But I expect it would take me a week to do this. We would probably need a lot of communication in order to confirm exactly what is required.
    Mais je pense qu'il me faudrait une semaine pour le faire. Nous aurions probablement besoin de beaucoup de communication afin de confirmer exactement ce qui est nécessaire.

    Unfortunately I must be away now for at least a week. So do not have that time currently.
    Malheureusement, je dois être loin maintenant pendant au moins une semaine. Donc, ne dispose pas actuellement ce moment-là.


    However, I have done an initial modification of an existing code version of mine
    Cependant, je l'ai fait une première modification d'une version de code existant de la mine


    I have not had time to ‘comment it and explain it fully.
    Je ne l'ai pas eu le temps de ‘commenter et expliquer pleinement.

    But, it will give you at least some indication of the complexity involved in what you are asking for!
    Mais, il vous donnera au moins une idée de la complexité de ce que vous demandez!


    It is impracticable to post the code in its current form as it is much too large.
    Il est impossible d'afficher le code dans sa forme actuelle car il est beaucoup trop grand.

    So I give you a File with this Code in it. The code is:
    Donc, je vous donne un fichier avec ce code en elle. Le code est:

    Sub Find_DifferencesPost_55_68_84DickOhEnfrançais()


    Here is the File:
    Voici le fichier:
    ( “Wb1Post84.xlsm” )
    https://app.box.com/s/v7uc7wn72xs6b5kv1awz6a0pmq1slfn4


    _......

    If you run that code using the Data you supplied in Post # 84, then you will get the following results:
    Si vous exécutez ce code à l'aide des données que vous avez fournies dans le message # 84, alors vous obtiendrez les résultats suivants:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    1
    2
    3 Missing order in Caceis 18.06.2016
    4 LU107 31.05.2016 03.06.2016 18109.44
    5 LU107 31.05.2016 03.06.2016 1600708
    6 LU107 31.05.2016 03.06.2016 191746.3
    7 LU107 31.05.2016 03.06.2016 328689.8
    8 LU107 31.05.2016 06.06.2016 139.36
    9 LU051 31.05.2016 03.06.2016 240
    10 LU051 31.05.2016 03.06.2016 6780
    11 LU051 31.05.2016 03.06.2016 0.9
    12 LU128 31.05.2016 01.06.2016 15000
    13 LU025 31.05.2016 01.06.2016 25400
    14 LU139 31.05.2016 02.06.2016 400000
    15 LU053 31.05.2016 01.06.2016 8000
    16 LU053 31.05.2016 01.06.2016 5000
    17 LU095 31.05.2016 01.06.2016 195000
    Fiche Nom Utilisé: Differences
    Code de Nom de la feuille: Sheet2


    _..................................

    That is the best help I can give you for now.
    C'est la meilleure aide que je peux vous donner pour l'instant.


    If you still require further help in a week or two, then reply here and if I have time I will try to help further
    Si vous avez encore besoin d'aide supplémentaire dans une semaine ou deux, puis répondre ici et si j'ai le temps, je vais essayer d'aider davantage

    Alan
    Attached Files Attached Files

  91. #91
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Yes I still need your help, please let me know when you have time, thank you!

  92. #92
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie,

    I may be able to take a look at this again in a few days.
    Je peux être en mesure de jeter un oeil à ce nouveau dans quelques jours.

    In the meantime please consider carefully the following:
    Regarding the “Differences” Code.
    En attendant s'il vous plaît examiner attentivement ce qui suit:
    En ce qui concerne le Code "Différences".


    Please consider very carefully what you wish to achieve now and in the future from this code.
    S'il vous plaît examiner très attentivement ce que vous souhaitez réaliser maintenant et dans l'avenir de ce code.

    Currently the code size is just about at the limit of a sensible size. Continuingly adding to and modifying a code you which you do not fully understand is a recipe for disaster for all those People concerned with it.
    Actuellement, la taille du code est à peu près à la limite d'une taille raisonnable. ajoutant continuingly à et modifier un code que vous ne comprenez pas une recette pour un désastre pour toutes les personnes concernées avec elle.

    It may save everyone’s time in the long run if a new code is written.
    Il peut gagner du temps de tout le monde dans le long terme si un nouveau code est écrit.

    Only if you can show and explain very clearly what you want is there any chance for me to do that for you in the constraints of working in such a Forum. Otherwise you will need to consider employing professional help This is because much time will be needed in communication to find out exactly what all the issues are. That is totally impractical for me to do that.
    Seulement si vous pouvez montrer et expliquer très clairement ce que vous voulez est-il une chance pour moi de faire cela pour vous dans les contraintes de travailler dans un tel Forum. Sinon, vous devrez envisager d'employer une aide professionnelle C'est parce que beaucoup de temps sera nécessaire dans la communication pour savoir exactement ce que toutes les questions sont. Cela est totalement impossible pour moi de le faire.


    Alan

  93. #93
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Alan,

    It worked with 2 files in post #84 but I tried it with 2 others files and it didn't work, the result is false. I tried to modify the code but can't detect where is the problem...
    I attach 2 files here with the result in the 'Differences' sheet, it should look like the result in post #88. Hopefully that you can view it for me when you have time, thank you!
    Attached Files Attached Files

  94. #94
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Shinpencil
    If I have time I will try to take a look tomorrow
    But please read carefully again Post #92
    If you can be very clear on what you want it may be better for me to start again with a new code. Please read again all in Post # 92

    Si j'ai le temps, je vais essayer de jeter un oeil demain
    Mais s'il vous plaît lire attentivement nouveau Poster # 92
    Si vous pouvez être très clair sur ce que vous voulez peut-être mieux pour moi de commencer à nouveau avec un nouveau code. S'il vous plaît lire à nouveau tous dans le message # 92

    Alan

  95. #95
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Yes the code in post #90 did work for 2 files in post #84 like I said, but when I tried it with 2 new files in post #93 above, the result is false (the result is displayed in the ''Differences'' sheet).
    I still want that it should look like the result in post #88, so I think it's better to modify the code instead of writing a new one...

  96. #96
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie,
    I have decided to take some more time on this and give you a more detailed solution. This is in order that I can both learn from it and, hopefully, you will then be able to understand and more easily take the project further yourself. I think that will benefit us both in the long run.
    J'ai décidé de prendre un peu plus de temps à ce sujet et vous donner une solution plus détaillée. Ceci afin que je puisse à la fois apprendre de lui et, je l'espère, vous serez alors en mesure de comprendre et plus facilement prendre le projet plus vous-même. Je pense que cela va nous deux bénéficier à long terme.

    Up until now I have done solutions that work on the data and requirements given at that time, but then need to be modified ( often vey inefficiently ) time and time again. In my opinion this is not the way to take the project further. It is likely to result in extra unnecessary work for us both in the future.
    I may therefore need a bit more time to give you the next solution. I will post as soon as I can.
    Jusqu'à présent, je l'ai fait des solutions qui fonctionnent sur les données et les exigences données à ce moment-là, mais doivent être modifiés (Vey souvent inefficacement) maintes et maintes fois. À mon avis, ce n'est pas la façon de prendre le projet plus loin. Elle est susceptible d'entraîner des travaux supplémentaires inutiles pour nous deux à l'avenir.
    * Je peux donc besoin d'un peu plus de temps pour vous donner la solution suivante. Je vais poster dès que je peux.


    In the meantime I would ask again that you consider and inform me of any extra or different requirements that may come up in the future. Then I can incorporate them into the current solution that I am doing.
    En attendant, je voudrais demander à nouveau que vous considérez et me informer de toutes les exigences supplémentaires ou différentes qui peuvent se présenter à l'avenir. Ensuite, je peux les intégrer dans la solution actuelle que je fais.

    I will post as soon as I can. But I may not be able to complete that Today.
    Je vais poster dès que je peux. Mais je ne peux pas être en mesure de terminer aujourd'hui.



    Alan

  97. #97
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Alan,

    No problem thank you anyway for take time to help me! I'm really struggling right now as you can see the problem is quite complicated, so any help is highly appreciated.

  98. #98
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie,

    _ I have been able to spend some time on this project and will very likely post a solution shortly.

    _ In the meantime it would be helpful to have from you some
    _1) Clarification of the search criteria for determining if a row form Wb2 is Not in Wb1
    _ and
    _2) The search criteria for determining if a row in Wb1 is Not in Wb2. I expect I can probably guess what this search criteria is. However it would do no harm if you could spell it out clearly in the way I have shown below for the case of _1)

    _............................................

    So:
    Question 1)
    Please answer and confirm if this is correct: ?
    Please Login or Register  to view this content.
    _.....

    Question 2)
    Please give me a similar summary to the above, to give me the search criteria to be used when determining If a row in Wb1 is missing in Wb2 Then it should be added to the Differenced List for MadMax ( Lomax )


    _.....

    Alan and Jacky



    P.s. I have been doing some Test Post in support of this Thread here:
    http://www.excelforum.com/showthread...14#post4421988
    You might want to have a quick look there. But do not worry to much at this stage about understanding all that is there. There is rather a lot of information there to take in. ( I have been teaching Jacky some more about VBA through my work there ).
    I will try to summarise that information when I post my next solution to you in this Thread. ( I or Jacky except we will be able to spend some more time on this project at around Tuesday or Wednesday )


    P.s.s. I may have the various Worksheet and Difference reference Names , Caceis , LoMomax, , board etc a bit mixed up. !! - It is has not always been clear to me which is which. I try to be specific when talking about
    Wb1 is typically tthe main Workbook where all our macro codes have been. This is typically a .xlsm File. The first Worksheet in this file is typically referred to as ws1 in our codes
    and
    Wb2 is typically a .xls File and in the first Worksheet is typically referred to in our codes by ws2
    Last edited by Doc.AElstein; 07-03-2016 at 04:48 PM. Reason: Took my Dik out ..... Oh er missus

  99. #99
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi,

    Question 1) It is correct
    Question 2)The search criteria for 2 wb is : ISIN code & NAV_Date & value_Date & S/R type & nature & amount, but the last 3 criterias are more complexes in wb1 (see question 1)

  100. #100
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie
    Quote Originally Posted by shinpencil View Post
    Question 1) It is correct
    ....
    Thanks

    Quote Originally Posted by shinpencil View Post
    Question 2)The search criteria for 2 wb is : ISIN code & NAV_Date & value_Date & S/R type & nature & amount, but the last 3 criterias are more complexes in wb1 (see question 1)
    I do not fully understand your answer.

    _2a) nature is no longer used by us in the string of concatenated values used as our Unique Key.
    see my post 89
    http://www.excelforum.com/excel-prog...ml#post4413900

    Alan

  101. #101
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Yes you're right I think "nature" can't be used bacause there isn't an exact value of it. So the search criterias will be: ISIN code & NAV_Date & value_Date & S/R type & amount
    And plz I would like to add something in the "Differences" sheet: As you can see in the wb2 below there "Fund share name" column, it is possible to have "Fund share name" value in the "Differences" sheet for wb2?
    Attached Files Attached Files
    Last edited by shinpencil; 07-04-2016 at 09:53 AM.

  102. #102
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    It will look like that in the "Differences" sheet after adding "Fund share name":
    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    2 Missing order in Lomax 04/07/2016
    3
    4 LU051 31/05/2016 06/03/2016 7260.9
    5
    6 Missing order in EFS 04/07/2016
    7 LU107 31/05/2016 06/03/2016 18109.44 Lyx
    8 LU107 31/05/2016 06/03/2016 1600708.4 Lyx
    9 LU107 31/05/2016 06/03/2016 191746.33 Lyx
    10 LU107 31/05/2016 06/03/2016 328689.82 Lyx
    11 LU107 31/05/2016 06/06/2016 139.36 Sw
    12 LU051 31/05/2016 06/03/2016 240 Abs
    13 LU051 31/05/2016 06/03/2016 6780 Abs
    14 LU051 31/05/2016 06/03/2016 0.9 Abs
    15 LU128 31/05/2016 06/01/2016 15000 Lyx
    16 LU025 31/05/2016 06/01/2016 25400 Lyx
    17 LU139 31/05/2016 06/02/2016 400000 Lyx
    18 LU053 31/05/2016 06/01/2016 8000 Lyx
    19 LU053 31/05/2016 06/01/2016 5000 Lyx
    20 LU095 31/05/2016 06/01/2016 195000 Lyx
    Sheet: Differences

  103. #103
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie
    _1)
    Quote Originally Posted by shinpencil View Post
    ...... you can see in the Wb2 below there "Fund share name" column, it is possible to have "Fund share name" value in the "Differences" sheet for wb2?
    Would “Fund share name” also to be found in Wb1 Ws1?
    It does not appear in the File
    Wb1Post84.xlsm
    which you uploaded in Post #101

    The current code is comparing concatenated strings of some cell values from each Worksheet.
    If “Fund share name” is included in one Ws string and not the other Ws string, then every data line will be different. I do not think you want that. Think carefully about exactly what you are asking for.

    If you only want “Fund share name” in one final differences output, then this is a major alteration and modification to the current program. It is possible but will make the Code even longer and more complicated.


    _2)
    Please answer fully my Question 2) from post #98. - Give me a similar set of criteria for Wb1 as you gave me for Wb2 in Post #84. Otherwise I have to keep guessing what you want.


    _ 3) The screenshot you gave me in post #102 is very clear and helpful, Thank you
    But Please Always give me a similar screen shot of the two Data data ranges from
    Wb1, Ws1
    And
    Wb2, ws2
    Which are those which should produce those difference results.

    Currently I have many different Files from you and it is getting very difficult to keep everything well ordered. So Please always give screenshots in groups of three.
    Wb1, Ws1
    Wb2, ws2
    Worksheet Differences
    And try again to reduce the test data to the mimimum number of rows possible to demonstrate all scenarios

    I appreciate this is extra work for you. But you must help me help you.
    It is very hard to check and Debug codes when lots of data rows are included.

    _4)
    I will have some time tomorrow to look again at your requirement. Please try to get the information to me before then if possible. After that it may be a few more days before I can help you again

    Alan

  104. #104
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    1) Ok no problem I will try to figure it myself

    2) The search criteria for wb1 are the same: ISIN code & NAV_Date & value_Date & S/R type & amount

    Thank you!
    Last edited by shinpencil; 07-05-2016 at 05:08 AM.

  105. #105
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie

    ? you edited your last post as I was answering ???
    I am answering now your original Post...
    _.......

    _1) So are you saying,. You want "Fund share name" included in wb2 in the "differences" but it is not used in the comparison string which is used to find the differences. (ISIN code & NAV_Date & value_Date & S/R type & amount ) It does not appear in ws1.
    If so I can do that. But i would not do it as You suggested. This is doing it „after the event“ and very messy. I will think of a way to include that info when i create the comparison string, but I will not use that info in the comparison. I will need to re write some code, but it will be better for us both, as I will allow for other such request in the future, ( such as adding other column info )

    _2) In our current Files, Wb2 is compared against one of 4 Dictionaries corresponding to the 4 criteria ( conditions ) you gave in Post #84. I also summarised it again in post #98 )

    So I am still not too clear what I do for when I take Wb1 and look to see what is missing in it from Wb2.
    Please think carefully about what you want.
    You ( we ! ) are confusing two things

    _(i) This ... ISIN code & NAV_Date & value_Date & S/R type & amount .... is our string of concatenated cell values used to check for differences. This is not the search criteria that I am talking about and asking about. ( It is the comparison criteria perhaps )

    _(ii) The search criteria I meant is the conditions you gave in post #84, and I summarised again on Post #98.

    Maybe I can guess what you want. let me try to tell you what you want

    I have 4 Dictionaries for wb1 as before:

    Wb1DikQRedIfWb2NUSRR made with amount = Quantity Red
    Wb1DikQSubIfWb2NUSRS made with amount = Quantity Sub
    Wb1DikARedcyIfWb2NASRR made with amount = Amount Red class ccy
    Wb1DikASubcyIfWb2NASRS made with amount = Amount Sub class ccy

    Please Login or Register  to view this content.
    _.....

    I need now 4 Dictionaries for Wb2

    DikWb2NUSRR made from Wb2 if nature "Unit" and S/R type "Redemption"
    DikWb2NUSRS made from Wb2 if nature "Unit" and S/R type "Subscription"
    DikWb2NASRR made from Wb2 -if nature "Amount" and S/R type "Redemption”
    DikWb2NASRS made from Wb2 -if nature "Amount" and S/R type "Subscription"

    When looking for missig infomation in Wb1 i will compare

    Wb1DikQRedIfWb2NUSRR to DikWb2NUSRR
    Wb1DikQSubIfWb2NUSRS to DikWb2NUSRS
    Wb1DikARedcyIfWb2NASRR to DikWb2NASRR
    Wb1DikASubcyIfWb2NASRS to DikWb2NASRS

    Is this correct ?

    _...................

    _3) I think I can write the full code for you.

    The best you can do to help me would be to give me Three screen shots of
    Wb2
    Wb1
    The differences Worksheet.

    Please take your time. Think very carefully about the data you choose.
    Keep the data to a minimum, but select it carefully so that it demonstrates and tests different scenarios. That is the best help you can give me at this stage. It is much more difficult for me to try to modify or change code attempts by you that do not work, or need later modification. I will do my best to make the code as flexible as possible to allow for what I can guess you might want later !!
    Alan...

    _...........................

    P.s....
    Quote Originally Posted by shinpencil View Post
    Yes the code in post #90 did work for 2 files in post #84 like I said, but when I tried it with 2 new files in post #93 above, the result is false (the result is displayed in the ''Differences'' sheet).
    I still want that it should look like the result in post #88,
    so I think it's better to modify the code instead of writing a new one...
    I do not agree!!
    Je ne suis pas d'accord!

    _...... I do not want to modify yet again the code for the reasons I mentioned in Post #92. In my opinion the thing will likely get out of hand as for example in these Threads
    http://www.excelforum.com/excel-prog...ext-sheet.html
    http://www.excelforum.com/excel-prog...ml#post4202258
    Such continual additions and modifications are only really practicable to be done by the Person Responsible for the Code. That is you , not me.
    I have learnt some useful things from working on this project, and so will offer one final solution for you. I will try to consolidate , summarise and explain as much as possible for all our benefit ( you , Jacky and me !! )
    I will write a Final code and attempt to adapt it for your current requirement....It will be as flexible as possible allowing you to do easy changes in the future...
    ( I assume you would like me to do this ? – corrrect ? )
    Last edited by Doc.AElstein; 07-05-2016 at 05:55 AM.

  106. #106
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Smile Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi,

    1) Yes i modified the last post because I think I could do this one so no need to waste your time on it
    2) Yes I would like that plz! In the same time I would try to work on the macro too. I'm trying to have the "nature" in the search criteria because it'll be easier to identify the number in the column D in the result to know it's an amount or an unit. And I'm also trying to delete the line in the result if the amount or unit (in column D) equal to 0
    Last edited by shinpencil; 07-06-2016 at 09:53 AM.

  107. #107
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    OK

    It would also still be very helpful to have the three screenshots I asked for.
    That would be:

    _ - useful to both of us when debugging and testing.
    _ - It would also be very helpful if we are both using the same test data so as to compare our final results.
    _ - And it would also clarify what you want your output to look like

    To summarise what I a asking for.

    _A) A screenshot of Ws2
    _B) A screenshot of Ws1

    The data should be as few rows as possible but the data should be chosen carefully to test out the different conditions.

    _C) A difference Worksheet Screenshot.
    This should look exactly as you wish it to look based on the data you choose for A) and B)

    It is difficult for me to progress without this info. This is because you are changing and adding to what you require. If I know as much as possible from the outset, then I have the best chance to get it right first time!!!!

    Alan

  108. #108
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Yes I attach here 2 wb: wb1 in the compare file and wb2, and the result in already the "Differences" sheet. It's the macro in post #90 and it worked well, I got the right result, there just a problem: It display all order having an amount or unit (column D) equal to 0, which I don't want. It should look like that:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    2 Missing order in Lomax 05/07/2016
    3
    4 There is no difference
    5
    6 Missing order in EFS 05/07/2016
    7 LU05137410 19/05/2016 24/05/2016 9 Abs
    8 LU08126074 19/05/2016 24/05/2016 612.75 Abs
    9 LU08126096 19/05/2016 24/05/2016 8.5 Abs
    10 LU08324354 18/05/2016 19/05/2016 6300 Lyx
    Sheet: Differences
    Attached Files Attached Files
    Last edited by shinpencil; 07-05-2016 at 10:00 AM.

  109. #109
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie
    Thanks for the Files

    But it is a bit difficult for me to work with.
    Wb1, the Compare File is a bit big to work with when developing the program.
    But I can probably work with that.. if i must. ( It would be a lot easier if you can reduce that data to a size that would fit on a screenshot like you did for the differences Worksheet.. )

    You appear to be just looking for the differences in Wb2, that is to say what is missing in Wb2 compared to Wb1

    You are not giving me any data that enables me to write and test a code that will also display the differences in Wb1, that is to say what is missing in Wb1 compared to Wb2.
    You must give me data that has differences and you must show those differences in the differences Worksheet Screenshot.
    You must try to choose data that will test out the conditions.
    I cannot test a program that will give you differences if you do not supply me with data that should produce differences.

    Please read my Post #107 again.

    It would really be ideal and best for both of us if you could supply the Screenshots as I asked for.
    But there is not so much of a rush now.
    I am running out of time to help you Today.

    I will try to do a little more now, but I may have to continue looking at this in a couple of days now.
    If you wish me to look at a code that will also give you differences in Wb1 compared to Wb2, then please try to supply me with the test data I asked for.

    Alan.

    EDIT: P.s. Well done on your progess and continued learning
    ( I looked at your File. But unfortunately I have no idea what you are doing. I do not understand where any of your results are coming from. It is always very difficult to understand somebody else’s code )

    EDIT: P.S. Eh bien fait sur vos progrès et l'apprentissage continu
    (Je regardais votre dossier. Mais, malheureusement, je ne sais pas ce que vous faites. Je ne comprends pas où l'un de vos résultats viennent. Il est toujours très difficile de comprendre le code de quelqu'un d'autre )
    Last edited by Doc.AElstein; 07-05-2016 at 06:14 PM.

  110. #110
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Alan,

    I think the data in post #84 is the one you are looking for (what is missing in Wb1 compared to Wb2). If isn't the case then I attach here 2 wb that I modified to have what you demanded.
    I tried to reduce the data of 2wb to do the sreenshot but it's still bigger than the size autorized. This time the result I got in false for "Missing order in lomax", it should look like that:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    2 Missing order in Lomax 06/07/2016
    3
    4 LU05330320 19/05/2016 20/05/2016 5800
    5 LU05330338 19/05/2016 20/05/2016 4500
    6
    7 Missing order in EFS 06/07/2016
    8 LU05137410 19/05/2016 24/05/2016 9 Abs
    9 LU08126074 19/05/2016 24/05/2016 612.75 Abs
    10 LU08126096 19/05/2016 24/05/2016 8.5 Abs
    11 LU08324354 18/05/2016 19/05/2016 6300 Lyx
    Sheet: Differences
    Attached Files Attached Files
    Last edited by shinpencil; 07-06-2016 at 08:51 AM.

  111. #111
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Thanks,
    I will take a look tomorrow and let you know
    Alan

  112. #112
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare 2 workbooks - Which ones ??? .... ;)

    Hi Elie.
    The data in “Sheet1” ( Ws1 ) in the Main Compare File is different from Post 84 / 93 and Post 110
    ( I only post the first few lines below because of the authorized size limit )**

    Post #93 ( first few rows )**
    Trade date Name Fund Name Ccy Ref Ccy Class Share Class Isin Quantity Sub Amount Sub class ccy Amount Sub Ref ccy Value date Sub Amount Red Ref ccy Quantity Red Amount Red Class ccy Value date Red
    31.05.2016
    L L E LU10797034
    0
    0
    0
    0
    0
    0
    E LU09854243
    0
    0
    0
    0
    0
    0
    Sheet1
    Full Screenshot: http://www.excelforum.com/showthread...15#post4424088
    _................................................................


    Post #110 ( first few rows )**
    Trade date Name Fund Name Ccy Ref Ccy Class Share Class Isin Quantity Sub Amount Sub class ccy Amount Sub Ref ccy Value date Sub Amount Red Ref ccy Quantity Red Amount Red Class ccy Value date Red
    19.05.2016
    MULTI MULTI E E E LU08544236
    1600
    0
    0
    20.05.2016
    0
    0
    0
    E E E LU08544239
    0
    0
    0
    0
    0
    0
    Fund Total:
    0
    0
    MULTI E E E LU08556925
    0
    0
    0
    0
    0
    0
    E E E LU10406886
    0
    0
    0
    0
    0
    0
    Sheet1
    Full Screenshot: http://www.excelforum.com/showthread...17#post4427937


    _..............................................


    Post #84 ( first few rows )**
    Quote Originally Posted by shinpencil View Post
    ....I think the data in post #84 is the one you are looking for .....
    I think that in incorrect. I do not think I should be using that data
    Trade date Name Fund Name Ccy Ref Ccy Class Share Class Isin Quantity Sub Amount Sub class ccy Amount Sub Ref ccy Value date Sub Amount Red Ref ccy Quantity Red Amount Red Class ccy Value date Red
    31.05.2016
    E C A LU107
    0
    0
    0
    0
    0
    0
    E C A LU098
    0
    0
    0
    0
    0
    0
    E C A LU140
    0
    0
    0
    0
    0
    0
    Sheet1
    Full Screenshot: http://www.excelforum.com/showthread...15#post4424088
    _............................


    Which should I be using
    I think I should use that from Post 110. Correct?


    Question 2)..
    Is this the correct Data that I should be using for Wb2 ?
    http://www.excelforum.com/showthread...t=#post4427605


    Question 3)..
    The code I am writing will allow you to include any other headings you wish on the Difference Worksheet. So Think carefully if you wish any additions. Otherwise I will assume that the Screenshot from Post #110 is still what you want

    Alan
    Last edited by Doc.AElstein; 07-07-2016 at 07:16 AM.

  113. #113
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Alan,

    Yes I confirm it's the newest data from post #110 that you should use. And the Screenshot from Post #110 is still the result I want for those data.

  114. #114
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Some More Notes on Posting. Not too important

    Hi Elie,

    This post is not too important. I will try to address the main issues in subsequent Posts!

    Ce poste n'a pas d'importance. Je vais essayer de répondre aux principales questions dans les messages ultérieurs!


    First a quick word about posting your data to me, as I have been getting a bit confused with all the different info I have for you.... So Please, Note for the next time please:

    . Your Wb1 Ws1....
    _a) for Post 108 was a bit big ( a few less rows would have been a lot easier to work with... but then I saw that you did in fact reduce it for Post 110. - Thanks ) ....
    But I missed that initially and got very confused as I have so many different data versions from you!! I confused Post 108 with Post 110 That was my fault – But screen shots help to avoid the confusion,,. -

    So please try to include a Screenshot if possible. It helps to make everything clear. For well reduced data , as you did in Post 110, the authorized size will normally work. But You may need to post one screen shot per Post. One way to do that is that You can do, as I do, and Post in the test Forum
    http://www.excelforum.com/development-testing-forum/
    , and then copy the URL link from the address bar and paste that URL in the Main Thread , ( _....The Address Bar is usually a long thin Bar at the top of your Browser Window
    _.... See for example what I did for you here:
    http://www.excelforum.com/showthread...29464&posted=1
    _..... )
    And here:
    http://www.excelforum.com/showthread...16#post4427605

    Also,
    You can squeeze a bit more on if, for example you select No Headers option in the screenshot Tool I gave you. For example I managed to get all from Post 108 in here
    http://www.excelforum.com/showthread...17#post4427937

    _b) Also please include the Post Number in all your File Names,. This will help us both to keep a better order. For example do this
    Compare EFPost108.xlsm
    Wb2_EFPost108.xls
    Or
    Wb2Post110.xls
    Etc.. etc...

    _c) you had no Duplicators. Please try to pick data that test all important possible scenarios. ( I added a few duplicate rows and tested the codes and they seem to work in that respect, that is to say they ignore duplicate rows )

    _d) you save me most time if you give clear screenshots of what you want and what you initially have. ( “A Picture that can be copied to a Spreadsheet pastes a thousand words” )
    Une image vaut mieux que mille mots. Une image qui peut être copié dans une feuille de calcul est encore mieux


    Alan


    P.s
    Football
    France 2 Germany 0

    Match de football
    Frankreich 2 Allemagne 0


    And

    Portugal 1 France 0 ????????

    What happened ???


  115. #115
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Back to your Problem and current requirement.
    Logic Missing Wb2, Ws1. What do I have in Wb2, Ws1 that is not in Wb1, Ws1...

    I am working on your Project and in parallel looking at Dictionaries and comparing data in General

    I can get at your required results for Missing order in EFS 06/07/2016 in many ways. See for example here:
    http://www.excelforum.com/showthread...18#post4429324
    And my codes will allow any Output. You can include nature, Fund share name,, etc. etc. The compare criteria and the data copied to the Output Differences can easily be different.

    _.............................

    As for Missing order in Lomax 06/07/2016,
    I think we have not quite got our logic clear...
    I think we do not yet know what we want...
    Or we have not clearly stated it.

    I will discuss that in the next post.
    _.............................................

    This is the Logic for Missing order in ( Wb2 ) EFS 06/07/2016: ( Cacky , Caceis etc )
    _........I take each row in Wb2 Ws1 and look for it in the appropriate of the 4 dictionaries for Wb1, Ws1. If I do not find that row from Wb2 Ws1, then I add a version of that row into my Differences Output. ( The appropriate Dictionary is determined by the 4 conditions )
    This logic appear correct and gives us the correct results.

    The above logic clearly works to get the results you want..

    Green := Not Missing
    Red := Missing

    All Rows in Wb2, Ws1 must be one or the other.
    For example, consider Acceptance conditions,

    ' DikWb2NUSRR made from Wb2 if nature "Unit" and S/R type "Redemption"
    LU05137410|19.05.2016|24.05.2016|9.00
    LU05330330|19.05.2016|20.05.2016|10300.00
    LU08126074|19.05.2016|24.05.2016|612.75
    DBDikWb2NUSRR_090716
    And the Appropriately chosen ( based on the conditions ) Dictionary from Wb1, Ws1:
    LU08544236|19.05.2016|20.05.2016|0.00
    LU08544239|19.05.2016|0|0.00
    LU05330320|19.05.2016|20.05.2016|0.00
    LU05330321|19.05.2016|0|0.00
    LU05330328|19.05.2016|0|0.00
    LU05330330|19.05.2016|20.05.2016|10300.00
    LU05330332|19.05.2016|0|0.00
    LU05330333|19.05.2016|0|0.00
    LU05330334|19.05.2016|0|0.00
    LU05330335|19.05.2016|0|0.00
    LU05330336|19.05.2016|0|0.00
    LU05330337|19.05.2016|0|0.00
    LU05330338|19.05.2016|20.05.2016|0.00
    LU05330340|19.05.2016|0|0.00
    LU05330345|19.05.2016|0|0.00
    LU05330346|19.05.2016|0|0.00
    DBQuantity RedWb1FukSukmyDik090

    _........................................................

    ' DikWb2NUSRS made from Wb2 if nature "Unit" and S/R type "Subscription"
    LU08126096|19.05.2016|24.05.2016|8.50
    LU08544236|19.05.2016|20.05.2016|1600.00
    LU08324354|18.05.2016|19.05.2016|6300.00
    DBDikWb2NUSRS_090716
    And the Appropriately chosen ( based on the conditions ) Dictionary from Wb1, Ws1:
    LU08544236|19.05.2016|20.05.2016|1600.00
    LU08544239|19.05.2016|0|0.00
    LU05330320|19.05.2016|20.05.2016|5800.00
    LU05330321|19.05.2016|0|0.00
    LU05330328|19.05.2016|0|0.00
    LU05330330|19.05.2016|20.05.2016|0.00
    LU05330332|19.05.2016|0|0.00
    LU05330333|19.05.2016|0|0.00
    LU05330334|19.05.2016|0|0.00
    LU05330335|19.05.2016|0|0.00
    LU05330336|19.05.2016|0|0.00
    LU05330337|19.05.2016|0|0.00
    LU05330338|19.05.2016|20.05.2016|4500.00
    LU05330340|19.05.2016|0|0.00
    LU05330345|19.05.2016|0|0.00
    LU05330346|19.05.2016|0|0.00
    DBQuantity SubWb1FukSukmyDik090
    _...............................................................


    ' DikWb2NASRR made from Wb2 -if nature "Amount" and S/R type "Redemption”
    This Dictionary is empty for your data
    _...............................................................

    ' DikWb2NASRS made from Wb2 -if nature "Amount" and S/R type "Subscription"
    This Dictionary is empty for your data
    _....................................................................


    Logic Missing Wb2, Ws1. Is correct All our codes work !!


  116. #116
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    The current Problem_....
    Back to Wb2 Ws1 Dictionaries...
    http://www.excelforum.com/showthread...18#post4429901

    etc....


    If : This is the logic for Missing order in Lomax 06/07/2016 ( MadMax )

    _1 ) First Make 4 Dictionaries for Wb2, Ws1, based on the previous selections conditions.: Selection conditions which of the 4 Wb1, Ws1 dictionaries were to be used.
    then
    __2)......For each Wb1, Ws1 Dictionary, ...._.._..
    _........I take each row that Wb1 Ws1 Dictionary, and look for it in the appropriate of the 4 dictionaries for Wb2, Ws1. If I do not find that row from Wb1 Ws1, then I add a version of that row into my Differences Output. ( The appropriate Dictionary is determined by the 4 conditions )


    Then : I will have many differences, - there are many rows in Wb1, Ws1 that do not appear in the Wb2 , Ws1 data given.
    http://www.excelforum.com/showthread...97#post4429463

    This logic, which I suggested, and was agreed to, gives , as said, many differences:
    Quote Originally Posted by Doc.AElstein View Post
    .......
    Maybe I can guess what you want. let me try to tell you what you want

    I have 4 Dictionaries for wb1 as before:

    Wb1DikQRedIfWb2NUSRR made with amount = Quantity Red
    Wb1DikQSubIfWb2NUSRS made with amount = Quantity Sub
    Wb1DikARedcyIfWb2NASRR made with amount = Amount Red class ccy
    Wb1DikASubcyIfWb2NASRS made with amount = Amount Sub class ccy

    Please Login or Register  to view this content.
    _.....
    I had only one main Wb2 Dictionary , but now_...
    _...I need 4 Dictionaries for Wb2 based on “Selection” conditions

    DikWb2NUSRR made from Wb2 if nature "Unit" and S/R type "Redemption" Acceptance Conditions
    DikWb2NUSRS made from Wb2 if nature "Unit" and S/R type "Subscription" Acceptance Conditions
    DikWb2NASRR made from Wb2 -if nature "Amount" and S/R type "Redemption” Acceptance Conditions
    DikWb2NASRS made from Wb2 -if nature "Amount" and S/R type "Subscription" Acceptance Conditions

    When looking for missing information in Wb1 I will compare

    Wb1DikQRedIfWb2NUSRR to DikWb2NUSRR
    Wb1DikQSubIfWb2NUSRS to DikWb2NUSRS
    Wb1DikARedcyIfWb2NASRR to DikWb2NASRR
    Wb1DikASubcyIfWb2NASRS to DikWb2NASRS

    Is this correct ?.........
    Answer was YES ..but clearly looking at the wanted results_......

    _... This is not correct !!!

    This logic gives many Missing rows. !!
    The sample hand filled in wanted Output, from you, has only two.
    http://www.excelforum.com/excel-prog...ml#post4426924


    Using Excel 2007 32 bit
    Missing order in Lomax 06/07/2016
    LU05330320 19.05.2016 20.05.2016
    5800
    LU05330338 19.05.2016 20.05.2016
    4500

    _
    I do not understand the Logic ?!?!
    _......Those two rows shown above are missing, that is correct... - but many more rows are also missing.
    There are many more than two missing rows.

    _..

    http://www.excelforum.com/showthread...=1#post4430751
    What next ? Where are we... Full Wb1 DictionAlanarries ( 4 of ) ?????
    http://www.excelforum.com/showthread...=1#post4430796

    I ( we ) think a bit more, maybe ???

    I try to think a bit more if i have the time tomorrow

    Alan

  117. #117
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Alan,

    Yes it is what I'm talking about, the result is not correct! I don't know why, still not find the problem...

    P/S: I have to say that Portugal have been very lucky this time, they scored one at the very last minute. Such a shame for French football team, we won almost all matches but unfortunately lost at the final!
    Anw congratulation to Portugal for their first champion title!
    Last edited by shinpencil; 07-13-2016 at 04:36 AM.

  118. #118
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie,
    Yes we Let Portugal win this once
    _...............................................

    Quote Originally Posted by shinpencil View Post
    ... the result is not correct! I don't know why, still not find the problem......
    Did you check out the Links I gave in Post #116 ?
    For example..
    Quote Originally Posted by Doc.AElstein View Post
    .......
    _..
    http://www.excelforum.com/showthread...=1#post4430751
    What next ? Where are we... Full Wb1 DictionAlanarries ( 4 of ) ?????
    http://www.excelforum.com/showthread...=1#post4430796
    ......
    I tried to explain the problem and a possible solution. Did you understand that?

    Alan

  119. #119
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Yes, I just checked out your posts, thank you for all the details, but like I said I would like to delete all the orders which have amount/unit equal to 0 in the "Differences" sheet, I'm trying to do it too...

  120. #120
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie

    This is extremely encouraging . Well done and keep at it.
    Cela est extrêmement encourageante . Bravo et continuez.

    It is very important that you only use codes that you are happy with and understand. That is very important, in my Opinion. I personally use no codes that I do not fully understand.
    Il est très important que vous utilisez uniquement des codes que vous êtes heureux avec et à comprendre. Cela est très important, à mon avis. Personnellement, j'utiliser aucun code que je ne comprends pas pleinement.

    I will not post anymore contributions on this Thread. I have just finished off my last posts over in the Appendix Thread today, starting from about here:
    Je ne vais pas plus poster des contributions sur ce sujet. Je viens de finir de mes derniers messages sur la discussion Annexe aujourd'hui, à partir d'environ ici:
    http://www.excelforum.com/showthread...t=#post4433099

    I think I have now achieved with my Codes all your requirements. But I think it is very important that you persevere and get your codes working, ( assuming you have not already done so ).
    Je crois avoir maintenant atteint avec mes codes à toutes vos exigences. Mais je pense qu'il est très important que vous persévérez et obtenir vos codes de travail, (en supposant que vous ne l'avez pas déjà fait).

    My codes are an interesting academic alternative. They do not use the Microsoft Scripting Runtime Dictionary library. I have Pseudo re.- invented the wheel there and produced Functions that do the same ( as well as producing an extra temporary DeBug sheet at every stage to show all “DiktionAlanArrays” and other Arrays, such as the Hash ( Perl associate ) Array for the Heading Name, Heading Column pairs that I produce as an alternative to your Diko_header Dictionary version.
    Mes codes sont une alternative intéressante académique. Ils n'utilisent la bibliothèque Dictionnaire Runtime Microsoft Scripting. J'ai Pseudo re.- inventé la roue là et produit des fonctions qui font la même (ainsi que la production d'une feuille supplémentaire de debug temporaire à chaque étape pour afficher toutes les "DiktionAlanArrays" et d'autres réseaux, comme le Hash (Perl associé) Tableau pour le nom des rubriques, la rubrique paires de colonnes que je produis comme une alternative à votre version Diko_header dictionnaire.




    I expect the Microsoft Scripting Runtime Dictionary library will outlive VBA ( and me Lol.. )
    Je pense la bibliothèque Dictionnaire Runtime Microsoft Scripting survivra VBA (et moi Lol ..)
    http://www.excelforum.com/showthread...t=#post4431326
    http://www.excelforum.com/showthread...t=#post4431239


    So it is probably better to keep using that.
    Donc, il est probablement préférable de continuer à l'utiliser.

    Good luck with your project. If you are able, when you have your solution, it would be very useful contribution to share it with us.
    Bonne chance pour votre projet. Si vous êtes en mesure, quand vous avez votre solution, il serait très utile contribution à la partager avec nous.

    Be a bit careful about just tacking on bits to your code that you have been getting from Help elsewhere. And remember to tell us when and where you get help elsewhere to avoid any of us duplicating efforts for you.
    Soyez un peu prudent sur tout vireur sur les bits à votre code qui vous sont donnés à partir de l'aide ailleurs. Et rappelez-vous nous dire quand et où vous obtenir de l'aide ailleurs pour éviter tout nous dupliquer les efforts pour vous.

    Alan


    Edit: So sorry about the sad news from France
    Edit: Alors, désolé les tristes nouvelles de la France
    Last edited by Doc.AElstein; 07-15-2016 at 05:23 AM.

  121. #121
    Registered User
    Join Date
    04-07-2016
    Location
    Paris, France
    MS-Off Ver
    MS Office 2010
    Posts
    68

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Alan,

    Yes I'm trying to get my code working, because it take time for me too to understand the code writing by others.
    Thank you again for all your efforts. You helped me a lot!

  122. #122
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Compare and Find missing rows in two sheets with different Headings and Formats

    Hi Elie
    You are welcome
    Good luck
    Alan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-18-2015, 12:36 AM
  2. [SOLVED] Error when averaging empty & non empty cells
    By simonlblea in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-09-2013, 09:04 AM
  3. Compare values in Array 1 and Array 2
    By kih1251 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2012, 02:06 PM
  4. [SOLVED] How to make an array of workbooks made up of an array of worksheets?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2012, 08:33 AM
  5. Program error when using Compare and Merge Workbooks
    By Gan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-16-2006, 03:50 AM
  6. [SOLVED] Need a insert a empty cell if compare different function
    By saici in forum Excel General
    Replies: 2
    Last Post: 06-09-2006, 03:18 AM
  7. Fixing a sorting error for empty rows (error '1004')?
    By StargateFan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1