VBA to transfer information based on a 'y' marker

Status
Not open for further replies.
N

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
111
Office Version
  1. 2010
Platform
  1. Windows
Hello All,
The only knowledge I have on VBA/Macro is that it exist If anyone can help with the problem, I will also require help to implement it.
I have previously had help with formulas, however after much trial and error, my only conclusion is that a VBA must be used.
As indicated in the title, I need to transfer data to a specific output from an inspection form. The actual form starts on row 13
INSPECTION.xlsx
ABCDEFGHIJKLM
1 REPAIR REQUIREDRepair NumberDescriptionQTY RequiredORDER PARTPart NumberREFERENCE INSTALLEDDAMAGEDCOMMENTSESTIMATED HOURS
2yEXTERIORabcdRepair bent L/H top rail,rails, LH cable and R/H holder.8
31L/S Rail1y000000001y
41L/S Hand Rail1y000000002y
51L/S Hand Rail1y000000003y
61L/S Holder1000000004y
71L/S Cable5y000000005y
81R/S Rail1y000000006y
91R/S Rail1000000007y
101R/S Rail1y000000008y
111R/S Holder1y000000009y
121R/S Cable5y000000010y
13y2R/S Box 1000000011defy
142L/S Box1y000000012y
152BOLTS1y000000013y
162WASHERS2000000014y
17y3Cables2000000015fgrtyPaint ends, clean and rust kill.2
184Rack Washers2000000016sadfgyReplace 2 rackwashers. Realign rack extension1.5
194Rack1000000017y
204Rack Extension1000000018y
21ROOF
22y5L/S Cover1y000000019Replace missingcovers0.5
235R/S Cover 1000000020
INSPECTION

As can be seen in columns A and E, I have placed a 'y' maker. This is used to import the data to other sheets which are formulated and what the marker is against will change from one inspection to the next. The issue I am having is with REPORT sheet which starts on row 5
INSPECTION.xlsx
ABCDEFGHIJK
510Repair bent L/H top rail,rails, LH cable and R/H holder.abcd8000000001L/S Rail11
6000000002L/S Hand Rail11
7000000003L/S Hand Rail11
8000000005L/S Cable51
9000000006R/S Rail11
10200def0000000012L/S Box12
11000000013BOLTS12
12   2
13   2
14   2
1530Paint ends, clean and rust kill.fgrt2   3
16   3
17   3
18   3
19   3
2050Replace missingcovers00.5000000019L/S Cover15
21   5
22   5
23   5
24   5
25        5
26   5
27   5
28   5
29   5
REPORT
Cell Formulas
RangeFormula
A5A5=IFERROR(INDEX(INSPECTION!$B$3:$B$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(A$5:A5))),"")
B5B5=IFERROR(INDEX(INSPECTION!$I$2:$I$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(B$5:B5))),"")
C5C5=IFERROR(INDEX(INSPECTION!$L$2:$L$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(C$5:C5))),"")
D5D5=IFERROR(INDEX(INSPECTION!$H$2:$H$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(D$5:D5))),"")
E5E5=IFERROR(INDEX(INSPECTION!$M$2:$M$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(E$5:E5))),"")
F5:F29F5=IFERROR(INDEX(INSPECTION!F$1:F$1096,AGGREGATE(15,6,(ROW(INSPECTION!$A$1:$A$1096)-MIN(ROW(INSPECTION!$A$1:$A$1096))+1)/(INSPECTION!$B$1:$B$1096=$K5)/(INSPECTION!$E$1:$E$1096="Y"),COUNTIFS($K$5:$K5,$K5))),"")
G5:H29G5=IFERROR(INDEX(INSPECTION!C$1:C$1096,AGGREGATE(15,6,(ROW(INSPECTION!$A$1:$A$1096)-MIN(ROW(INSPECTION!$A$1:$A$1096))+1)/(INSPECTION!$B$1:$B$1096=$K5)/(INSPECTION!$E$1:$E$1096="Y"),COUNTIFS($K$5:$K5,$K5))),"")
A10A10=IFERROR(INDEX(INSPECTION!$B$2:$B$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(A$5:A6))),"")
B10B10=IFERROR(INDEX(INSPECTION!$I$2:$I$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(B$5:B6))),"")
C10C10=IFERROR(INDEX(INSPECTION!$L$2:$L$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(C$5:C6))),"")
D10D10=IFERROR(INDEX(INSPECTION!$H$2:$H$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(D$5:D6))),"")
E10E10=IFERROR(INDEX(INSPECTION!$M$2:$M$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(E$5:E6))),"")
A15A15=IFERROR(INDEX(INSPECTION!$B$2:$B$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(A$5:A7))),"")
B15B15=IFERROR(INDEX(INSPECTION!$I$2:$I$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(B$5:B7))),"")
C15C15=IFERROR(INDEX(INSPECTION!$L$2:$L$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(C$5:C7))),"")
D15D15=IFERROR(INDEX(INSPECTION!$H$2:$H$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(D$5:D7))),"")
E15E15=IFERROR(INDEX(INSPECTION!$M$2:$M$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(E$5:E7))),"")
A20A20=IFERROR(INDEX(INSPECTION!$B$2:$B$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(A$5:A8))),"")
B20B20=IFERROR(INDEX(INSPECTION!$I$2:$I$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(B$5:B8))),"")
C20C20=IFERROR(INDEX(INSPECTION!$L$2:$L$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(C$5:C8))),"")
D20D20=IFERROR(INDEX(INSPECTION!$H$2:$H$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(D$5:D8))),"")
E20E20=IFERROR(INDEX(INSPECTION!$M$2:$M$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(E$5:E8))),"")
A25A25=IFERROR(INDEX(INSPECTION!$B$2:$B$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(A$5:A9))),"")
B25B25=IFERROR(INDEX(INSPECTION!$I$2:$I$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(B$5:B9))),"")
C25C25=IFERROR(INDEX(INSPECTION!$L$2:$L$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(C$5:C9))),"")
D25D25=IFERROR(INDEX(INSPECTION!$H$2:$H$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(D$5:D9))),"")
E25E25=IFERROR(INDEX(INSPECTION!$M$2:$M$976,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$976)-MIN(ROW(INSPECTION!$A$2:$A$976))+1)/(INSPECTION!$A$2:$A$976="Y"),ROWS(E$5:E9))),"")
K29,K5:K27K5=IF(A5<>"",A5,K4)
K28K28=IF(A28<>"",A28,K26)

This is the current formulated output which is where I have found a number of problems. I am hoping that someone could help to produce the following output format
INSPECTION.xlsx
ABCDEFGHI
3210Repair bent L/H top rail,rails, LH cable and R/H holder.abcd8000000001L/S Rail1
33000000002L/S Hand Rail1
34000000003L/S Hand Rail1
35000000005L/S Cable5
36000000006R/S Rail1
37000000008R/S Rail1
38000000009R/S Holder1
39000000010R/S Cable5
40200def0000000012L/S Box1
41000000013BOLTS1
4230Paint ends, clean and rust kill.fgrt2
4350Replace missingcovers00.5000000019L/S Cover1
REPORT

As I am unfamiliar with the requirements of VBA, the actual INSPECTION data goes down some 2000 rows. As the return in the REPORT is dependant on the 'y' marker, the REPORT sheet could range from a few rows to 2000.
As the INSPECTION sheet is what people work off, the current 2000 rows could potentially grow.

The layout is that of what I am using. If there is anything I have missed, please let me know

Thank to all that look and help
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
The following macro begins reading the data in row 13 of the "inspection" sheet. The results on the "report" sheet in cell A2 onwards.

VBA Code: 
Sub transfer_information()
  Dim b As Variant, x As Long, y As Long, lr As Long
  Dim i As Long, j As Long, k As Long, m As Long
  
  With Sheets("INSPECTION")
    lr = .Range("B" & Rows.Count).End(3).Row
    ReDim b(1 To lr, 1 To 8)
    For i = 13 To lr
      If .Range("A" & i).Value = "y" Then
        x = .Range("A" & i).Cells(1).Row
        y = x
        If .Range("A" & i).MergeCells Then y = .Range("A" & i).MergeArea.Rows.Count + x - 1
        If k = j Then k = k + 1
        j = k
        For m = x To y
          If .Range("B" & m).Value <> "" Then b(j, 1) = .Range("B" & m).Value
          b(j, 2) = 0
          If .Range("L" & m).Value <> "" Then b(j, 3) = .Range("L" & m).Value
          If .Range("H" & m).Value <> "" Then b(j, 4) = .Range("H" & m).Value
          If .Range("M" & m).Value <> "" Then b(j, 5) = .Range("M" & m).Value
          
          If .Range("E" & m).Value = "y" Then
            b(k, 6) = .Range("F" & m).Value
            b(k, 7) = .Range("C" & m).Value
            b(k, 8) = .Range("D" & m).Value
            k = k + 1
          End If
          
        Next m
        i = m - 1
      End If
    Next i
  End With
  
  Sheets("REPORT").Rows("2:" & Rows.Count).ClearContents
  Sheets("REPORT").Range("A2").Resize(k, 8).Value = b
End Sub
 
Upvote 0
OK, I have managed to work oput how to omplement and run this.
This only covers therepairs required from INSPECTION
However also does not input the parts in columns F:H in the REPORT.

As indicated in the last example supplied, I am needing the reapir (your VBA works for this part), then the parts listed in F:H.
If you refer to the first example, E is the marker column for the parts required with column C,D and F from INSPECTION needing to be listed in REPORT F:H. Once all the parts for the repair are listed, then list the next repair required A:E in REPORT

I hope I have explained itcorrectly. I am unsure if this can be done
Thanks
 
Upvote 0
Correction.

Sorry, this does work correctly. I have realised that the marker needs to be y and not Y this works wonderfully

Thank you for your help
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Hello All
I have an issue with the above VBA. This works as it states, but I need an adjustment in it. If the "y" marker is in one cell in column B, it transfers that row.
This is a sample of the issue I am having
FROM THIS - INSPECTION
Book1.xlsx
ABCDEFGHIJKLM
1y66Box1042Fit missing h/w, Clean treat rust & patch paint100-000-0001RUyy0.25
2Box1329Weld repair, adjust & patch paint100-000-0001FFyy0.25
366Washer4y00-000-0003
466Screw4y00-000-0004
566Nut4y00-000-0005
Sheet1

TO THIS - REPORT
Book1.xlsx
ABCDEFGH
166RUFit missing h/w, Clean treat rust & patch paint10420.25
Sheet2

AS can be seen, the has picked up the comment in E11, but left of the parts required "y" marker in column G. The VBA works if I merge the marker cells , however if I leave a blank in column B, it doesn't transfer as needed.
FROM THIS - INSPECTION
Book1.xlsx
ABCDEFGHIJKLM
11y66Box1042Fit missing h/w, Clean treat rust & patch paint100-000-0001RUyy0.25
12Box1329Weld repair, adjust & patch paint100-000-0001FFyy0.25
1366Washer4y00-000-0003
1466Screw4y00-000-0004
1566Nut4y00-000-0005
Sheet1

TO THIS - REPORT
Book1.xlsx
ABCDEFGH
566FFWeld repair, adjust & patch paintVEH M 606-208 WP 13290.2500-000-0003Washer4
600-000-0004Screw4
700-000-0005Nut4
Sheet2

AS can be seen, it has picked up comments in E12 as well as all the parts, but missed the comments in E11.
The issue is, I don't need row 12 to be transferred. I do however require row 11 due to the comments in E11 and row 13:15 information for the parts.
Thanks for any help
 
Upvote 0
Ladies, and Gents. I am still having issues with the above VBA so I am going back to the start.
DanteAmor was extremely helpful in supplying the above VBA, however, I didn't take into account the aspect of merged cells, so I have rectified this in the examples provided.
I would also like to note that if someone has an alternate solution to the same as the REPORT result, I am open to ideas.
The aim of this is for the inspectors to operate of one sheet and the information is spread across multiple others. This is the only one I am having issues with.
I am needing this INSPECTION (please note the unmarked row in serial 1)
Book1.xlsx
ABCDEFGHIJKLM
1 REPAIR REQUIREDSerialNumberDESCRIPTION PACKAGECOMMENTSQTY RequiredParts to OrderP/NINSTALLEDDAMAGEDESTIMATED HOURS
2LEFT SIDE
3# 1 Skirt1166-2044FFyy
4y1Brace Lh #1 support1335Weld repair Lh #1 support to close & latch properly1071-9048RUyy0.5
5Washer Lh #1 support washer389-9683
61Washer Lh #1 support shim3y146-8960
7y2Washer #1 latch1301Fit washer & pin to Lh #1 latch free up & stake screw thread1y379-9572FFy0.25
82Pin #1 latch1y753-3893
9y3Stay #1 stop1337Fit Lh #1 stop stay correct way up, Replace shouldered screw1200-6049RUyy0.25
103Screw shouldered #1 stop2y071-9075
11y4Rubber Lh #1 rubber rear1302Reposition apply glue to Lh #1 rear rubber1FFy0.25
12# 2 1166-2045
13y5Bracket weldment Lh #2 bracket1360-61Weld repair Lh #2 lock pin bracket to open/close properly1y102-3466FFyy2
14y6Shim hinge upper1335-1336Adjust #1 & #2 to open close properly, Straighten re-align #2 rubber6y390-0053FFy0.25
156Spacer Shim Lh #2 lower hinge support1y102-3568
16# 31071-9044
17# 41071-9043
18Washer Lh #4 #5 lock block427Fit h/w to Lh # 4 locking pin block2379-9572RUy0.25
19Screw Lh #4 #5 lock block2390-0548RUy
20y7Bracket Lh/Rh #4 - #613,401,360Weld Lh #4 #5 #6 lock pin bracketsVariousFFyy2
21# 5 1071-9045
22y8# 6 1363Weld repair bent Lh #6 rubber mountings1071-9046RUyy1
23y9Rubber, Lh #6 rubber1318Replace damaged Lh #6 rubber, retainers & h/w1y208-7052RUyy0.5
249Strip, Lh #6 rubber retainer1102-4528
259Strip, Lh #6 retainer1y102-4522
269Screw, Lh #6 retainerAdjust #6 to open close properly, Straighten re-align #2 rubber14y068-0508
279Nut, Lh #6 retainer14y378-7761
28y10Hinge Butt # 6 piano hinge1344Weld fit missing piano hinge to position1y054-1578FFyy0.5
29# 7 ( Guard )1199-2407
30Rear Splash Guard1456-0508
31y11Screw rear splash guard1329Fit correct/missing h/w tap threads for Lh rear mudguard screws3y390-0547FFy0.25
3211Washer rear splash guard3y373-2547FFy
Sheet3

Turned to this REPORT using VBA
Book1.xlsx
ABCDEFGHI
1SerialClassWork DescriptionPackageHRSP/NDescription Qty ReqRPS SOH
21RUWeld repair Lh #1 support to close & latch properly13350.50146-8960Washer Lh #1 support shim3
32FFFit washer & pin to Lh #1 latch free up & stake screw thread13010.25379-9572Washer #1 latch1
4753-3893Pin #1 latch1
53RUFit Lh #1 stop stay correct way up, Replace shouldered screw13370.25071-9075Screw shouldered #1 stop strap2
64FFReposition apply glue to Lh #1 rear rubber13020.25
75FFWeld repair Lh #2 lock pin bracket to open/close properly1360-612.00102-3466Bracket weldment Lh #2 lock bracket1
86FFAdjust #1 & #2 open close properly, Straighten re-align #2 rubber1335-13360.25390-0053Shim hinge upper6
9102-3568Spacer Shim Lh #2 lower hinge support1
107FFWeld Lh #4 #5 #6 lock pin brackets13,401,3602.00
118RUWeld repair bent Lh #6 rubber mountings13631.00
129RUReplace damaged Lh #6 rubber, retainers & h/w13180.50208-7052Rubber, Lh #6 rubber1
13Adjust #6 to open close properly, Straighten re-align #2 rubber102-4522Strip, Lh #6 retainer1
14068-0508Screw, Lh #6 retainer14
15378-7761Nut, Lh #6 retainer14
1610FFWeld fit missing piano hinge to position13440.50054-1578Hinge Butt # 6 piano hinge1
1711FFFit correct/missing h/w tap threads for Lh rear mudguard screws13290.25390-0547Screw rear splash guard3
18373-2547Washer rear splash guard3
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
 
Upvote 0
Status
Not open for further replies.

Similar threads

HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
290
Jeffrey Mahoney
Jeffrey Mahoney
gtd526
  • Question Question
Create List of Cell Values with Criteria
Replies
1
Views
397
gtd526
gtd526
J
  • Question Question
Formula to move information to calendar
Replies
0
Views
378
jc352
J
R
  • Solved
Alternative to using Aggregate
Replies
8
Views
335
shaowu459
shaowu459
J
  • Question Question
Formula to find date and return column number
Replies
2
Views
426
jc352
J

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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