VBA to transfer information based on a 'y' marker

Status
Not open for further replies.

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
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
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
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
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

I'm glad to help you. Thanks for the feedback.
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
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
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,995
Office Version
  1. 365
Platform
  1. Windows
Closed at OP's request.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,118,974
Messages
5,575,305
Members
412,654
Latest member
dibison18
Top