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

A | B | C | D | E | F | G | H | I | J | K | L | M | |||

1 | REPAIR REQUIRED | Repair Number | Description | QTY Required | ORDER PART | Part Number | REFERENCE | INSTALLED | DAMAGED | COMMENTS | ESTIMATED HOURS | ||||

2 | y | EXTERIOR | abcd | Repair bent L/H top rail,rails, LH cable and R/H holder. | 8 | ||||||||||

3 | 1 | L/S Rail | 1 | y | 000000001 | y | |||||||||

4 | 1 | L/S Hand Rail | 1 | y | 000000002 | y | |||||||||

5 | 1 | L/S Hand Rail | 1 | y | 000000003 | y | |||||||||

6 | 1 | L/S Holder | 1 | 000000004 | y | ||||||||||

7 | 1 | L/S Cable | 5 | y | 000000005 | y | |||||||||

8 | 1 | R/S Rail | 1 | y | 000000006 | y | |||||||||

9 | 1 | R/S Rail | 1 | 000000007 | y | ||||||||||

10 | 1 | R/S Rail | 1 | y | 000000008 | y | |||||||||

11 | 1 | R/S Holder | 1 | y | 000000009 | y | |||||||||

12 | 1 | R/S Cable | 5 | y | 000000010 | y | |||||||||

13 | y | 2 | R/S Box | 1 | 000000011 | def | y | ||||||||

14 | 2 | L/S Box | 1 | y | 000000012 | y | |||||||||

15 | 2 | BOLTS | 1 | y | 000000013 | y | |||||||||

16 | 2 | WASHERS | 2 | 000000014 | y | ||||||||||

17 | y | 3 | Cables | 2 | 000000015 | fgrt | y | Paint ends, clean and rust kill. | 2 | ||||||

18 | 4 | Rack Washers | 2 | 000000016 | sadfg | y | Replace 2 rackwashers. Realign rack extension | 1.5 | |||||||

19 | 4 | Rack | 1 | 000000017 | y | ||||||||||

20 | 4 | Rack Extension | 1 | 000000018 | y | ||||||||||

21 | ROOF | ||||||||||||||

22 | y | 5 | L/S Cover | 1 | y | 000000019 | Replace missingcovers | 0.5 | |||||||

23 | 5 | R/S Cover | 1 | 000000020 | |||||||||||

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

A | B | C | D | E | F | G | H | I | J | K | |||

5 | 1 | 0 | Repair bent L/H top rail,rails, LH cable and R/H holder. | abcd | 8 | 000000001 | L/S Rail | 1 | 1 | ||||

6 | 000000002 | L/S Hand Rail | 1 | 1 | |||||||||

7 | 000000003 | L/S Hand Rail | 1 | 1 | |||||||||

8 | 000000005 | L/S Cable | 5 | 1 | |||||||||

9 | 000000006 | R/S Rail | 1 | 1 | |||||||||

10 | 2 | 0 | 0 | def | 0 | 000000012 | L/S Box | 1 | 2 | ||||

11 | 000000013 | BOLTS | 1 | 2 | |||||||||

12 | 2 | ||||||||||||

13 | 2 | ||||||||||||

14 | 2 | ||||||||||||

15 | 3 | 0 | Paint ends, clean and rust kill. | fgrt | 2 | 3 | |||||||

16 | 3 | ||||||||||||

17 | 3 | ||||||||||||

18 | 3 | ||||||||||||

19 | 3 | ||||||||||||

20 | 5 | 0 | Replace missingcovers | 0 | 0.5 | 000000019 | L/S Cover | 1 | 5 | ||||

21 | 5 | ||||||||||||

22 | 5 | ||||||||||||

23 | 5 | ||||||||||||

24 | 5 | ||||||||||||

25 | 5 | ||||||||||||

26 | 5 | ||||||||||||

27 | 5 | ||||||||||||

28 | 5 | ||||||||||||

29 | 5 | ||||||||||||

REPORT |

Cell Formulas | ||
---|---|---|

Range | Formula | |

A5 | A5 | =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))),"") |

B5 | B5 | =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))),"") |

C5 | C5 | =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))),"") |

D5 | D5 | =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))),"") |

E5 | E5 | =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:F29 | F5 | =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:H29 | G5 | =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))),"") |

A10 | A10 | =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))),"") |

B10 | B10 | =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))),"") |

C10 | C10 | =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))),"") |

D10 | D10 | =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))),"") |

E10 | E10 | =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))),"") |

A15 | A15 | =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))),"") |

B15 | B15 | =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))),"") |

C15 | C15 | =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))),"") |

D15 | D15 | =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))),"") |

E15 | E15 | =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))),"") |

A20 | A20 | =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))),"") |

B20 | B20 | =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))),"") |

C20 | C20 | =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))),"") |

D20 | D20 | =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))),"") |

E20 | E20 | =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))),"") |

A25 | A25 | =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))),"") |

B25 | B25 | =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))),"") |

C25 | C25 | =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))),"") |

D25 | D25 | =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))),"") |

E25 | E25 | =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:K27 | K5 | =IF(A5<>"",A5,K4) |

K28 | K28 | =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 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | |||

32 | 1 | 0 | Repair bent L/H top rail,rails, LH cable and R/H holder. | abcd | 8 | 000000001 | L/S Rail | 1 | |||

33 | 000000002 | L/S Hand Rail | 1 | ||||||||

34 | 000000003 | L/S Hand Rail | 1 | ||||||||

35 | 000000005 | L/S Cable | 5 | ||||||||

36 | 000000006 | R/S Rail | 1 | ||||||||

37 | 000000008 | R/S Rail | 1 | ||||||||

38 | 000000009 | R/S Holder | 1 | ||||||||

39 | 000000010 | R/S Cable | 5 | ||||||||

40 | 2 | 0 | 0 | def | 0 | 000000012 | L/S Box | 1 | |||

41 | 000000013 | BOLTS | 1 | ||||||||

42 | 3 | 0 | Paint ends, clean and rust kill. | fgrt | 2 | ||||||

43 | 5 | 0 | Replace missingcovers | 0 | 0.5 | 000000019 | L/S Cover | 1 | |||

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