extract data from different worksheets and summarises match data on another sheet format

[RAM1972]

Hi


Requires to match codes on summary sheet with a database on a match report sheet.

Actually I am doing this manually and would like to this in excel

Annexed sample

Summary

*	A	B	C	D	E	F	G
1	Page	Line	rayon	ean	msi	Désignation	Origine
2	1	1	69	3612301904511	3147147	3CINTRECROCHET+1GRT OXYDEBLC *	FRANCE * * * * * * *
3	1	2	33	3612302040034	1112481	XA1 BRASSIERE SPORT JAUNE FLU	ROUMANIE * * * * * *
4	1	3	33	3612301842769	966812	XY3 BRAS SS COUT MARINE * * *	ROUMANIE * * * * * *
5	1	4	33	3612301843056	968321	XY3 TOP SS COUTURE IVOIRE * *	ROUMANIE * * * * * *
6	1	5	33	3612301843049	967208	XY3 TOP SS COUTURE MARINE * *	ROUMANIE * * * * * *
7	1	6	33	3612301842936	967158	XY3 SH SS COUTURE IVOIRE * * *	ROUMANIE * * * * * *
8	1	7	33	3612301842929	967117	XY3 SH SS COUTURE MARINE * * *	ROUMANIE * * * * * *
9	1	8	33	3612301842813	966887	XY3 BRAS SS COUT IVOIRE * * *	ROUMANIE * * * * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:46px;"><col style="width:99px;"><col style="width:56px;"><col style="width:254px;"><col style="width:119px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Database for matching

Detailed Database

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Position

ctm

mag

MSI

Ean Colis

Ean VENTES

Libellé_Réf

Modèle

Modèle

ModèleCouleur

ModèleCouleur

Rayon_

type

UG

UG

Famille_

Famille_

Sous_Famille

Sous_Famille

N° de facture

2

1

3216

AAAAA

0219006 * * *

3612302008454

3612301607498

3 BOXERS 9J * * * * *560 4-5A

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *560

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

3

2

3216

AAAAA

0219014 * * *

3612302008454

3612301607504

3 BOXERS 9J * * * * *560 6-8A

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *560

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

4

3

3216

AAAAA

0219147 * * *

3612302008454

3612301607511

3 BOXERS 9J * * * * *560 10-12

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *560

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

5

4

3216

AAAAA

0219170 * * *

3612302008454

3612301607528

3 BOXERS 9J * * * * *560 14-16

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *560

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

6

5

3216

AAAAA

0219238 * * *

3612302008454

3612301607535

3 BOXERS 9J * * * * *510 4-5A

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *510

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

7

6

3216

AAAAA

0219311 * * *

3612302008454

3612301607542

3 BOXERS 9J * * * * *510 6-8A

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *510

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

8

7

3216

AAAAA

0219600 * * *

3612302008454

3612301607559

3 BOXERS 9J * * * * *510 10-12

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *510

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

9

8

3216

AAAAA

0219618 * * *

3612302008454

3612301607566

3 BOXERS 9J * * * * *510 14-16

E31550000 * * * *

3 BOXERS 9J

E31550000 * * * *

3 BOXERS 9J * * * * *510

31 * *

ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *

31 55 * *

SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *

796 *

SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *

796002 * * *

BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

10

9

3216

AAAAA

5885132 * * *

3612301710532

3612301710495

XXBIG TEE BANDANA * *360 38-40

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

11

10

3216

AAAAA

5887005 * * *

3612301710532

3612301710501

XXBIG TEE BANDANA * *360 42-44

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

12

11

3216

AAAAA

5887211 * * *

3612301710532

3612301710518

XXBIG TEE BANDANA * *360 46-48

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

13

12

3216

AAAAA

5887450 * * *

3612301710532

3612301710525

XXBIG TEE BANDANA * *360 50-52

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

14

13

3216

AAAAA

3853298 * * *

3612301713946

3612301713892

LOT 2 CALECON Promo2 N00 S

E36204401 * * * *

LOT 2 CALECON Promo2

E36204401 * * * *

LOT 2 CALECON Promo2 N00

36 * *

HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * *

36 20 * *

SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *

818 *

SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *

818002 * * *

CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

15

14

3216

AAAAA

3853306 * * *

3612301713946

3612301713908

LOT 2 CALECON Promo2 N00 M

E36204401 * * * *

LOT 2 CALECON Promo2

E36204401 * * * *

LOT 2 CALECON Promo2 N00

36 * *

HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * *

36 20 * *

SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *

818 *

SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *

818002 * * *

CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

16

15

3216

AAAAA

3853314 * * *

3612301713946

3612301713915

LOT 2 CALECON Promo2 N00 L

E36204401 * * * *

LOT 2 CALECON Promo2

E36204401 * * * *

LOT 2 CALECON Promo2 N00

36 * *

HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * *

36 20 * *

SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *

818 *

SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *

818002 * * *

CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

17

16

3216

AAAAA

3853322 * * *

3612301713946

3612301713922

LOT 2 CALECON Promo2 N00 XL

E36204401 * * * *

LOT 2 CALECON Promo2

E36204401 * * * *

LOT 2 CALECON Promo2 N00

36 * *

HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * *

36 20 * *

SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *

818 *

SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *

818002 * * *

CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

18

17

3216

AAAAA

3853330 * * *

3612301713946

3612301713939

LOT 2 CALECON Promo2 N00 XXL

E36204401 * * * *

LOT 2 CALECON Promo2

E36204401 * * * *

LOT 2 CALECON Promo2 N00

36 * *

HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * *

36 20 * *

SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *

818 *

SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *

818002 * * *

CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *

1111

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:35px;"><col style="width:85px;"><col style="width:84px;"><col style="width:99px;"><col style="width:99px;"><col style="width:240px;"><col style="width:109px;"><col style="width:174px;"><col style="width:109px;"><col style="width:189px;"><col style="width:52px;"><col style="width:281px;"><col style="width:59px;"><col style="width:365px;"><col style="width:60px;"><col style="width:308px;"><col style="width:89px;"><col style="width:329px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Report on another sheet


Matching Report

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

*

Page

Line

rayon

ean

msi

Désignation

Origine

*

*

*

*

*

*

*

*

*

*

*

*

2

*

1

1

68

3121040031643

5232426

VENTILATEUR TABLE 30 VENTILATE

CHINE * * * * * * *

*

*

*

*

*

*

*

*

*

*

*

*

3

*

1

2

33

3612301710532

1216100

XXBIG TEE BANDANA CORAIL * * *

BENGLADESH * * * * *

*

*

*

*

*

*

*

*

*

*

*

*

4

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

5

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

6

Position

ctm

mag

MSI

Ean Colis

Ean VENTES

Libellé_Réf

Modèle

Modèle

ModèleCouleur

ModèleCouleur

Rayon_

type

UG

UG

Famille_

Famille_

Sous_Famille

Sous_Famille

N° de facture

7

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

8

*

ctm

mag

MSI

Ean Colis

Ean VENTES

Libellé_Réf

Modèle

Modèle

ModèleCouleur

ModèleCouleur

Rayon_

type

UG

UG

Famille_

Famille_

Sous_Famille

Sous_Famille

N° de facture

9

*

*

AAAAA

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

no match

10

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

11

9

3216

AAAAA

5885132 * * *

3612301710532

3612301710495

XXBIG TEE BANDANA * *360 38-40

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

12

10

3216

AAAAA

5887005 * * *

3612301710532

3612301710501

XXBIG TEE BANDANA * *360 42-44

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

13

11

3216

AAAAA

5887211 * * *

3612301710532

3612301710518

XXBIG TEE BANDANA * *360 46-48

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

14

12

3216

AAAAA

5887450 * * *

3612301710532

3612301710525

XXBIG TEE BANDANA * *360 50-52

E33112300 * * * *

XXBIG TEE BANDANA

E33112300 * * * *

XXBIG TEE BANDANA * *360

33 * *

LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *

33 11 * *

CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684 *

CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * *

684001 * * *

CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * *

1111

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:58px;"><col style="width:52px;"><col style="width:84px;"><col style="width:99px;"><col style="width:99px;"><col style="width:254px;"><col style="width:119px;"><col style="width:64px;"><col style="width:84px;"><col style="width:180px;"><col style="width:99px;"><col style="width:128px;"><col style="width:217px;"><col style="width:64px;"><col style="width:141px;"><col style="width:308px;"><col style="width:183px;"><col style="width:145px;"><col style="width:99px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

For ease of explanation if i put ean code on report if there is no match

it shall indicate no match

in case ean code matches shall extract as sample on below

However I would prefer a range for EAN and extract the data

Summary

*	D
349	ean
350	3121040031643
351	3612301710532
352	3612301713946
353	3612301714042
354	3612301714127
355	3612301764665
356	3612301764672
357	3612301778709
358	3612301778839
359	3612301791883
360	3612301791937
361	3612301792019
362	3612301792088
363	3612301792118
364	3612301792149
365	3612301792217
366	3612301792286
367	3612301792439
368	3612301792477
369	3612301793122
370	3612301793290
371	3612301793368
372	3612301793436
373	3612301793443
374	3612301793450
375	3612301793467
376	3612301793474
377	3612301793481
378	3612301827605
379	3612301842769
380	3612301842813
381	3612301842929
382	3612301842936
383	3612301843049
384	3612301843056
385	3612301853062
386	3612301853079
387	3612301853277
388	3612301853284
389	3612301869339
390	3612301869384
391	3612301870472
392	3612301870533
393	3612301870564
394	3612301871783
395	3612301871899
396	3612301872070
397	3612301873855
398	3612301874838
399	3612301878140
400	3612301878430
401	3612301878690
402	3612301879703
403	3612301879734
404	3612301881409
405	3612301881416
406	3612301902920
407	3612301902944
408	3612301902951
409	3612301902982
410	3612301904511
411	3612301904566
412	3612301904672
413	3612301904702
414	3612301904764
415	3612301904832
416	3612301904931
417	3612301905129
418	3612301906577
419	3612301923079
420	3612301940434
421	3612301950600
422	3612301950754
423	3612301950891
424	3612301950907
425	3612301951065
426	3612301951157
427	3612301954912
428	3612301954981
429	3612301954998
430	3612301967400
431	3612301967981
432	3612301969060
433	3612301969718
434	3612301972893
435	3612301976242
436	3612301982991
437	3612301983110
438	3612301983196
439	3612301983202
440	3612301983684
441	3612301983691
442	3612301983783
443	3612301983820
444	3612301984148
445	3612301984155
446	3612301984254
447	3612301984261
448	3612301985299
449	3612301985305
450	3612301985374
451	3612301985381
452	3612301985756
453	3612301991450
454	3612301992402
455	3612301992419
456	3612301992686
457	3612301992693
458	3612301992716
459	3612301992723
460	3612301992914
461	3612301992921
462	3612301993171
463	3612301993188
464	3612301996059
465	3612302001523
466	3612302001646
467	3612302001653
468	3612302001714
469	3612302001783
470	3612302002674
471	3612302004531
472	3612302004654
473	3612302004678
474	3612302004739
475	3612302005576
476	3612302006566
477	3612302006573
478	3612302006580
479	3612302006887
480	3612302006894
481	3612302006900
482	3612302006917
483	3612302007310
484	3612302008157
485	3612302008454
486	3612302008577
487	3612302008638
488	3612302008690
489	3612302009703
490	3612302009710
491	3612302012222
492	3612302012239
493	3612302012321
494	3612302012574
495	3612302012581
496	3612302012673
497	3612302013038
498	3612302013182
499	3612302014448
500	3612302014455
501	3612302014752
502	3612302014875
503	3612302015148
504	3612302015155
505	3612302015162
506	3612302015728
507	3612302020654
508	3612302020739
509	3612302022061
510	3612302022078
511	3612302022344
512	3612302022351
513	3612302022528
514	3612302023167
515	3612302023877
516	3612302027660
517	3612302027677
518	3612302027738
519	3612302027851
520	3612302027912
521	3612302028001
522	3612302028070
523	3612302028506
524	3612302028742
525	3612302029039
526	3612302030646
527	3612302030738
528	3612302032756
529	3612302032763
530	3612302033029
531	3612302037027
532	3612302037607
533	3612302038123
534	3612302040034
535	3612302041079

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:99px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4