Ladies, and Gents.
I had significant help on a VBA, however, I didn't take into account the aspect of merged cells, so I have gone back to the start and rectified this in the examples provided.
The aim of this is for the inspectors to operate of one sheet and the information is spread across multiple others. The inspectors will place a marker "y" in columns A and G of the INSPECTION sheet for the data to be transferred. This is explained further down.
I am needing this INSPECTION (please note the unmarked row in serial 1 as this is a possibility of the information) This information actually starts on row 14 in the workbook
Turned to this REPORT using VBA. This example as seen actually starts on row 4
The inspector will place a marker "y" in column A and give a serial number. They will then add any comments.
Any parts required will then have a marker "y" placed against them. If the part is not listed, they will insert a row and add it to the list
I am hoping that I might be able to get a VBA to do the following:
- Look in INSPECTION column A for the marker "y" ***ideally this marker is only need to be placed once against the serial number(please refer to first example)***
- Reference the serial number INSPECTION column B and list in REPORT column A (this serial only need to be listed once in the report, please refer to second example)
- Check for any comment in INSPECTION column E and list them in the REPORT column C
- Check for the reference in INSPECTION column J and list in REPORT column B
- NOW check for the marker "y" against ant parts in INSPECTION column G and list the following in REPORT
- P/N INSPECTION column H listed in REPORT column F
- Part Description in INSPECTION column C listed in REPORT column G
- Quantity Required in INSPECTION column F listed in REPORT column H
I had significant help on a VBA, however, I didn't take into account the aspect of merged cells, so I have gone back to the start and rectified this in the examples provided.
The aim of this is for the inspectors to operate of one sheet and the information is spread across multiple others. The inspectors will place a marker "y" in columns A and G of the INSPECTION sheet for the data to be transferred. This is explained further down.
I am needing this INSPECTION (please note the unmarked row in serial 1 as this is a possibility of the information) This information actually starts on row 14 in the workbook
| Inspection trial.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | REPAIR REQUIRED | Serial Number | DESCRIPTION | PACKAGE | COMMENTS | QTY Required | Parts to Order | P/N | INSTALLED | DAMAGED | ESTIMATED HOURS | ||||
| 2 | LEFT SIDE | ||||||||||||||
| 3 | # 1 Skirt | 1 | 166-2044 | FF | y | y | |||||||||
| 4 | y | 1 | Brace Lh #1 support | 1335 | Weld repair Lh #1 support to close & latch properly | 1 | 071-9048 | RU | y | y | 0.5 | ||||
| 5 | Washer Lh #1 support washer | 389-9683 | |||||||||||||
| 6 | 1 | Washer Lh #1 support shim | 3 | y | 146-8960 | ||||||||||
| 7 | y | 2 | Washer #1 latch | 1301 | Fit washer & pin to Lh #1 latch free up & stake screw thread | 1 | y | 379-9572 | FF | y | 0.25 | ||||
| 8 | 2 | Pin #1 latch | 1 | y | 753-3893 | ||||||||||
| 9 | y | 3 | Stay #1 stop | 1337 | Fit Lh #1 stop stay correct way up, Replace shouldered screw | 1 | 200-6049 | RU | y | y | 0.25 | ||||
| 10 | 3 | Screw shouldered #1 stop | 2 | y | 071-9075 | ||||||||||
| 11 | y | 4 | Rubber Lh #1 rubber rear | 1302 | Reposition apply glue to Lh #1 rear rubber | 1 | FF | y | 0.25 | ||||||
| 12 | # 2 | 1 | 166-2045 | ||||||||||||
| 13 | y | 5 | Bracket weldment Lh #2 bracket | 1360-61 | Weld repair Lh #2 lock pin bracket to open/close properly | 1 | y | 102-3466 | FF | y | y | 2 | |||
| 14 | y | 6 | Shim hinge upper | 1335-1336 | Adjust #1 & #2 to open close properly, Straighten re-align #2 rubber | 6 | y | 390-0053 | FF | y | 0.25 | ||||
| 15 | 6 | Spacer Shim Lh #2 lower hinge support | 1 | y | 102-3568 | ||||||||||
| 16 | # 3 | 1 | 071-9044 | ||||||||||||
| 17 | # 4 | 1 | 071-9043 | ||||||||||||
| 18 | Washer Lh #4 #5 lock block | 427 | Fit h/w to Lh # 4 locking pin block | 2 | 379-9572 | RU | y | 0.25 | |||||||
| 19 | Screw Lh #4 #5 lock block | 2 | 390-0548 | RU | y | ||||||||||
| 20 | y | 7 | Bracket Lh/Rh #4 - #6 | 13,401,360 | Weld Lh #4 #5 #6 lock pin brackets | Various | FF | y | y | 2 | |||||
| 21 | # 5 | 1 | 071-9045 | ||||||||||||
| 22 | y | 8 | # 6 | 1363 | Weld repair bent Lh #6 rubber mountings | 1 | 071-9046 | RU | y | y | 1 | ||||
| 23 | y | 9 | Rubber, Lh #6 rubber | 1318 | Replace damaged Lh #6 rubber, retainers & h/w | 1 | y | 208-7052 | RU | y | y | 0.5 | |||
| 24 | 9 | Strip, Lh #6 rubber retainer | 1 | 102-4528 | |||||||||||
| 25 | 9 | Strip, Lh #6 retainer | 1 | y | 102-4522 | ||||||||||
| 26 | 9 | Screw, Lh #6 retainer | Adjust #6 to open close properly, Straighten re-align #2 rubber | 14 | y | 068-0508 | |||||||||
| 27 | 9 | Nut, Lh #6 retainer | 14 | y | 378-7761 | ||||||||||
| 28 | y | 10 | Hinge Butt # 6 piano hinge | 1344 | Weld fit missing piano hinge to position | 1 | y | 054-1578 | FF | y | y | 0.5 | |||
| 29 | # 7 ( Guard ) | 1 | 199-2407 | ||||||||||||
| 30 | Rear Splash Guard | 1 | 456-0508 | ||||||||||||
| 31 | y | 11 | Screw rear splash guard | 1329 | Fit correct/missing h/w tap threads for Lh rear screws | 3 | y | 390-0547 | FF | y | 0.25 | ||||
| 32 | 11 | Washer rear splash guard | 3 | y | 373-2547 | FF | y | ||||||||
Sheet3 | |||||||||||||||
Turned to this REPORT using VBA. This example as seen actually starts on row 4
| Inspection trial.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Serial | Class | Work Description | Package | HRS | P/N | Description | Qty Req | RPS SOH | ||
| 2 | 1 | RU | Weld repair Lh #1 support to close & latch properly | 1335 | 0.50 | 146-8960 | Washer Lh #1 support shim | 3 | |||
| 3 | 2 | FF | Fit washer & pin to Lh #1 latch free up & stake screw thread | 1301 | 0.25 | 379-9572 | Washer #1 latch | 1 | |||
| 4 | 753-3893 | Pin #1 latch | 1 | ||||||||
| 5 | 3 | RU | Fit Lh #1 stop stay correct way up, Replace shouldered screw | 1337 | 0.25 | 071-9075 | Screw shouldered #1 stop strap | 2 | |||
| 6 | 4 | FF | Reposition apply glue to Lh #1 rear rubber | 1302 | 0.25 | ||||||
| 7 | 5 | FF | Weld repair Lh #2 lock pin bracket to open/close properly | 1360-61 | 2.00 | 102-3466 | Bracket weldment Lh #2 lock bracket | 1 | |||
| 8 | 6 | FF | Adjust #1 & #2 open close properly, Straighten re-align #2 rubber | 1335-1336 | 0.25 | 390-0053 | Shim hinge upper | 6 | |||
| 9 | 102-3568 | Spacer Shim Lh #2 lower hinge support | 1 | ||||||||
| 10 | 7 | FF | Weld Lh #4 #5 #6 lock pin brackets | 13,401,360 | 2.00 | ||||||
| 11 | 8 | RU | Weld repair bent Lh #6 rubber mountings | 1363 | 1.00 | ||||||
| 12 | 9 | RU | Replace damaged Lh #6 rubber, retainers & h/w | 1318 | 0.50 | 208-7052 | Rubber, Lh #6 rubber | 1 | |||
| 13 | Adjust #6 to open close properly, Straighten re-align #2 rubber | 102-4522 | Strip, Lh #6 retainer | 1 | |||||||
| 14 | 068-0508 | Screw, Lh #6 retainer | 14 | ||||||||
| 15 | 378-7761 | Nut, Lh #6 retainer | 14 | ||||||||
| 16 | 10 | FF | Weld fit missing piano hinge to position | 1344 | 0.50 | 054-1578 | Hinge Butt # 6 piano hinge | 1 | |||
| 17 | 11 | FF | Fit correct/missing h/w tap threads for Lh rear screws | 1329 | 0.25 | 390-0547 | Screw rear splash guard | 3 | |||
| 18 | 373-2547 | Washer rear splash guard | 3 | ||||||||
Sheet4 | |||||||||||
The inspector will place a marker "y" in column A and give a serial number. They will then add any comments.
Any parts required will then have a marker "y" placed against them. If the part is not listed, they will insert a row and add it to the list
I am hoping that I might be able to get a VBA to do the following:
- Look in INSPECTION column A for the marker "y" ***ideally this marker is only need to be placed once against the serial number(please refer to first example)***
- Reference the serial number INSPECTION column B and list in REPORT column A (this serial only need to be listed once in the report, please refer to second example)
- Check for any comment in INSPECTION column E and list them in the REPORT column C
- Check for the reference in INSPECTION column J and list in REPORT column B
- NOW check for the marker "y" against ant parts in INSPECTION column G and list the following in REPORT
- P/N INSPECTION column H listed in REPORT column F
- Part Description in INSPECTION column C listed in REPORT column G
- Quantity Required in INSPECTION column F listed in REPORT column H
