Search tables and extract explicit string/data by using VBA

L

Legacy 495945

Guest
Hello folks,

I am desperate to scan the tables via Vba and determine specific values.

I have a master table (first table) that is automatically generated in a sheet every day.

This table should be compared with the table on the left (here as an example below) and the data from the master table should be transferred to the table on the right (here also as an example below). The following columns are to be compared to transfer the data: Material No - Part number. Also wenn Material Nr in Part Number vorhanden ist, dann soll die Abfrage starten.

I need the number of hours of activity between the first "Set-up time" and last "Cleaning time" of a "Material No." from the machines. This should be entered later in the right-hand table in the "Duration" column.

The period of the requested data should be from 6am yesterday to 6am today. However, some data will go beyond this period, but it should only be calculated/queried for this specified period. That is, "Start time" of activity "Set-up time" of a "Material Nr" must start after 6am yesterday and if for example "End Time" of a "Material Nr" goes up to 8am, then it should be cut at 6am and the duration calculated, regardless of whether "Cleaning time" has been reached or not.

This should be calculated for all "Material Nr" of the machines and the remaining information from the other columns should then be inserted into the right table. The process should happen for all machines, i.e. several tables like the one on the right in the example.

In addition, lines with the material number "-1" that fall within the specified period should be inserted into the table (here as an example at the bottom) on a new sheet. A hyperlink should be inserted in the "Reason for Delay" column in the table on the right (here as an example above). This should also be done for all machines, i.e. several sheets.

Since this has to take place automatically every day, new data should be added to each table below as a new row.

I started a few attempts to program it but without success.

I would be super grateful for any help!

Regards
Jimmy



MachineMatchcode2Production Order NumberMaterial NrActivityStart DateStart TimeEnd DateEnd TimeMachine Time Booking (h)Quantity 1Quantity 2
12---------1Repair27.07.202220:13:0027.07.202220:22:000,2
12400BU2331F141689Manufacturing time27.07.202205:52:0027.07.202210:50:004,8114.592,0229.184,0
12400BU2331F141689Cleaning time27.07.202210:50:0027.07.202211:32:000,7
12400BU2332F141689Set-up time27.07.202211:32:0027.07.202212:18:000,8
12400BU2332F141689Running-in time27.07.202212:18:0027.07.202212:52:000,6
12400BU2332F141689Manufacturing time27.07.202212:52:0027.07.202218:15:005,4
12400BU2332F141689Cleaning time27.07.202218:15:0027.07.202218:49:000,6
12400BU2333F141689Set-up time27.07.202218:49:0027.07.202220:13:001,4
12400BU2333F141689Running-in time27.07.202220:22:0027.07.202220:37:000,3
12400BU2333F141689Manufacturing time27.07.202220:37:0028.07.202201:23:004,8
12400BU2333F141689Cleaning time28.07.202201:23:0028.07.202201:53:000,5
12400BU2334F141689Set-up time28.07.202201:53:0028.07.202202:16:000,4
12400BU2334F141689Running-in time28.07.202202:16:0028.07.202202:34:000,3
12400BU2334F141689Manufacturing time28.07.202202:34:0028.07.202208:15:005,7
12400BU2334F141689Cleaning time28.07.202208:15:0028.07.202209:05:000,8
12400BU2335F141689Set-up time28.07.202209:05:0028.07.202210:01:000,9
12400BU2335F141689Running-in time28.07.202210:01:0028.07.202210:17:000,3
12400BU2335F141689Manufacturing time28.07.202210:17:0028.07.202210:47:000,5
12400BU2335F141689Cleaning time28.07.202210:47:0028.07.202211:21:000,6
12400BU2402F139923Set-up time28.07.202211:21:0028.07.202212:19:001,0
12400BU2402F139923Running-in time28.07.202212:19:0028.07.202212:40:000,4
12400BU2402F139923Manufacturing time28.07.202212:40:0028.07.202214:00:001,3
13---------1Machine downtime: lack of staff27.07.202217:03:0027.07.202217:55:000,9
13---------1Waiting for setter27.07.202219:10:0027.07.202220:20:001,2
13---------1Machine downtime: no order28.07.202212:05:0028.07.202214:00:001,9
135/12,5BU2436F139307Set-up time28.07.202210:54:0028.07.202212:05:001,2
1325BU2382F122126Manufacturing time26.07.202217:35:0027.07.202208:38:002,635.049,070.098,0
1325BU2382F122126Cleaning time27.07.202208:38:0027.07.202209:26:000,8
1325BU2435F122094Set-up time27.07.202209:26:0027.07.202210:22:000,920.049,040.098,0
1325BU2435F122094Running-in time27.07.202210:22:0027.07.202210:41:000,3
1325BU2435F122094Manufacturing time27.07.202210:41:0027.07.202216:41:006,0
1325BU2435F122094Cleaning time27.07.202216:41:0027.07.202217:03:000,4
1325BU2497F140694Set-up time27.07.202217:55:0027.07.202219:10:001,35.009,015.027,0
1325BU2497F140694Set-up time27.07.202220:20:0028.07.202203:00:006,7
1325BU2497F140694Running-in time28.07.202203:00:0028.07.202203:12:000,2
1325BU2497F140694Manufacturing time28.07.202203:12:0028.07.202206:37:003,4
1325BU2497F140694Cleaning time28.07.202206:37:0028.07.202210:54:004,3


Machine 12Machine 12
Guide number#Part numberParts labelBl./Pckg.AmountQuantity 2Start orderEnde of orderDurationFS- SizeATP BulkATP PMMachineMatchcode2Production Order NumberMaterial NrStart DateStart TimeEnd DateEnd TimeDurationQuantity 1Quantity 2Reson for delay
BU229022010AF011297Espumisan Kautabl. 100KTbl. N3101.967,019.67025.07.22 17:5025.07.22 21:253,5860 (V136916 47x60x110)✔✔12IBU 400F1399232428928.07.202211:21:0029.07.202206:00:0018,7163.413,0328.826,0Hyperlink to Sheet of Machine 12
BU232722069BF141689MIG 20FTbl22.248,04.49626.07.22 05:4026.07.22 06:401,0026 (V125586 47x26x110)✔✔
BU232822095AF141689MIG 20FTbl223.700,047.40026.07.22 08:1026.07.22 15:056,9126 (V125586 47x26x110)✔✔
BU232922096AF141689MIG 20FTbl223.442,046.88426.07.22 16:3526.07.22 23:256,8326 (V125586 47x26x110)✔✔
BU233022098AF141689MIG 20FTbl222.448,044.89627.07.22 00:5527.07.22 07:276,5426 (V125586 47x26x110)✔✔
BU233122099AF141689MIG 20FTbl223.368,046.73627.07.22 08:5727.07.22 15:466,8126 (V125586 47x26x110)✔✔
BU233222100AF141689MIG 20FTbl222.927,045.85427.07.22 17:1627.07.22 23:576,6826 (V125586 47x26x110)✔✔
BU233322101AF141689MIG 20FTbl220.498,040.99628.07.22 01:2728.07.22 07:265,9826 (V125586 47x26x110)✔✔
BU233422102AF141689MIG 20FTbl223.516,047.03228.07.22 08:5628.07.22 15:476,8626 (V125586 47x26x110)✔✔
BU233522103AF141689MIG 20FTbl22.853,05.70628.07.22 17:1728.07.22 18:171,0026 (V125586 47x26x110)✔✔
BU240222103BF139923MIG 20 FTbl219.099,038.19828.07.22 19:4729.07.22 01:215,5726 (V139926 47x26x110)
BU240322105AF139923MIG 20 FTbl23.901,07.80229.07.22 02:5129.07.22 03:591,1426 (V139926 47x26x110)
BU240722105BF139922MIG 10FTbl138.862,038.86229.07.22 08:5929.07.22 16:197,3320 (V139924 47x20x110)
BU240822106AF139922MIG 10FTbl17.138,07.13829.07.22 17:4929.07.22 19:101,3520 (V139924 47x20x110)
BU240922106BF141584MIG-400 10FTbl130.000,030.00029.07.22 20:4030.07.22 02:205,6620 (V141582 47x20x110)
BU241222106CF143438Ibustar 10FTbl110.598,010.59830.07.22 03:5030.07.22 05:502,0020 (V097228 47x20x110)
BU241322107AF143438Ibustar 10FTbl19.402,09.40231.07.22 23:2001.08.22 01:061,7720 (V097228 47x20x110)


Machine 12
MachineMatchcode2Material NrActivityStart DateStart TimeEnd DateEnd TimeMachine Time Booking (h)
12---1Repair27.07.202220:13:0027.07.202220:22:000,2
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
269
shift-del
shift-del
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
57
Bernie Galve
B
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
286
excelnewbie999
E

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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