Hello all once again
I am hoping that this is a case of formula adjustment in the aggregate component. I have had significant help with what is sampled below. I have supplied a sample of both what I currently have (current output) and what I would like it to look like (hoping for) as these are being used by those less familiar with EXCEL. I have included the formulas at the bottom of the current output hence the column width.
Issue 1: I am wanting to do away with the merged cells (this caused some in the initial setup). As can be seen in the samples, currently there are 5 fixed rows allocated to each for the parts. This can change as seen below.
Issue 2: Currently if there are more than 5 parts to be supplied, I need to insert rows and copy the formulas F:J down.
I am hoping that the overall repair is listed A:E and then the parts F:I. If there is 1 or no parts, then the next repair is listed (see hoping for sample rows 1 and 2). If there are multiple parts (regardless of how many), these are listed in F:I. Once there are no more parts to be listed, then the next repair is listed in A:E (see hoping for sample rows 13:17)
I have no knowledge on macro at all beyond the fact that they can be used. If this goes down the path of macro, the first row of referenced data is 5 as there column headers. I would also require assistance in implementing and how to operate it. I am listing this as a possibility as I am unsure of the limitations of formulas. There is no formulation on the data being referenced.
Current output:
Hoping for:
I hope that I have made it clear, I understand the difficulties of helping when you are not familiar with the work or exactly what is being required
Thanks you to any and all that help
I am hoping that this is a case of formula adjustment in the aggregate component. I have had significant help with what is sampled below. I have supplied a sample of both what I currently have (current output) and what I would like it to look like (hoping for) as these are being used by those less familiar with EXCEL. I have included the formulas at the bottom of the current output hence the column width.
Issue 1: I am wanting to do away with the merged cells (this caused some in the initial setup). As can be seen in the samples, currently there are 5 fixed rows allocated to each for the parts. This can change as seen below.
Issue 2: Currently if there are more than 5 parts to be supplied, I need to insert rows and copy the formulas F:J down.
I am hoping that the overall repair is listed A:E and then the parts F:I. If there is 1 or no parts, then the next repair is listed (see hoping for sample rows 1 and 2). If there are multiple parts (regardless of how many), these are listed in F:I. Once there are no more parts to be listed, then the next repair is listed in A:E (see hoping for sample rows 13:17)
I have no knowledge on macro at all beyond the fact that they can be used. If this goes down the path of macro, the first row of referenced data is 5 as there column headers. I would also require assistance in implementing and how to operate it. I am listing this as a possibility as I am unsure of the limitations of formulas. There is no formulation on the data being referenced.
Current output:
| Inspection data.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | 47 | FF/X/ | gearbox, seal and belt . Replace worn bearing | 0791, 1402, 1176 | 3 | 012103954 | Bearing | 1 | ||||
| 2 | ||||||||||||
| 3 | ||||||||||||
| 4 | ||||||||||||
| 5 | ||||||||||||
| 6 | 48 | FF/X/ | Repair arm rest. | 0 | 1 | |||||||
| 7 | ||||||||||||
| 8 | ||||||||||||
| 9 | ||||||||||||
| 10 | ||||||||||||
| 11 | 49 | RU/X/ | Replace bottom cushion, pad and lock assembly. Replace corroded seatsprings and lowerbolts | 1097, 1099, 1100, 1102, 1106, | 1.5 | 011862393 | Bottom seat cushion | 1 | ||||
| 12 | 012173789 | Bottom underside pad | 1 | |||||||||
| 13 | 013745430 | Bottom pad lockwasher | 4 | |||||||||
| 14 | 010841222 | Internal Height Spring | 2 | |||||||||
| 15 | 011871036 | Lock ***. - Pin | 1 | |||||||||
| 16 | 50 | RU/X/ | Replace bracket. Replace delaminating guard. | 1133, 1077, 1085, 1092 | 1.5 | 50 | ||||||
| 17 | 50 | |||||||||||
| 18 | 50 | |||||||||||
| 19 | 50 | |||||||||||
| 20 | 50 | |||||||||||
| 21 | 51 | FF/X/ | lubricate sticking bracket. | 703 | 0.25 | 51 | ||||||
| 22 | 51 | |||||||||||
| 23 | 51 | |||||||||||
| 24 | 51 | |||||||||||
| 25 | 51 | |||||||||||
| 26 | 52 | XX/X/ | Replace seat bottom cushion, mounting. | 1114, 1116, 1120 | 1 | 011441454 | Bottom cushion | 1 | 52 | |||
| 27 | 005501130 | Bottom seat lockwasher | 4 | 52 | ||||||||
| 28 | 005765752 | Lockwasher | 1 | 52 | ||||||||
| 29 | 52 | |||||||||||
| 30 | 52 | |||||||||||
| 31 | =IFERROR(INDEX(INSPECTION!$B$15:$B$1013,AGGREGATE(15,6,(ROW(INSPECTION!$A$14:$A$1013)-MIN(ROW(INSPECTION!$A$14:$A$1013))+1)/(INSPECTION!$A$14:$A$1013="Y"),ROWS(A$5:A5))),"") | =IFERROR(INDEX(INSPECTION!$I$14:$I$1013,AGGREGATE(15,6,(ROW(INSPECTION!$A$14:$A$1013)-MIN(ROW(INSPECTION!$A$14:$A$1013))+1)/(INSPECTION!$A$14:$A$1013="Y"),ROWS(B$5:B5))),"") | =IFERROR(INDEX(INSPECTION!$I$14:$I$1013,AGGREGATE(15,6,(ROW(INSPECTION!$A$14:$A$1013)-MIN(ROW(INSPECTION!$A$14:$A$1013))+1)/(INSPECTION!$A$14:$A$1013="Y"),ROWS(B$5:B5))),"") | =IFERROR(INDEX(INSPECTION!$H$14:$H$1013,AGGREGATE(15,6,(ROW(INSPECTION!$A$14:$A$1013)-MIN(ROW(INSPECTION!$A$14:$A$1013))+1)/(INSPECTION!$A$14:$A$1013="Y"),ROWS(D$5:D5))),"") | =IFERROR(INDEX(INSPECTION!$M$14:$M$1013,AGGREGATE(15,6,(ROW(INSPECTION!$A$14:$A$1013)-MIN(ROW(INSPECTION!$A$14:$A$1013))+1)/(INSPECTION!$A$14:$A$1013="Y"),ROWS(E$5:E5))),"") | =IFERROR(INDEX(INSPECTION!F$2:F$1133,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$1133)-MIN(ROW(INSPECTION!$A$2:$A$1133))+1)/(INSPECTION!$B$2:$B$1133=$K6)/(INSPECTION!$E$2:$E$1133="Y"),COUNTIFS($K$5:$K6,$K6))),"") | =IFERROR(INDEX(INSPECTION!C$2:C$1133,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$1133)-MIN(ROW(INSPECTION!$A$2:$A$1133))+1)/(INSPECTION!$B$2:$B$1133=$K6)/(INSPECTION!$E$2:$E$1133="Y"),COUNTIFS($K$5:$K6,$K6))),"") | =IFERROR(INDEX(INSPECTION!D$2:D$1133,AGGREGATE(15,6,(ROW(INSPECTION!$A$2:$A$1133)-MIN(ROW(INSPECTION!$A$2:$A$1133))+1)/(INSPECTION!$B$2:$B$1133=$K6)/(INSPECTION!$E$2:$E$1133="Y"),COUNTIFS($K$5:$K6,$K6))),"") | =IF(A16<>"",A16,J16) | |||
| 32 | ||||||||||||
| 33 | ||||||||||||
| 34 | ||||||||||||
| 35 | ||||||||||||
Sheet1 | ||||||||||||
Hoping for:
| Inspection data.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | 47 | FF/X/ | gearbox, seal and belt . Replace worn bearing | 0791, 1402, 1176 | 3 | 012103954 | Bearing | 1 | |||
| 2 | 48 | FF/X/ | Repair arm rest. | 0 | 1 | ||||||
| 3 | 49 | RU/X/ | Replace bottom cushion, pad and lock assembly. Replace corroded seatsprings and lowerbolts | 1097, 1099, 1100, 1102, 1106, | 1.5 | 011862393 | Bottom seat cushion | 1 | |||
| 4 | 012173789 | Bottom underside pad | 1 | ||||||||
| 5 | 013745430 | Bottom pad lockwasher | 4 | ||||||||
| 6 | 010841222 | Internal Height Spring | 2 | ||||||||
| 7 | 011871036 | Lock ***. - Pin | 1 | ||||||||
| 8 | 000589737 | Lock ***. - Pin | 1 | ||||||||
| 9 | 011975064 | Lock ***. - Spring | 1 | ||||||||
| 10 | 011871088 | Lock ***. - Spacer | 1 | ||||||||
| 11 | 010078299 | Seat Service PIN | 2 | ||||||||
| 12 | 000680510 | Screw to floor | 3 | ||||||||
| 13 | 013819948 | Washer to floor | 3 | ||||||||
| 14 | 51 | FF/X/ | lubricate sticking bracket. | 703 | 0.25 | ||||||
| 15 | 52 | XX/X/ | Replace seat bottom cushion, mounting. | 1114, 1116, 1120 | 1 | 011441454 | Bottom cushion | 1 | |||
| 16 | 005501130 | Bottom seat lockwasher | 4 | ||||||||
| 17 | 005765752 | Lockwasher | 1 | ||||||||
Sheet2 | |||||||||||
I hope that I have made it clear, I understand the difficulties of helping when you are not familiar with the work or exactly what is being required
Thanks you to any and all that help
