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

RAM1972

Board Regular
Joined
Jun 29, 2014
Messages
217
Hi:confused::confused::mad::mad:


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

*ABCDEFG
1Page Line rayoneanmsiDésignationOrigine
21169361230190451131471473CINTRECROCHET+1GRT OXYDEBLC *FRANCE * * * * * * *
3123336123020400341112481XA1 BRASSIERE SPORT JAUNE FLU ROUMANIE * * * * * *
413333612301842769966812XY3 BRAS SS COUT MARINE * * * ROUMANIE * * * * * *
514333612301843056968321XY3 TOP SS COUTURE IVOIRE * * ROUMANIE * * * * * *
615333612301843049967208XY3 TOP SS COUTURE MARINE * * ROUMANIE * * * * * *
716333612301842936967158XY3 SH SS COUTURE IVOIRE * * *ROUMANIE * * * * * *
817333612301842929967117XY3 SH SS COUTURE MARINE * * *ROUMANIE * * * * * *
918333612301842813966887XY3 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

*ABCDEFGHIJKLMNOPQRST
1PositionctmmagMSIEan ColisEan VENTESLibellé_RéfModèleModèleModèleCouleurModèleCouleurRayon_typeUGUGFamille_Famille_Sous_FamilleSous_FamilleN° de facture
213216AAAAA0219006 * * * 361230200845436123016074983 BOXERS 9J * * * * *560 4-5AE31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *56031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
323216AAAAA0219014 * * * 361230200845436123016075043 BOXERS 9J * * * * *560 6-8AE31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *56031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
433216AAAAA0219147 * * * 361230200845436123016075113 BOXERS 9J * * * * *560 10-12E31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *56031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
543216AAAAA0219170 * * * 361230200845436123016075283 BOXERS 9J * * * * *560 14-16E31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *56031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
653216AAAAA0219238 * * * 361230200845436123016075353 BOXERS 9J * * * * *510 4-5AE31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *51031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
763216AAAAA0219311 * * * 361230200845436123016075423 BOXERS 9J * * * * *510 6-8AE31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *51031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
873216AAAAA0219600 * * * 361230200845436123016075593 BOXERS 9J * * * * *510 10-12E31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *51031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
983216AAAAA0219618 * * * 361230200845436123016075663 BOXERS 9J * * * * *510 14-16E31550000 * * * * 3 BOXERS 9JE31550000 * * * * 3 BOXERS 9J * * * * *51031 * *ENFANT * * * * * * * * * * * * * * * * * * * * * * * * * * *31 55 * * SOUS-VET.GARC.4/16AN * * * * * * * * * * * * * * * * * * * *796 * SOUS-VETEMENT GARCON * * * * * * * * * * * * * * * * * * * *796002 * * *BAS * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1111
1093216AAAAA5885132 * * * 36123017105323612301710495XXBIG TEE BANDANA * *360 38-40E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
11103216AAAAA5887005 * * * 36123017105323612301710501XXBIG TEE BANDANA * *360 42-44E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
12113216AAAAA5887211 * * * 36123017105323612301710518XXBIG TEE BANDANA * *360 46-48E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
13123216AAAAA5887450 * * * 36123017105323612301710525XXBIG TEE BANDANA * *360 50-52E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
14133216AAAAA3853298 * * * 36123017139463612301713892LOT 2 CALECON Promo2 N00 SE36204401 * * * * LOT 2 CALECON Promo2E36204401 * * * * LOT 2 CALECON Promo2 N0036 * *HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * * 36 20 * * SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *818 * SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *818002 * * *CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *1111
15143216AAAAA3853306 * * * 36123017139463612301713908LOT 2 CALECON Promo2 N00 ME36204401 * * * * LOT 2 CALECON Promo2E36204401 * * * * LOT 2 CALECON Promo2 N0036 * *HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * * 36 20 * * SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *818 * SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *818002 * * *CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *1111
16153216AAAAA3853314 * * * 36123017139463612301713915LOT 2 CALECON Promo2 N00 LE36204401 * * * * LOT 2 CALECON Promo2E36204401 * * * * LOT 2 CALECON Promo2 N0036 * *HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * * 36 20 * * SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *818 * SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *818002 * * *CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *1111
17163216AAAAA3853322 * * * 36123017139463612301713922LOT 2 CALECON Promo2 N00 XLE36204401 * * * * LOT 2 CALECON Promo2E36204401 * * * * LOT 2 CALECON Promo2 N0036 * *HOMME * * * * * * * * * * * * * * * * * * * * * * * * * * * 36 20 * * SOUS-VETEMENTS * * * * * * * * * * * * * * * * * * * * * * *818 * SOUS VETEMENTS BAS * * * * * * * * * * * * * * * * * * * * *818002 * * *CALECONS * * * * * * * * * * * * * * * * * * * * * * * * * *1111
18173216AAAAA3853330 * * * 36123017139463612301713939LOT 2 CALECON Promo2 N00 XXLE36204401 * * * * LOT 2 CALECON Promo2E36204401 * * * * LOT 2 CALECON Promo2 N0036 * *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

*ABCDEFGHIJKLMNOPQRST
1*Page Line rayoneanmsiDésignationOrigine************
2*116831210400316435232426VENTILATEUR TABLE 30 VENTILATECHINE * * * * * * * ************
3*123336123017105321216100XXBIG TEE BANDANA CORAIL * * *BENGLADESH * * * * *************
4********************
5********************
6PositionctmmagMSIEan ColisEan VENTESLibellé_RéfModèleModèleModèleCouleurModèleCouleurRayon_typeUGUGFamille_Famille_Sous_FamilleSous_FamilleN° de facture
7********************
8*ctmmagMSIEan ColisEan VENTESLibellé_RéfModèleModèleModèleCouleurModèleCouleurRayon_typeUGUGFamille_Famille_Sous_FamilleSous_FamilleN° de facture
9**AAAAAno 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********************
1193216AAAAA5885132 * * * 36123017105323612301710495XXBIG TEE BANDANA * *360 38-40E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
12103216AAAAA5887005 * * * 36123017105323612301710501XXBIG TEE BANDANA * *360 42-44E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
13113216AAAAA5887211 * * * 36123017105323612301710518XXBIG TEE BANDANA * *360 46-48E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *LINGERIE * * * * * * * * * * * * * * * * * * * * * * * * * *33 11 * * CHEMISES DE NUIT * * * * * * * * * * * * * * * * * * * * * *684 * CHEMISE DE NUIT * * * * * * * * * * * * * * * * * * * * * * 684001 * * *CLASSIQUE * * * * * * * * * * * * * * * * * * * * * * * * * 1111
14123216AAAAA5887450 * * * 36123017105323612301710525XXBIG TEE BANDANA * *360 50-52E33112300 * * * * XXBIG TEE BANDANAE33112300 * * * * XXBIG TEE BANDANA * *36033 * *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
349ean
3503121040031643
3513612301710532
3523612301713946
3533612301714042
3543612301714127
3553612301764665
3563612301764672
3573612301778709
3583612301778839
3593612301791883
3603612301791937
3613612301792019
3623612301792088
3633612301792118
3643612301792149
3653612301792217
3663612301792286
3673612301792439
3683612301792477
3693612301793122
3703612301793290
3713612301793368
3723612301793436
3733612301793443
3743612301793450
3753612301793467
3763612301793474
3773612301793481
3783612301827605
3793612301842769
3803612301842813
3813612301842929
3823612301842936
3833612301843049
3843612301843056
3853612301853062
3863612301853079
3873612301853277
3883612301853284
3893612301869339
3903612301869384
3913612301870472
3923612301870533
3933612301870564
3943612301871783
3953612301871899
3963612301872070
3973612301873855
3983612301874838
3993612301878140
4003612301878430
4013612301878690
4023612301879703
4033612301879734
4043612301881409
4053612301881416
4063612301902920
4073612301902944
4083612301902951
4093612301902982
4103612301904511
4113612301904566
4123612301904672
4133612301904702
4143612301904764
4153612301904832
4163612301904931
4173612301905129
4183612301906577
4193612301923079
4203612301940434
4213612301950600
4223612301950754
4233612301950891
4243612301950907
4253612301951065
4263612301951157
4273612301954912
4283612301954981
4293612301954998
4303612301967400
4313612301967981
4323612301969060
4333612301969718
4343612301972893
4353612301976242
4363612301982991
4373612301983110
4383612301983196
4393612301983202
4403612301983684
4413612301983691
4423612301983783
4433612301983820
4443612301984148
4453612301984155
4463612301984254
4473612301984261
4483612301985299
4493612301985305
4503612301985374
4513612301985381
4523612301985756
4533612301991450
4543612301992402
4553612301992419
4563612301992686
4573612301992693
4583612301992716
4593612301992723
4603612301992914
4613612301992921
4623612301993171
4633612301993188
4643612301996059
4653612302001523
4663612302001646
4673612302001653
4683612302001714
4693612302001783
4703612302002674
4713612302004531
4723612302004654
4733612302004678
4743612302004739
4753612302005576
4763612302006566
4773612302006573
4783612302006580
4793612302006887
4803612302006894
4813612302006900
4823612302006917
4833612302007310
4843612302008157
4853612302008454
4863612302008577
4873612302008638
4883612302008690
4893612302009703
4903612302009710
4913612302012222
4923612302012239
4933612302012321
4943612302012574
4953612302012581
4963612302012673
4973612302013038
4983612302013182
4993612302014448
5003612302014455
5013612302014752
5023612302014875
5033612302015148
5043612302015155
5053612302015162
5063612302015728
5073612302020654
5083612302020739
5093612302022061
5103612302022078
5113612302022344
5123612302022351
5133612302022528
5143612302023167
5153612302023877
5163612302027660
5173612302027677
5183612302027738
5193612302027851
5203612302027912
5213612302028001
5223612302028070
5233612302028506
5243612302028742
5253612302029039
5263612302030646
5273612302030738
5283612302032756
5293612302032763
5303612302033029
5313612302037027
5323612302037607
5333612302038123
5343612302040034
5353612302041079

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


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,147,482
Messages
5,741,406
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top