merge XLSX files for same sheet name based on specific files names

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
635
Office Version
  1. 2019
Hi Guys
I have files contains Table 1 sheet for each file and each files names contain BRIDGESTONE word( so just search for files contains BRIDGESTONE word) .
BRIDGESTONE-4.xlsx
ABCDEF
1µa฻Jl jaཬJlİ฽iaJl S1¿$9iJlaiaJl jli¿aiaJl a࣫µq µ
22,700.000JAPANBRIDGESTONEBS 1200R20 18PR G580 JAP1
32,680.000JAPANBRIDGESTONEBS 1200R20 18PR R187 JAP2
42,800.000JAPANBRIDGESTONEBS 13R22.5 18PR L328 JAP3
54,280.000JAPANBRIDGESTONEBS 1400R20VSJ TCF JAP4
6420.000THILANDBRIDGESTONEBS 175/65R14 EP1505
7180.000TURKEYBRIDGESTONEBS 175/70R13 EP150 82S TURK6
8280.000THILANDBRIDGESTONEBS 175/70R13 EP150 THI7
9215.000THILANDBRIDGESTONEBS 175/70R14 MY02 THI8
10260.000IndonesiaBRIDGESTONEBS 185/65R14 EP150 IND9
11315.000JAPANBRIDGESTONEBS 185/65R15 B250 JAP10
12290.000IndonesiaBRIDGESTONEBS 185/70R13 EP150 IND11
13360.000THILANDBRIDGESTONEBS 185/70R14 B250 THI12
14435.000TURKEYBRIDGESTONEBS 185R14C R660 102R100R 8 TURK13
15288.000THILANDBRIDGESTONEBS 195/60R15 EP150 THI14
16267.000JAPANBRIDGESTONEBS 195/60R15 T001 JAP15 16 17 18 19 20 21 22 23 24 25 26 27 28
17335.000JAPANBRIDGESTONEBS 195/65R15 EP150 JAP
18740.000JAPANBRIDGESTONEBS 195/70R15C R623 JAP
19342.000JAPANBRIDGESTONEBS 195R14C 613 JAP
20490.000THILANDBRIDGESTONEBS 195R14C R623 THI
21340.000TURKEYBRIDGESTONEBS 195R14C R624 HD 106P104P 8 TURK
22488.000TURKEYBRIDGESTONEBS 195R14C R660 TURK
23428.000JAPANBRIDGESTONEBS 195R15C 613V JAP
24500.000THILANDBRIDGESTONEBS 195R15C R623 THI
25490.000TURKEYBRIDGESTONEBS 195R15C R660 TURK
26409.000TURKEYBRIDGESTONEBS 205/65R15 EP150 TURK
27300.000THILANDBRIDGESTONEBS 205/65R15 T005 THI
28525.000THILANDBRIDGESTONEBS 205/70R15C R623 THI
29443.000TURKEYBRIDGESTONEBS 205/70R15C R660 TURK
30645.000THILANDBRIDGESTONEBS 205R16C D840 THI29
31525.000THILANDBRIDGESTONEBS 215/45R17 RE003 THI30
32430.000THILANDBRIDGESTONEBS 215/50R17 EP300 THI31
33400.000IndonesiaBRIDGESTONEBS 215/55R17 AR20 IND32
34395.000THILANDBRIDGESTONEBS 215/55R17 EP300 THI33
35435.000JAPANBRIDGESTONEBS 215/55R17 T005 JAP34
36507.000JAPANBRIDGESTONEBS 215/60R16 ER30 JAP35
37522.000JAPANBRIDGESTONEBS 215/60R16T005A JAP36
38484.000JAPANBRIDGESTONEBS 215/65R16 D688 JAP37
39600.000THILANDBRIDGESTONEBS 215/65R16C R611 THI38
40560.000THILANDBRIDGESTONEBS 215/70R15C R623 THI39
41515.000TURKEYBRIDGESTONEBS 215/70R15C R624 HD109S107S 8 TURK40
42505.000TURKEYBRIDGESTONEBS 215/70R15C R660 TURK41
43843.000IndonesiaBRIDGESTONEBS 215/70R16 D697 IND42
44598.000JAPANBRIDGESTONEBS 225/45R18 T005A JAP43 44 45 46 47 48 49 50 51 52 53 54 55 56
451,340.000JAPANBRIDGESTONEBS 295/35R20 S001 JAP
461,225.000JAPANBRIDGESTONEBS 315/35R20 DHPA JAPRFT
472,600.000JAPANBRIDGESTONEBS 315/80R22.5 R248 JAP
482,545.000JAPANBRIDGESTONEBS 315/80R22.5-18PR G580 JAP
492,410.000THILANDBRIDGESTONEBS 315/80R22.5-18PR G582 THI
502,750.000JAPANBRIDGESTONEBS 385/65 R22.5 R164 JAP
513,150.000JAPANBRIDGESTONEBS 425/65R22.5 R164 JAP
523,710.000JAPANBRIDGESTONEBS 445/65R22.5 R164 JAP113
53757.000JAPANBRIDGESTONEBS 650R16 R230 JAP114
541,180.000JAPANBRIDGESTONEBS 750R16 VSJ JAP115
55938.000JAPANBRIDGESTONEBS 750R16 VSJ TCF JAP18116
561,405.000JAPANBRIDGESTONEBS 8.25R16 R180 TCF JAP117 118 119 120
57870.000JAPANBRIDGESTONEBS LT285/75R16 AT001 JAP
58348.000JAPANBRIDGESTONEFS 225/45R17 TZ700 JAP
59522.000IndonesiaBRIDGESTONEFS 31*10.50R15 DEST AT IND
Table 1



BRIDGESTONE-6.xlsx
ABCDEF
1µa฻Jl jaཬJlİ฽iaJl S1¿$9iJlaiaJl jli¿aiaJl a࣫µq µ
22,800.000JAPANBRIDGESTONEBS 1200R24 20PR G582 JAP1
32,640.000JAPANBRIDGESTONEBS 13R22.5 18PR R187 JAP2
44,060.000JAPANBRIDGESTONEBS 1400R20 TCF R180 JAP3
54,160.000JAPANBRIDGESTONEBS 1400R20VSJ TCF JAP4
6393.000THILANDBRIDGESTONEBS 175/65R14 EP1505
7168.000TURKEYBRIDGESTONEBS 175/70R13 EP150 82S TURK6
8261.000THILANDBRIDGESTONEBS 175/70R13 EP150 THI7
9200.000THILANDBRIDGESTONEBS 175/70R14 MY02 THI8
10242.000IndonesiaBRIDGESTONEBS 185/65R14 EP150 IND9
11295.000JAPANBRIDGESTONEBS 185/65R15 B250 JAP10
12270.000IndonesiaBRIDGESTONEBS 185/70R13 EP150 IND11
13335.000THILANDBRIDGESTONEBS 185/70R14 B250 THI12
14405.000TURKEYBRIDGESTONEBS 185R14C R660 102R100R 8 TURK13
15268.000THILANDBRIDGESTONEBS 195/60R15 EP150 THI14
16250.000JAPANBRIDGESTONEBS 195/60R15 T001 JAP15 16 17 18 19 20 21 22 23 24 25 26 27 28
17690.000JAPANBRIDGESTONEBS 195/70R15C R623 JAP
18325.000JAPANBRIDGESTONEBS 195R14C 613 JAP
19455.000THILANDBRIDGESTONEBS 195R14C R623 THI
20305.000TURKEYBRIDGESTONEBS 195R14C R624 HD 106P104P 8 TURK
21320.000TURKEYBRIDGESTONEBS 195R14C R660 TURK
22423.000JAPANBRIDGESTONEBS 195R15C 613V JAP
23455.000THILANDBRIDGESTONEBS 195R15C R623 THI
24445.000TURKEYBRIDGESTONEBS 195R15C R660 TURK
25380.000TURKEYBRIDGESTONEBS 205/65R15 EP150 TURK
26380.000THILANDBRIDGESTONEBS 205/65R15 T005 THI
27407.000JAPANBRIDGESTONEBS 205/65R16 T05A JAP
28490.000THILANDBRIDGESTONEBS 205/70R15C R623 THI
29410.000TURKEYBRIDGESTONEBS 205/70R15C R660 TURK
30595.000THILANDBRIDGESTONEBS 205R16C D840 THI29
31490.000THILANDBRIDGESTONEBS 215/45R17 RE003 THI30
32407.000THILANDBRIDGESTONEBS 215/50R17 EP300 THI31
33350.000IndonesiaBRIDGESTONEBS 215/55R17 AR20 IND32
34365.000THILANDBRIDGESTONEBS 215/55R17 EP300 THI33
35405.000JAPANBRIDGESTONEBS 215/55R17 T005 JAP34
36472.000JAPANBRIDGESTONEBS 215/60R16 ER30 JAP35
37485.000JAPANBRIDGESTONEBS 215/60R16 T005A JAP36
38450.000JAPANBRIDGESTONEBS 215/65R16 D688 JAP37
39565.000THILANDBRIDGESTONEBS 215/65R16C R611 THI38
40520.000THILANDBRIDGESTONEBS 215/70R15C R623 THI39
41475.000TURKEYBRIDGESTONEBS 215/70R15C R624 HD109S107S 8 TURK40
42470.000TURKEYBRIDGESTONEBS 215/70R15C R660 TURK41
43785.000IndonesiaBRIDGESTONEBS 215/70R16 D697 IND42
44905.000THILANDBRIDGESTONEBS 215/70R17.5 R265 JAP43 44 45 46 47 48 49 50 51 52 53 54 55 56
45555.000JAPANBRIDGESTONEBS 225/45R18 T005A JAP
46495.000THILANDBRIDGESTONEBS 225/50R17 EP300 THI
47495.000JAPANBRIDGESTONEBS 225/55R17 T005A JAP
48860.000JAPANBRIDGESTONEBS 225/55R18 AL001 JAP
49445.000IndonesiaBRIDGESTONEBS 225/60R16 EP300 IND
50572.000JAPANBRIDGESTONEBS 225/60R17 D-SPORT JAP
51610.000JAPANBRIDGESTONEBS 225/60R18 ALENZA001 JAP
52580.000JAPANBRIDGESTONEBS 225/70R15C R623 JAP
53495.000TURKEYBRIDGESTONEBS 225/70R15C R660 112S110S 8 TURK
54492.000JAPANBRIDGESTONEBS 225/70R16 D687 JAP
55460.000THILANDBRIDGESTONEBS 225/70R16 H005 THI
56835.000JAPANBRIDGESTONEBS 225/70R17 D697 8PR JAP
572,680.000JAPANBRIDGESTONEBS 385/65 R22.5 R164 JAP117
582,475.000THILANDBRIDGESTONEBS 385/65 R22.5 R164 THI118
592,930.000JAPANBRIDGESTONEBS 425/65R22.5 R164 JAP119
603,460.000JAPANBRIDGESTONEBS 445/65R22.5 R164 JAP120
61705.000JAPANBRIDGESTONEBS 650R16 R230 JAP121 122 123 124 125 126 127 128
621,080.000JAPANBRIDGESTONEBS 750R16 8PR VSJ JAP
63920.000JAPANBRIDGESTONEBS 750R16 R230 TCF JAP
64795.000JAPANBRIDGESTONEBS 750R16 VSJ TCF JAP2018
651,310.000JAPANBRIDGESTONEBS 8.25R16 R180 TCF JAP
66810.000JAPANBRIDGESTONEBS LT285/75R16 AT001 JAP
67325.000JAPANBRIDGESTONEFS 225/45R17 TZ700 JAP
68485.000IndonesiaBRIDGESTONEFS 31*10.50R15 DEST AT IND
Table 1



result will be
TRT.xlsm
ABCDEF
1ITEMBRANDMARKORIGINUNIT PRICEUNIT PRICE
21BS 1200R20 18PR G580 JAPBRIDGESTONEJAPAN2,700.000
32BS 1200R20 18PR R187 JAPBRIDGESTONEJAPAN2,680.000
43BS 1200R24 20PR G582 JAPBRIDGESTONEJAPAN0.0002,800.000
54BS 13R22.5 18PR L328 JAPBRIDGESTONEJAPAN2,800.0002,640.000
65BS 1400R20VSJ TCF JAPBRIDGESTONEJAPAN4,280.0004,160.000
76BS 1400R20 TCF R180 JAPBRIDGESTONEJAPAN0.0004,060.000
87BS 175/65R14 EP150BRIDGESTONETHILAND420.000393.000
98BS 175/70R13 EP150 82S TURKBRIDGESTONETURKEY180.000168.000
109BS 175/70R13 EP150 THIBRIDGESTONETHILAND280.000261.000
1110BS 175/70R14 MY02 THIBRIDGESTONETHILAND215.000200.000
1211BS 185/65R14 EP150 INDBRIDGESTONEIndonesia260.000242.000
1312BS 185/65R15 B250 JAPBRIDGESTONEJAPAN315.000295.000
1413BS 185/70R13 EP150 INDBRIDGESTONEIndonesia290.000270.000
1514BS 185/70R14 B250 THIBRIDGESTONETHILAND360.000335.000
1615BS 185R14C R660 102R100R 8 TURKBRIDGESTONETURKEY435.000405.000
1716BS 195/60R15 EP150 THIBRIDGESTONETHILAND288.000268.000
1817BS 195/60R15 T001 JAPBRIDGESTONEJAPAN267.000250.000
1918BS 195/65R15 EP150 JAPBRIDGESTONEJAPAN335.0000.000
2019BS 195/70R15C R623 JAPBRIDGESTONEJAPAN740.000690.000
2120BS 195R14C 613 JAPBRIDGESTONEJAPAN342.000325.000
2221BS 195R14C R623 THIBRIDGESTONETHILAND490.000455.000
2322BS 195R14C R624 HD 106P104P 8 TURKBRIDGESTONETURKEY340.000305.000
2423BS 195R14C R660 TURKBRIDGESTONETURKEY488.000320.000
2524BS 195R15C 613V JAPBRIDGESTONEJAPAN428.000423.000
2625BS 195R15C R623 THIBRIDGESTONETHILAND500.000455.000
2726BS 195R15C R660 TURKBRIDGESTONETURKEY490.000445.000
2827BS 205/65R15 EP150 TURKBRIDGESTONETURKEY409.000380.000
2928BS 205/65R15 T005 THIBRIDGESTONETHILAND300.000380.000
3029BS 205/65R16 T05A JAPBRIDGESTONEJAPAN0.000407.000
3130BS 205/70R15C R623 THIBRIDGESTONETHILAND525.000490.000
3231BS 205/70R15C R660 TURKBRIDGESTONETURKEY443.000410.000
3332BS 205R16C D840 THIBRIDGESTONETHILAND645.000595.000
3433BS 215/45R17 RE003 THIBRIDGESTONETHILAND525.000490.000
3534BS 215/50R17 EP300 THIBRIDGESTONETHILAND430.000407.000
3635BS 215/55R17 AR20 INDBRIDGESTONEIndonesia400.000350.000
3736BS 215/55R17 EP300 THIBRIDGESTONETHILAND395.000365.000
3837BS 215/55R17 T005 JAPBRIDGESTONEJAPAN435.000405.000
3938BS 215/60R16 ER30 JAPBRIDGESTONEJAPAN507.000472.000
4039BS 215/60R16T005A JAPBRIDGESTONEJAPAN522.000485.000
4140BS 215/65R16 D688 JAPBRIDGESTONEJAPAN484.000450.000
4241BS 215/65R16C R611 THIBRIDGESTONETHILAND600.000565.000
4342BS 215/70R15C R623 THIBRIDGESTONETHILAND560.000520.000
4443BS 215/70R15C R624 HD109S107S 8 TURKBRIDGESTONETURKEY515.000475.000
4544BS 215/70R15C R660 TURKBRIDGESTONETURKEY505.000470.000
4645BS 215/70R16 D697 INDBRIDGESTONEIndonesia843.000785.000
4746BS 215/70R17.5 R265 JAPBRIDGESTONETHILAND905.000905.000
4847BS 225/45R18 T005A JAPBRIDGESTONEJAPAN598.000555.000
4948BS 225/50R17 EP300 THIBRIDGESTONETHILAND495.000495.000
5049BS 225/55R17 T005A JAPBRIDGESTONEJAPAN495.000495.000
5150BS 225/55R18 AL001 JAPBRIDGESTONEJAPAN860.000860.000
5251BS 225/60R16 EP300 INDBRIDGESTONEIndonesia445.000445.000
5352BS 225/60R17 D-SPORT JAPBRIDGESTONEJAPAN572.000572.000
5453BS 225/60R18 ALENZA001 JAPBRIDGESTONEJAPAN610.000610.000
5554BS 225/70R15C R623 JAPBRIDGESTONEJAPAN580.000580.000
5655BS 225/70R15C R660 112S110S 8 TURKBRIDGESTONETURKEY495.000495.000
5756BS 225/70R16 D687 JAPBRIDGESTONEJAPAN492.000492.000
5857BS 225/70R16 H005 THIBRIDGESTONETHILAND460.000460.000
5958BS 225/70R17 D697 8PR JAPBRIDGESTONEJAPAN835.000835.000
6059BS 295/35R20 S001 JAPBRIDGESTONEJAPAN1,340.0000.000
6160BS 315/35R20 DHPA JAPBRIDGESTONEJAPAN1,225.0000.000
6261BS 315/80R22.5 R248 JAPBRIDGESTONEJAPAN2,600.0000.000
6362BS 315/80R22.5-18PR G580 JAPBRIDGESTONEJAPAN2,545.0000.000
6463BS 315/80R22.5-18PR G582 THIBRIDGESTONETHILAND2,410.0000.000
6564BS 385/65 R22.5 R164 JAPBRIDGESTONEJAPAN2,750.0002,680.000
6665BS 385/65 R22.5 R164 THIBRIDGESTONETHILAND2,475.0002,475.000
6766BS 425/65R22.5 R164 JAPBRIDGESTONEJAPAN3,150.0002,930.000
6867BS 445/65R22.5 R164 JAPBRIDGESTONEJAPAN3,710.0003,460.000
6968BS 650R16 R230 JAPBRIDGESTONEJAPAN757.000705.000
7069BS 750R16 VSJ JAPBRIDGESTONEJAPAN1,180.0001,080.000
7170BS 750R16 R230 TCF JAPBRIDGESTONEJAPAN0.000920.000
7271BS 750R16 VSJ TCF JAPBRIDGESTONEJAPAN938.000795.000
7372BS 8.25R16 R180 TCF JAPBRIDGESTONEJAPAN1,405.0001,310.000
7473BS LT285/75R16 AT001 JAPBRIDGESTONEJAPAN870.000810.000
7574FS 225/45R17 TZ700 JAPBRIDGESTONEJAPAN348.000325.000
7675FS 31*10.50R15 DEST AT INDBRIDGESTONEIndonesia522.000485.000
Table 1

the result should be:
1-the column F should be in column A in TRT file
2-the column D should be in column B in TRT file
3-the column C should be in column C in TRT file
4-the column B should be in column D in TRT file
5- the column A should be in column E,F,..... so on
as POINT 5 will there are many prices for the same BRAND or there are new brands for each file , then will brings all of brands from all of file and insert new column for each file contains prices so should put price for each file in adjacent brand without forgetting insert new columns contains prices for each file alone
all of files will be in this device "C:\Users\abmg\Desktop\REP"
the TRT file will be open and the others files will be closed
I hope some body help
 
Sort by date Sort by votes

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You must log in or register to reply here.

Similar threads

A
  • Solved
rearranging columns by reversing in location and create headers
2 3
Replies
20
Views
776
Alex Blakenburg
A
A
  • Question Question
Highlight and delete duplicates items in the different spaces for multiple columns
Replies
1
Views
437
abdo meghari
A
A
  • Question Question
populate & update invoice information across sheets based on invoice number
Replies
0
Views
338
abdo meghari
A
A
  • Solved
rearranging data based on headers and get rid of merged cells
2
Replies
15
Views
524
kvsrinivasamurthy
K
A
  • Solved
Delete ranges based on header contains colon and matched with cell value
Replies
6
Views
148
abdo meghari
A

Forum statistics

Threads
1,224,527
Messages
6,179,331
Members
452,907
Latest member
Roland Deschain

Share this page

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
Back
Top