Complex extract data & compare from 2 sheets and create matching report on a 3 sheet

Hi JEAN1972 ,
. Please reduce your test data to about 30 rows on all sheets so that i can see easier what you want.
. If you do that I will try and do it for you tomorrow
Alan

Hi Jean

Originally Posted by JEAN1972

As per your request reduce amount of data .........

Thanks for the extra info.

. 1 )

Originally Posted by JEAN1972

..............Requires to match bar codes EAN on summary sheet column D with detailed database sheet column D EAN COLIS......................

. I think maybe you mean Column E EAN COLIS in sheet Detailed Database ??
..................................


. 2 )

. It is still not too clear to me exactly what you want..

. It would be much easier if you include 2 sheets for “Matching Report”
. – one should look exactly as it does before the running of any code I do for you.
. – the other should be filled in by you manually and should look exactly as you want it to based on the actual test data you supply in sheets “Summary” and “”Detailed Database”
..........................................

. Your requirement looks quite easy to satisfy. But I am having difficulty seeing excactly what you want.

Alan
p.s. ( It is also a bit difficult to understand your English , which makes it even more important for you to give a visual “Picture” of what you want, - preferable in the sheets I am asking you for ...

Remember: We say ( IN English ) ... “ A Picture paints a thousand words”...and a workbook is worth a thousand pictures!

Hi Jean,

Thanks for the updated info and File.

Originally Posted by Doc.AElstein

..... I think maybe you mean Column E EAN COLIS in sheet Detailed Database , not column D as you stated in post #1 ??

Originally Posted by JEAN1972

......
My concern is to find data from EAN column D in summary sheet if they match with column E (EAN COLIS) of detailed database.......

OK we have that bit cleared up now. Good.
.............................................

BUT:

.. In the latest File you have updated you have given 4 sheets , 2 similar to before, "Summary" and "Detailed Database"......
... But now I have two sheets “Expected Matching Report” and “No match report” ????

. I even more confused now as to what you require.

... Please give me 2 Files
. - One should represent the situation before the running of any code.
. – The second should be hand filled in by you in exactly the form you want it to be by any code i do for you. ( based on your sample data in "Summary" and "Detailed Database". )

Alan

... I think we are almost there...... But...

..... Just one last thing
. What is sheet "Explanation File for info" ??

Hi Jean

I think it should be fairly straight forward to take your " Before " File,
Matching_1(2) original _1
and working with that produce the " After " File
Matching_1(2)amend explanation_11
based on the Match criteria..

. I will try to do it for you later today or tomorrow.

Alan

P.s. 1)

... one last discrepency.....

you apper to have 10 matches in total...

Using Excel 2007

Row\Col	D
31	3612301853079
32	3612301710532

Summary

Using Excel 2007

Row\Col	E	F
10	3612301710532	3612301710495
11	3612301710532	3612301710501
12	3612301710532	3612301710518
13	3612301710532	3612301710525
14	3612301853079	3612301852980
15	3612301853079	3612301852997
16	3612301853062	3612301853000
17	3612301853079	3612301853017
18	3612301853079	3612301853024
19	3612301853062	3612301853031
20	3612301853079	3612301853048
21	3612301853079	3612301853055

Detailed Database

....

But you have only indicated 8 in your After file:


Using Excel 2007

Row\Col	D	E
1	MSI	Ean Colis
2	5885132	3612301710532
3	5887005	3612301710532
4	5887211	3612301710532
5	5887450	3612301710532
6	3870292	3612301853079
7	3870300	3612301853079
8	3870318	3612301853079
9	3870326	3612301853079
10

Matching Report

... can you explain this

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

P.s. 2)
. Is the row order of the outout data in sheet " Matching Report " Important ?

Alan

Hi Jean,

. I want to get this “off my desk” just now.. so I will post a working code I have for you.

. I expect you may need some further help in its implementation.

. when you feedback, we can take it from there.

. I assume for now
. 1 ) the results based on your sample data should have 10 rows as I suggested and not the 8 you suggested.
. 2) I assume the order of the rows in the output is not important.

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

. I summarise now for clarity in this Thread as to what is going on here... I will only give partials screenshots to give the general idea...

.. So:-

. The following two screen shots are extracts from your supplied sample data, simplified..

Using Excel 2007

Page	Line	rayon	ean	msi							Montant total
1	1	69	3612301904511	3147147							0.00
1	2	33	3612302040034	1112481							0.00
1	26	48	3612302015728	7488323							0.00
1	27	48	3612302022061	7679780							0.00
1	28	68	3612301879734	6995435							0.00
1	29	68	3612301878690	6995328							0.00
1	30	36	3612301853079	3870235							0.00
1	31	36	3612301710532	1216100							34.42

Summary

Using Excel 2007

Range	ctm	mag	MSI	Ean Colis	Ean VENTES
1	3216	AAAAA	0219006	3612302008454	3612301607498
7	3216	AAAAA	0219600	3612302008454	3612301607559
8	3216	AAAAA	0219618	3612302008454	3612301607566
9	3216	AAAAA	5885132	3612301710532	3612301710495
10	3216	AAAAA	5887005	3612301710532	3612301710501
11	3216	AAAAA	5887211	3612301710532	3612301710518
12	3216	AAAAA	5887450	3612301710532	3612301710525
13	3216	AAAAA	3870292	3612301853079	3612301852980
14	3216	AAAAA	3870300	3612301853079	3612301852997
15	3216	AAAAA	3870219	3612301853062	3612301853000
16	3216	AAAAA	3870318	3612301853079	3612301853017
17	3216	AAAAA	3870326	3612301853079	3612301853024
18	3216	AAAAA	3870227	3612301853062	3612301853031
19	3216	AAAAA	3870334	3612301853079	3612301853048
20	3216	AAAAA	3870342	3612301853079	3612301853055
21	3216	AAAAA	3870417	3612301853277	3612301853123

Detailed Database

... continued in next post..

... from last post....



... after running the code I give in the next post you get an additional column in sheet summary..

Using Excel 2007

Page	Line	rayon	ean	msi							Montant total	Remarks
1	1	69	3612301904511	3147147							0.00	no match
1	2	33	3612302040034	1112481							0.00	no match
1	26	48	3612302015728	7488323							0.00	no match
1	27	48	3612302022061	7679780							0.00	no match
1	28	68	3612301879734	6995435							0.00	no match
1	29	68	3612301878690	6995328							0.00	no match
1	30	36	3612301853079	3870235							0.00
1	31	36	3612301710532	1216100							34.42

Summary

And in addition a new sheet is created.. part of which looks like this:

Using Excel 2007

Row\Col	A	B	C	D	E	F	G
1	Range	ctm	mag	MSI	Ean Colis	Ean VENTES	Libellé_Réf
2	13	3216	AAAAA	3870292	3.6E+12	3.6E+12	LOT 2 BOXERS Promo 2 920 L
3	14	3216	AAAAA	3870300	3.6E+12	3.6E+12	LOT 2 BOXERS Promo 2 930 L
4	16	3216	AAAAA	3870318	3.6E+12	3.6E+12	LOT 2 BOXERS Promo 2 920 XL
5	17	3216	AAAAA	3870326	3.6E+12	3.6E+12	LOT 2 BOXERS Promo 2 930 XL
6	19	3216	AAAAA	3870334	3.6E+12	3.6E+12	LOT 2 BOXERS Promo 2 920 XXL
7	20	3216	AAAAA	3870342	3.6E+12	3.6E+12	LOT 2 BOXERS Promo 2 930 XXL
8	9	3216	AAAAA	5885132	3.6E+12	3.6E+12	XXBIG TEE BANDANA 360 38-40
9	10	3216	AAAAA	5887005	3.6E+12	3.6E+12	XXBIG TEE BANDANA 360 42-44
10	11	3216	AAAAA	5887211	3.6E+12	3.6E+12	XXBIG TEE BANDANA 360 46-48
11	12	3216	AAAAA	5887450	3.6E+12	3.6E+12	XXBIG TEE BANDANA 360 50-52
12
13

MatchingReport

... here is the code for the last couple of posts #12 and # 13

Please Login or Register  to view this content.

. The code in this form is more complicated than it needs to be. This is just to remind me of what is going on. Once you are happy with the running of it , it can easily be simplified..

Alan

Hi,

Originally Posted by JEAN1972

......
See attached for requested headers that should appear matching report.(some headers on detailed database is not required ......

.. so you appear to have changed the headers that you want now compared with your original request..?..

. I can change the code to handle that..

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

Originally Posted by JEAN1972

.......
However just made a dummy test on huge database .

got an run time error 13 type mismtatch

If arrSean(rS, 1) = arrDDean(rDD, 1) Then 'Matched condition at Summary "row" rS... So

Perphaps some tuning in the code.
.......

You will need to give me more info for me to help you on that one.., possibly upload the file that is giving you problems.

.. But I think you should concentrate first on getting the code to work and understand it a little at least, with the reduced sample data initially.
.
. I will modify the code to allow for your new headings request and post that later

Alan

Originally Posted by JEAN1972

......
However,if this causes to much adjustment in the code for adjustment. proceed as normal with the headers.my priority is to make working and will delete column headers manually.
......

. I wrote the code in such a way that changing the required headings is very easy....

Please Login or Register  to view this content.

... if you examine the above code line – you should see that those numbers represent the column numbers of the Headings. So simply changing them allows you to easilly select whatever combination of Headers you like!!

( Note in posts #12 and # 13 I only showed part of the spreadsheet rows and columns because of limitations in the ExcelForum Posts width and length ... The actual Headers I used correspond to the Spreadsheet Column numbers
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 22 )
.. Looking back I may have missed off the last column, and those numbers should have been
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 22 29
.....................
If I understand your last request, you now are asking for columns
1 2 3 4 5 6 7 8 9 10 11 13 14 16 17 18 19 22 29
..
or it may be
1 2 3 4 5 6 7 8 9 10 11 13 15 16 17 18 19 22 29

.. but as noted that is easy to adjust at any time.
. In fact it may be wise to test the code with just a few times to help keep it simple when debugging


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

Originally Posted by JEAN1972

........I don’t think it will load on the forum due to mb limitations.....

. You could try a free file sharing site as an alternative such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give.

or

, P.M. me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Alan

Originally Posted by JEAN1972

I tested it at home as follows,my findings are as follows I omit to remove the matching report sheet but when I renamed it and test the code it worked with no flaw......

.. can you upload the shortened file that you have working, with the code version you have working in it, so that I can be sure of what columns etc. that you are wanting / using
Alan

Originally Posted by JEAN1972

vba code .

Now I tested with no #N/A in the database of 1425 rows and 500 rows of data in summary sheet.

No flaw detected. It was tedious task but achievable ................

. 1 ) For the next time that you may wish to do that: that sort of thing can be done very easily with a short piece od VBA code..

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

Originally Posted by JEAN1972

.....
See attached Matching report I need, I numbered the headers in colours for ease so those who are no coloured I do not need these columns......

. 2 ) You have given me a very clear well laid out file which shows exactly what you want. Well done. That is the sort of info that makes it very easy for us to help you. !!

BUT:-

... Did you read my Post # 18 of this Thread !!!
.. ... I already explained to you how to do that!!!!!

Originally Posted by Doc.AElstein

. I wrote the code in such a way that changing the required headings is very easy...

Please Login or Register  to view this content.

... if you examine the above code line – you should see that those numbers represent the column numbers of the Headings. So simply changing them allows you to easilly select whatever combination of Headers you like!!.........

.

So for your new requirement as shown in that uploaded File you simply need to substitute this code line:

Please Login or Register  to view this content.

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

. 3 ) I note you are now going up to 33 columns instead of the 29 in your original request.

you may then also need to adjust this line

Dim arrDD() As Variant: Let arrDD() = wsDD.Range("A1:AB" & lDDr & "").Value2 'Complete Detailed database

to something like

Dim arrDD() As Variant: Let arrDD() = wsDD.Range("A1:AG" & lDDr & "").Value2 'Complete Detailed database
( The code could also be modified to allow you to change the number of columns in the Future without having to make this sort of modification.. )

.. If you need help with that, or in general further help, I suggest you give me some similar short sample files to those you gave me in Post # 8. with the new extra columns I can then modify appropriately to ensure that the codes work with these new requirements..

Alan

Hi Jean,
. 1 )

Originally Posted by JEAN1972

.....
Thanks a lot for the time taken and patience to solve this issue. ....

. You are welcome. Nice to see us getting there.
. And you are getting better all the time at explaining and , importantly, "showing" what you want
...................................................................

. 2 ) When you reply to a Post at ExcelForum with a quote, please edit some of it out and only include the relevant parts, as i do. This is because it makes the Post and Thread much easier to follow.
..........................................................................

. 3 )

Originally Posted by JEAN1972

.....Yes I did not read the post completely ....

. Please always try to do read the post completely, even if you are busy. Remember we are all doing this voluntarily, and cannot always reply quickly, so please always use the time in between to read thoroughly what we do for you.

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

. 4 )

Originally Posted by JEAN1972

......
So is there a macro to delete these cells with #N/A (inclusive with those formulas), spilled over the database and to highlight the cell in red when macro has deleted these cells.......

. I expect it would be very simple for me to write such a code for you.
. As always it would be most helpful if you could upload 2 files or 1 file with 2 sheets.
. As always a
"Before"
...showing what you have (Reduced the amount of data to minimum required to demonstrate all scenarios of interest to you )
and a
After
... Hand filled in by you showing exactly what you want the before to look like after running of any code i do for you

Alan

Originally Posted by JEAN1972

........

I have attached the files one as raw data with #N/A in COLUMN D,F and AG in red font.

.......

OK.
. I will give that a go when I have time, and post back

Alan

Hi Jean,
. I have a couple of solutions for you,
. 1 ) a code to replace cells containing errors to empty cells with blue background color.
. 2 ) A version of the main code ( given in next Post ) that will work with errors in cells and not crash


. 1 ) This code may not be the best. Errors and empty cells are tricky things to play with in Excel. I do not have too much experience with these things. This may not be the most professional approach , but it appears to do what you want.. ( Possibly anyone else catching this post may have a better alternative code. I expect this requirement has come up before )

Code to knock out errors:

.. here is a small extract of your sample sheet before running the code

Using Excel 2007

Row\Col	A	B	C	D	E	F	G
1	ctm	mag	MSI	Ean Colis	Ean VENTES	Code Interne VLB	Libellé_Réf
2	3216	aaaa	0141903	3612302057391	3612301938011	#NV	XXDEB V LIN 720 0
3	3216	aaaa	0141929	3612302057391	3612301938028	#NV	XXDEB V LIN 720 1
4	3216	aaaa	0141937	3612302057391	3612301938035	#NV	XXDEB V LIN 720 2
5	3216	aaaa	6302723	3612301973609	3612301973586	#NV	TONG PAILLETTE N00 40
6	3216	aaaa	6539720	3612302080832	3612302080801	#NV	TS MC COL BRODE 720 4
7	3216	aaaa	7775752	#NV	3612302080856	#NV	ET COT IMP ZEBRES 840 TU
8	3216	aaaa	5951553	#NV	3612302081105	#NV	ET TS IMP FLEURS 200 TU
9	3216	aaaa	3765666	3612302085509	3612302085455	#NV	BORSA PAP GALON 810 55
10	3216	aaaa	0289694	3612302085950	3612302085851	#NV	JUPE LONG MAILL 570 0

Detailed database

......

Here is the same screenshot after running the code FkOffErrors()

Using Excel 2007

Row\Col	A	B	C	D	E	F	G
1	ctm	mag	MSI	Ean Colis	Ean VENTES	Code Interne VLB	Libellé_Réf
2	3216	aaaa	0141903	3612302057391	3612301938011		XXDEB V LIN 720 0
3	3216	aaaa	0141929	3612302057391	3612301938028		XXDEB V LIN 720 1
4	3216	aaaa	0141937	3612302057391	3612301938035		XXDEB V LIN 720 2
5	3216	aaaa	6302723	3612301973609	3612301973586		TONG PAILLETTE N00 40
6	3216	aaaa	6539720	3612302080832	3612302080801		TS MC COL BRODE 720 4
7	3216	aaaa	7775752		3612302080856		ET COT IMP ZEBRES 840 TU
8	3216	aaaa	5951553		3612302081105		ET TS IMP FLEURS 200 TU
9	3216	aaaa	3765666	3612302085509	3612302085455		BORSA PAP GALON 810 55
10	3216	aaaa	0289694	3612302085950	3612302085851		JUPE LONG MAILL 570 0
11	3216	aaaa	1077965	3612302104071	3612302104064		2P TRIA IMP KIWI 320 44
12	3216	aaaa	5293089		3612302117811		CABAS N/S COT RAY 220 TU
13	3216	aaaa	0104588	3612302118856	3612302118801		XXROB MC JERSEY B00 0
14	3216	aaaa	0104596	3612302118856	3612302118818		XXROB MC JERSEY B00 1

Detailed database

Code :

Please Login or Register  to view this content.

Alan
P.s. Be carefull of the odd spelling you have with your sheet – and you have a space “ “ at the end :
“Detailed database “ – But you should always check / change that to suit

Here is a modified code version of the main code that will also work without crashing if there are any errors in cells..

Note: I have updated it also for your latest Column selection requirements. from Post # 21

Please Login or Register  to view this content.

Hi Jean.

Originally Posted by JEAN1972

.......
Again thank you Alan for this great results you achieved and thanks to this forum.
.....

..You are welcome. Thanks for the kind Words. And thanks for feeding back your results
.. I know most of my VBA from Participating in Excel Forums
Alan