LinkBack URL
About LinkBacks
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?
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?
Last edited by zbor; 06-17-2016 at 09:53 AM.
Can someone help me plz!
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.
Thanks but how can I correct it?
Last edited by shinpencil; 04-18-2016 at 09:48 AM.
Originally Posted by shinpencil
You must make those variables Arrays containing the data you wish them to have..
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.
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.
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.
Hi shinpencil
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
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
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 IE00B9D Unit EUR Redemption OUT IE00B9D Unit GBP Subscription IN IE00B9D Amount GBP Subscription IN IE00B9D Unit USD Subscription IN IE00B9D Unit USD Subscription IN IE00B9C Unit GBP Subscription IN IE00B9C Unit EUR Subscription IN IE00B9C Unit GBP Redemption OUT IE00B8B Unit EUR Subscription IN IE00B8B Amount EUR Redemption OUT IE00B8H Unit USD Redemption OUT IE00B8B Amount EUR Redemption OUT IE00B8B Amount EUR Subscription IN IE00B9C Unit GBP Redemption OUT IE00B9C Unit 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.
Thank you so much but I still have get mismatch error at the line "For i = LBound(header, 2) To UBound(header, 2)"...
Hi shinpencil
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.
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!!!
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.
Hi shinpencil,
You are welcome
_...........................................
_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:I just changed a bit and wrote it like that:
Please Login or Register to view this content.
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.
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.
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 Redemption IE00B9D 05.04.2016 12.04.2016 Unit Redemption IE00B9D 05.04.2016 12.04.2016 Unit Subscription IE00B9D 05.04.2016 12.04.2016 Amount Subscription IE00B9D 05.04.2016 12.04.2016 Unit Subscription IE00B9D 05.04.2016 12.04.2016 Unit Subscription IE00B9C 05.04.2016 12.04.2016 Unit Subscription IE00B9C 05.04.2016 12.04.2016 Unit Subscription IE00B9C 05.04.2016 12.04.2016 Unit Redemption IE00B8B 05.04.2016 12.04.2016 Unit Subscription IE00B8B 05.04.2016 12.04.2016 Amount Redemption IE00B8H 05.04.2016 12.04.2016 Unit Redemption IE00B8B 05.04.2016 12.04.2016 Amount Redemption IE00B8B 05.04.2016 12.04.2016 Amount Subscription IE00B9C 05.04.2016 12.04.2016 Unit Redemption IE00B9C 05.04.2016 12.04.2016 Unit Subscription IE00B9J 05.04.2016 12.04.2016 Unit Redemption IE00B9J 05.04.2016 12.04.2016 Unit Redemption IE00BR8 05.04.2016 12.04.2016 Amount Redemption IE00BR8 05.04.2016 12.04.2016 Unit Subscription IE00BR8 05.04.2016 12.04.2016 Amount Redemption IE00BR8 05.04.2016 12.04.2016 Amount Subscription IE00BS7 05.04.2016 12.04.2016 Amount Subscription IE00BS7 05.04.2016 12.04.2016 Amount Redemption IE00BS7 05.04.2016 12.04.2016 Unit Redemption IE00BS7 05.04.2016 12.04.2016 Amount Subscription IE00BS7 05.04.2016 12.04.2016 Amount Subscription IE00BS7 05.04.2016 12.04.2016 Amount Subscription IE00BS7 05.04.2016 12.04.2016 Unit Redemption IE00BS7 05.04.2016 12.04.2016 Unit Redemption IE00BS7 05.04.2016 12.04.2016 Unit Subscription IE00BRJ 05.04.2016 12.04.2016
Sheet2
Jack
( _.. code to follow in next Post, Post # 20 )
Last edited by JackSheet_1; 04-20-2016 at 09:42 AM.
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 )
Au RevoirPlease Login or Register to view this content.
_... Jack ... ..
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.Omg thanks a lot! You really are my lifesaver!
Nice to meet you here. Au revoir and see you next time maybe
Thanks for the feedback.
Goodbye.
Jack
P.s.
please mark Thread as solved, Thanks:
Last edited by JackSheet_1; 04-21-2016 at 09:01 AM.
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
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
The files are below. It supposed to have no difference between wb1 and wb2 but there is
Last edited by shinpencil; 04-25-2016 at 05:45 AM.
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 Missing order in Lomax Redemption IE00B905SX62 26/04/2016 Unit Subscription IE00B905SX62 26/04/2016 Unit Redemption IE00B905SX62 26/04/2016 Unit Subscription IE00B905SX62 26/04/2016 Unit Redemption IE00B9CB6D86 26/04/2016 Unit Redemption IE00B905SX62 26/04/2016 Amount
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 Missing order in Lomax Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B905SX62 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit Subscription IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Subscription IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00B905SX62 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B8BS6228 26.04.2016 04.05.2016 Amount Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00BRJG7170 26.04.2016 04.05.2016 Unit Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00BRJG6Z44 26.04.2016 04.05.2016 Amount Redemption IE00B905SX62 26.04.2016 04.05.2016 Amount Subscription IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Subscription IE00BRJG7170 26.04.2016 04.05.2016 Unit Redemption IE00B8BS6228 26.04.2016 04.05.2016 Unit Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit Redemption IE00BRJG6Z44 26.04.2016 04.05.2016 Unit Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit
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.
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.
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.
_.................................................
I have no idea what this means of from where it come.
Using Excel 2007
Row\Col Missing order in Lomax Redemption IE00B905SX62 26/04/2016 Unit Subscription IE00B905SX62 26/04/2016 Unit Redemption IE00B905SX62 26/04/2016 Unit Subscription IE00B905SX62 26/04/2016 Unit Redemption IE00B9CB6D86 26/04/2016 Unit Redemption IE00B905SX62 26/04/2016 Amount
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.
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.
_..................................
_.........................................................
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 Missing order in Lomax Redemption IE00B905SX62 26/04/2016 Unit Subscription IE00B905SX62 26/04/2016 Unit Redemption IE00B905SX62 26/04/2016 Unit Subscription IE00B905SX62 26/04/2016 Unit Redemption IE00B9CB6D86 26/04/2016 Unit Redemption IE00B905SX62 26/04/2016 Amount
_.............................
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 Subscription IE00B9CB586 26.04.2017 04.05.2016 Pftang Subscription IE003837546D86 26.04.2016 04.05.2016 OLE Redemption IE00B9988375X62 26.04.2016 04.05.2016 wigwam Subscription IE0047SJ09 26.04.2016 04.05.2016 biscuitbarrel 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 Code Gives This But it is Wrong Missing order in Lomax Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit
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 This is what I want the code to give me Missing order in Lomax Redemption IE00B9DCSJ09 26.04.2016 04.05.2016 Unit Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount Redemption IE00BS7K2808 26.04.2016 04.05.2016 Unit Subscription IE00B8BS6228 26.04.2016 04.05.2016 Unit Redemption IE00B905SX62 26.04.2016 04.05.2016 Unit Subscription IE00BS7K2808 26.04.2016 04.05.2016 Unit Subscription IE00B905SX62 26.04.2016 04.05.2016 Unit Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Unit
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 )
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.
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 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.) Redemption OUT IE00B9DCSJ09 Amount EUR Redemption OUT IE00B9CB6D86 Amount GBP Redemption OUT IE00B9CB6D86 Amount GBP Subscription IN IE00B9CB6D86 Amount GBP Subscription IN IE00BX7RQ732 Amount EUR
Board
And this in file wb2.xls ( in code wb2 )
Using Excel 2007
Row\Col S/R type Order status Fund share code Fund share name Fund name Pricing Date Value Date Quantity Nature Net amount Redemption Estimated IE00B9DCSJ09 Amount Redemption Estimated IE00B9CB6D86 Unit Subscription Estimated IE00BRJ Amount Redemption Estimated IE00BS7 Amount Subscription Estimated IE00BS7 Amount Redemption Estimated IE00BS7 Amount Subscription Estimated IE00BS7 Amount
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 Missing order in Lomax Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Amount Redemption IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00B9CB6D86 26.04.2016 04.05.2016 Amount Subscription IE00BX7RQ732 26.04.2016 04.05.2016 Amount
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 key data1_Dico.Item(key) key data2_Dico.Item(key) Redemption#IE00B9DCSJ09#26.04.2016#04.05.2016#Amount#19193.4600 Redemption#IE00B9DCSJ09#26.04.2016#04.05.2016#Amount#19193.4600 Redemption#IE00B9CB6D86#26.04.2016#04.05.2016#Amount#6113.2500 Redemption#IE00B9CB6D86#26.04.2016#04.05.2016#Unit#2480.0000 Redemption#IE00B9CB6D86#26.04.2016#04.05.2016#Amount#24024.0000 Subscription#IE00BRJ#26.04.2016#04.05.2016#Amount#1501287.4800 Subscription#IE00B9CB6D86#26.04.2016#04.05.2016#Amount#3900.0000 Redemption#IE00BS7#26.04.2016#04.05.2016#Amount#369.1500 Subscription#IE00BX7RQ732#26.04.2016#04.05.2016#Amount#19835.0000 Subscription#IE00BS7#26.04.2016#04.05.2016#Amount#80999.5900 Redemption#IE00BS7#26.04.2016#04.05.2016#Amount#44515.4800 Subscription#IE00BS7#26.04.2016#04.05.2016#Amount#243263.4000
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.
Okay thank you and your wife anw for your help!
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
Hi shinpencil,
_..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
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 ! )HTML Code:
Alan
Last edited by Doc.AElstein; 04-28-2016 at 11:46 AM.
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.
Last edited by shinpencil; 04-28-2016 at 08:40 AM.
Hi
It is always coutious to feedback , tell us, and share your working solution to a problem you have solved with our help
_...___________________________________________
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 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 SUB IN IE00B6 M entire SUB IN IE00B5 P column RED OUT IE00B6 P is empty. RED OUT IE00B6 P So CurremtRegion SUB IN IE00B6 M will Fail 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.)
The code given herePlease Login or Register to view this content.
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.
Results For post #40 above.
Using Excel 2007
Row\Col 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 SUB IN IE00B6 M SUB IN IE00B5 P RED OUT IE00B6 P RED OUT IE00B6 P SUB IN IE00B6 M
CSVImport
_.......................
Code Again:
AlanPlease Login or Register to view this content.
Last edited by Doc.AElstein; 04-28-2016 at 01:38 PM.
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.
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.
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.
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.
Forum Software initiated Duplicate post
Last edited by Doc.AElstein; 05-04-2016 at 09:43 AM.
Forum Software initiated Duplicate post
Last edited by Doc.AElstein; 05-04-2016 at 09:43 AM.
Thankyou
Merci
_.....
But
Mais
Also
Aussi
.......
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.
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.
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.
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.
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”
I run this code and I choose Wb1.csv when prompted for a csv File to importPlease Login or Register to view this content.
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 MT502 Ref. Transaction Type Input/Output Transaction Ref. ISIN Code NAV Date Value Date Share Nb. InvestAmountent TyUnite Fund Amount (Client Cur.) Client Cur. Redemption OUT IE00B9D Unit EUR Redemption OUT IE00B9C Unit GBP Redemption OUT IE00B9C Unit GBP Subscription IN IE00B9C Amount GBP Subscription IN IE00BX7 Amount EUR
Board
_.....
Now i open wb2.xls
I copy this code from Post #44 into the File, “Compare Caceis.xlsm”
I run it in debug ( F8 ). I get the error you do... error herePlease Login or Register to view this content.
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:
This is because you are also changing the Header, which I presume you do not wish to?Please Login or Register to view this content.
So you need to make this change
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 ) :Please Login or Register to view this content.
Using Excel 2007
Row\Col Share Nb. Investment Type Fund Amount (Client Cur.) Unit Unit
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 Missing order in Lomax 04.05.2016 Redemption IE00B9C 26.04.2016 04.05.2016 Unit Redemption IE00BS7 26.04.2016 04.05.2016 Unit Redemption IE00B9C 26.04.2016 04.05.2016 Unit Subscription IE00B9D 26.04.2016 04.05.2016 Unit Redemption IE00B9D 26.04.2016 04.05.2016 Unit Redemption IE00B8B 26.04.2016 04.05.2016 Unit Redemption IE00B8B 26.04.2016 04.05.2016 Unit Redemption IE00B9C 26.04.2016 04.05.2016 Unit Subscription IE00B9D 26.04.2016 04.05.2016 Unit Redemption IE00B9C 26.04.2016 04.05.2016 Unit Redemption IE00B8B 26.04.2016 04.05.2016 Unit Redemption IE00BS7 26.04.2016 04.05.2016 Unit Redemption IE00B9C 26.04.2016 04.05.2016 Unit Subscription IE00B9C 26.04.2016 04.05.2016 Unit Redemption IE00B8B 26.04.2016 04.05.2016 Unit Redemption IE00B8B 26.04.2016 04.05.2016 Unit Subscription IE00BS7 26.04.2016 04.05.2016 Unit Redemption IE00B9D 26.04.2016 04.05.2016 Unit
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 ;)
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
You are welcome, thanks for the feedback.
Vous êtes les bienvenus, merci pour les commentaires
Alan
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?
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.
I'm fine thank you, and you?
Thank you for your kindness!
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:
This ensures only rows with Redemption or Subscription in first column are consideredPlease Login or Register to view this content.
_...........................
Next post gets bacK to the Problem, and request for help from Post #55................
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
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.) Redemption OUT IE00B64 Amount EUR Subscription IN IE00B64 Unit EUR Subscription IN IE00B64 Amount EUR Redemption OUT IE00B64 Unit EUR Subscription IN IE00B64 Amount EUR Subscription IN IE00B53 Unit USD Redemption OUT IE00B53 Unit USD Redemption OUT IE00B64 Unit EUR Subscription IN IE00B64 Unit EUR Subscription IN IE00B64 Amount EUR Redemption OUT IE00B61 Unit EUR Redemption OUT IE00B64 Amount EUR
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
S/R type Order status Fund share code Fund name Pricing Date Value Date Import Date Settlement Date Publication Date Nature Quantity Net amount 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 Redemption Estimated IE00B61 25/05/2016 01/06/2016 25/05/2016 Unit Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Amount Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Amount Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Amount Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Amount Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Unit Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Unit Redemption Estimated IE00B64 25/05/2016 01/06/2016 25/05/2016 Unit Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Amount Subscription Estimated IE00B64 25/05/2016 31/05/2016 25/05/2016 Unit Redemption Estimated IE00B53 25/05/2016 01/06/2016 25/05/2016 Unit Subscription Estimated IE00B53 25/05/2016 31/05/2016 25/05/2016 Unit
Sheet: Sheet1
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:
Imported CSV File, Dictionary data1_DickOh: key ; Item Compare File, wb2. Dictionary data2_DickOh: Key ; Item Redemption#IE00B64#25.05.2016#31.05.2016#Amount#50000.0000 Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908.5938 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320.7700 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937.6900 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000.0000 Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191.1300 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69.5200 Redemption#IE00B64#25.05.2016#01.06.2016#Amount#50000.0000 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574.0000 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000.0000 Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18.4000 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69.5240 Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092.8000 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320.7652 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3.8200 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3.8150 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000.0000 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574.0000 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937.6900 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000.0000 Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908.5900 Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092.8000 Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191.1300 Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18.4000
Sheet: DebugSheet
_.......................................
Missing order in Lomax 26.05.2016 Redemption IE00B64 25.05.2016 31.05.2016 Amount Subscription IE00B64 25.05.2016 31.05.2016 Unit Redemption IE00B64 25.05.2016 01.06.2016 Unit Redemption IE00B64 25.05.2016 01.06.2016 Unit Redemption IE00B61 25.05.2016 01.06.2016 Unit
Sheet: Differences
_...
I see no problem. The code appears to be functioning correctly
?? I try again in next post.......
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:
Imported CSV File, Dictionary data1_DickOh: key ; Item Compare File, wb2. Dictionary data2_DickOh: Key ; Item Redemption#IE00B64#25.05.2016#31.05.2016#Amount#50000 Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000 Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69 Redemption#IE00B64#25.05.2016#01.06.2016#Amount#50000 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#850000 Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#69 Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#1320 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3 Redemption#IE00B64#25.05.2016#01.06.2016#Unit#3 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#53574 Subscription#IE00B64#25.05.2016#31.05.2016#Amount#18937 Subscription#IE00B64#25.05.2016#31.05.2016#Unit#2000 Redemption#IE00B61#25.05.2016#01.06.2016#Unit#2908 Redemption#IE00B53#25.05.2016#01.06.2016#Unit#3092 Redemption#IE00B64#25.05.2016#01.06.2016#Amount#191 Subscription#IE00B53#25.05.2016#31.05.2016#Unit#18
Sheet: DebugSheet
And
Missing order in Lomax 26.05.2016 Redemption IE00B64 25.05.2016 31.05.2016 Amount
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.
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
Last edited by Doc.AElstein; 05-27-2016 at 06:55 AM.
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.
Last edited by shinpencil; 05-30-2016 at 04:42 AM.
Hi Shinpencil,
_1 ) I am sorry but this makes no sense what so ever.
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 Redemption IE00B64 25.05.2016 31.05.2016
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 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
Last edited by Doc.AElstein; 05-30-2016 at 05:59 AM.
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
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
Last edited by Doc.AElstein; 05-30-2016 at 06:53 AM.
_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 Redemption IE00B64 25.05.2016 31.05.2016
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 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
Last edited by Doc.AElstein; 05-30-2016 at 07:27 AM.
I use this macro with 2 wb in post #55. You can see the problem in the "Differences" sheet..
Last edited by shinpencil; 05-30-2016 at 08:34 AM.
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
And not that which I suggested in Post #61Please Login or Register to view this content.
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.
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 ????
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
Hi
Ok I see
Schnitzel ____True bien
Alan Wnks __ False mal
Please Login or Register to view this content.
I understand now, Thank youPlease Login or Register to view this content.
_..............................
I check now your latest problem.
Alan
Last edited by Doc.AElstein; 05-30-2016 at 10:24 AM.
Hi shinpencil,
This is your “latest” problem
Using Excel 2007 32 bit
Row\Col Missing order in Lomax 30/05/2016 Redemption IE00B64 25/05/2016 31/05/2016 Amount Missing order in Caceis 30/05/2016 Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated Order status: Estimated unwanted row.. such as the header row Redemption IE00B64 25/05/2016 Amount unwanted row...…………. an extra final row with just some small bit in it. It appears 2 values out of nowhere
Differences
I explained this potential problem already !! – See my post #58 from last week
.........
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 S/R type Order status Order status: Estimated Order status: Estimated Redemption Estimated Subscription Estimated Redemption Estimated Redemption Estimated Subscription Estimated Redemption Estimated Subscription Estimated Redemption Estimated Subscription Estimated Subscription Estimated Redemption Estimated Subscription Estimated
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 Missing order in Lomax 31.05.2016 Redemption IE00B64 25.05.2016 31.05.2016 Amount Missing order in Caceis 31.05.2016 Redemption IE00B64 25.05.2016 01.06.2016 Amount
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.
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.This code make good wonks better......Please Login or Register to view this content.
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
Thank you but it bring up the header line in the sheet. I tried to modify it like this:
But it still have value in the "order status" line (191,13)Please Login or Register to view this content.
Using Excel 2007
Row\Col 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
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 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.
Yes I just modified this line like that:
and I got the table like below. I would like to delete the value in order status linePlease Login or Register to view this content.
Last edited by shinpencil; 05-31-2016 at 08:08 AM.
I solved the problem! Here is my code:
Please Login or Register to view this content.
Hi Schnipcel,
I see you have another solution, good.
Je vois que vous avez une autre solution, bon.
_.......................................................
OK, then the problem is obviousYes I just modified this line like that:
and I got the table like below. I would like to delete the value in order status linePlease Login or Register to view this content.
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:
_.........................................
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 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
Hi,
Yes I understand the problem as you pointed out. Thank you I am able to learn a lot here!
Hi
Your Welcome
Thanks for the Feedback
Alan
De rien
Merci pour les commentaires
Alan
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?
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.
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...
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 ?
Yes thank you anyway. It should look like that in the "Differences" worksheet after removing the duplicate values:
Using Excel 2007
Row\Col 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
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:
You make no attempt to give “nature” a value.Please Login or Register to view this content.
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
must be replaced with this ?????Please Login or Register to view this content.
doit être remplacé par ce ?????
Please Login or Register to view this content.
Alan
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 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
Yes I still need your help, please let me know when you have time, thank you!
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
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!
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
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...
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
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.
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
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)
Hi Elie
Thanks
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
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?
Last edited by shinpencil; 07-04-2016 at 09:53 AM.
It will look like that in the "Differences" sheet after adding "Fund share name":
Using Excel 2007
Row\Col 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
Hi Elie
_1)
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
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.
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....
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.
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.
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
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 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
Last edited by shinpencil; 07-05-2016 at 10:00 AM.
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.
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 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
Last edited by shinpencil; 07-06-2016 at 08:51 AM.
Thanks,
I will take a look tomorrow and let you know
Alan
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 L L E LU10797034 E LU09854243
Sheet1
_................................................................
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 MULTI MULTI E E E LU08544236 E E E LU08544239 Fund Total: MULTI E E E LU08556925 E E E LU10406886
Sheet1
_..............................................
Post #84 ( first few rows )**
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 E C A LU107 E C A LU098 E C A LU140
Sheet1
_............................
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.
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.
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.
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
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 ???
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 And the Appropriately chosen ( based on the conditions ) Dictionary from Wb1, Ws1:
DBDikWb2NUSRR_090716
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 And the Appropriately chosen ( based on the conditions ) Dictionary from Wb1, Ws1:
DBDikWb2NUSRS_090716
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
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:
Answer was YES ..but clearly looking at the wanted results_.............
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 ?.........
_... 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 LU05330338 19.05.2016 20.05.2016
_
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
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.
Hi Elie,
Yes we Let Portugal win this once
_...............................................
Did you check out the Links I gave in Post #116 ?
For example..
I tried to explain the problem and a possible solution. Did you understand that?
Alan
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...
Hi Elie
This is extremely encouraging
Cela est extrêmement encourageante
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.
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!
Hi Elie
You are welcome
Good luck
Alan
There are currently 2 users browsing this thread. (0 members and 2 guests)
Posting Permissions
Register To Reply
Bookmarks