I have had a huge amount of help from people on this (my formulation knowledge is pretty basic) and I am hoping to once again someone may be able to help me out with formulations. 3 sheets are involved.

Sheet 1 is a master sheet people input a "Y" into 2 different columns (E (part) & A (heading)). The same item can be listed against various headings.

Sheet 2 is part of an order form currently in use. This references the "Y" and pulls information accordingly. If the same part is required multiple times, it will list it multiple times.

The result I am looking for is Sheet . A MATCH / COUNTIF version of sheet 2. If the "Y" is placed against a part that is required multiple times, it is listed once and the total is tallied up.

All formulas currently in use have been listed. I know that with the 3 sheets I can get the result, however, I am hoping to only have 2 sheets. I am not the end user of this and I would like to not complicate what people are using or seeing

All columns are the same as the actual. Rows and sheet names are different, but I can deal with that later.

Sheet1

Book1.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | |||

1 | REPAIR | Repair Number | NOMENCLATURE | QTY Required | ORDER | |||

2 | y | 1 | EXTERIOR | |||||

3 | Top | 1 | ||||||

4 | Middle | 1 | ||||||

5 | 1 | Bottom | 1 | y | 1079 | |||

6 | 1 | Holder | 1 | y | 1587 | |||

7 | Clip | 5 | ||||||

8 | Top | 1 | ||||||

9 | Middle | 1 | ||||||

10 | 1 | Bottom | 1 | y | 1079 | |||

11 | 1 | Holder | 1 | y | 1587 | |||

12 | Clip | 5 | ||||||

13 | y | 2 | boxes | |||||

14 | 2 | Box | 1 | y | 1208 | |||

15 | Box | 1 | 543 | |||||

16 | 2 | BOLTS | 4 | y | 1373 | |||

17 | 2 | WASHERS | 4 | y | 1374 | |||

18 | y | 3 | cables | |||||

19 | 3 | Cable | 1 | y | 202 | |||

20 | y | 4 | rack | |||||

21 | Washers | 2 | 1374 | |||||

22 | Rack | 1 | 1647 | |||||

23 | Extension | 1 | 1648 | |||||

24 | bracket | 1 | 241 | |||||

25 | 4 | 3/8' | 6 | y | 1376 | |||

26 | 4 | bottom | 3 | y | 1095 | |||

27 | pin | 2 | 1271 | |||||

28 | 4 | rope | 2 | y | 68 | |||

29 | Screw | 2 | 1380 | |||||

30 | Washer | 2 | 1347 | |||||

31 | y | 5 | brackets | |||||

32 | 1 | |||||||

33 | 1 | 1380 | ||||||

34 | BOLTS | 3 | 1134 | |||||

35 | 5 | WASHERS | 3 | y | 1392 | |||

36 | Harness | 1 | 1384 | |||||

37 | 5 | Harness | 1 | y | 1076 | |||

38 | Bracket | 1 | 1074 | |||||

39 | 5 | Bracket | 1 | y | 71 | |||

40 | TOP | 2 | 167 | |||||

41 | TOP | 2 | 71 | |||||

42 | BOTTOM | 1 | 1379 | |||||

43 | BOTTOM | 1 | 1042 | |||||

44 | 5 | Cover | 1 | y | 1043 | |||

45 | 5 | Cover | 1 | y | 1044 | |||

46 | y | 6 | rack | |||||

47 | Washers | 2 | 1374 | |||||

48 | Rack | 1 | 1647 | |||||

49 | Extension | 1 | 1648 | |||||

50 | bracket | 1 | 241 | |||||

51 | 6 | 3/8' | 6 | y | 1376 | |||

52 | 6 | bottom | 3 | y | 1095 | |||

53 | pin | 2 | 1271 | |||||

54 | 6 | rope | 2 | y | 68 | |||

55 | Screw | 2 | 1380 | |||||

56 | Washer | 2 | ||||||

57 | y | 7 | boxes | 1208 | ||||

58 | 7 | Box | 1 | y | 1208 | |||

59 | Box | 1 | 543 | |||||

60 | 7 | BOLTS | 4 | y | 1373 | |||

61 | WASHERS | 4 | 1374 | |||||

Sheet1 |

Sheet 2

Book1.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|

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

1 | 1079 | Middle | 1 | ||||||||

2 | 1587 | Bottom | 1 | ||||||||

3 | 1079 | Middle | 1 | ||||||||

4 | 1587 | Bottom | 1 | ||||||||

5 | 1208 | boxes | 1 | ||||||||

6 | 1373 | Box | 4 | ||||||||

7 | 1374 | BOLTS | 4 | ||||||||

8 | 202 | cables | 1 | ||||||||

9 | 1376 | bracket | 6 | ||||||||

10 | 1095 | 3/8' | 3 | ||||||||

11 | 68 | pin | 2 | ||||||||

12 | 1392 | BOLTS | 3 | ||||||||

13 | 1076 | Harness | 1 | ||||||||

14 | 71 | Bracket | 1 | ||||||||

15 | 1043 | BOTTOM | 1 | ||||||||

16 | 1044 | Cover | 1 | ||||||||

17 | 1376 | bracket | 6 | ||||||||

18 | 1095 | 3/8' | 3 | ||||||||

19 | 68 | pin | 2 | ||||||||

20 | 1208 | boxes | 1 | ||||||||

21 | 1373 | Box | 4 | ||||||||

Sheet2 |

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

Range | Formula | |

C1 | C1 | =IFERROR(INDEX(Sheet1!$F$2:$F$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(C1:C$1))),"") |

D1 | D1 | =IFERROR(INDEX(Sheet1!$C$1:$C$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(D1:D$1))),"") |

C2:C21 | C2 | =IFERROR(INDEX(Sheet1!$F$2:$F$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(C$1:C2))),"") |

D2:D21 | D2 | =IFERROR(INDEX(Sheet1!$C$1:$C$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(D$1:D2))),"") |

I1 | I1 | =IFERROR(INDEX(Sheet1!$D$2:$D$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(I1:I$1))),"") |

I2:I21 | I2 | =IFERROR(INDEX(Sheet1!$D$2:$D$1216,AGGREGATE(15,6,(ROW(Sheet1!$E$2:$E$1216)-MIN(ROW(Sheet1!$E$2:$E$1216))+1)/(Sheet1!$E$2:$E$1216="Y"),ROWS(I$1:I2))),"") |

Press CTRL+SHIFT+ENTER to enter array formulas. |

Sheet 3

Book1.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|

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

2 | 1079 | Middle | 2 | ||||||||

3 | 1587 | Bottom | 2 | ||||||||

4 | 1208 | boxes | 2 | ||||||||

5 | 1373 | Box | 8 | ||||||||

6 | 1374 | BOLTS | 4 | ||||||||

7 | 202 | cables | 1 | ||||||||

8 | 1376 | bracket | 12 | ||||||||

9 | 1095 | 3/8' | 6 | ||||||||

10 | 68 | pin | 4 | ||||||||

11 | 1392 | BOLTS | 3 | ||||||||

12 | 1076 | Harness | 1 | ||||||||

13 | 71 | Bracket | 1 | ||||||||

14 | 1043 | BOTTOM | 1 | ||||||||

15 | 1044 | Cover | 1 | ||||||||

Sheet3 |

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

Range | Formula | |

C2:C15 | C2 | =IFERROR(INDEX(Sheet2!$C$1:$C$980,MATCH(0,COUNTIF(Sheet3!$C$1:C1,Sheet2!$C$1:$C$980),0)),"") |

D2:D15 | D2 | =IFERROR(VLOOKUP(C2,Sheet2!C:C:Sheet2!F:F,2,FALSE)&"","") |

I2:I15 | I2 | =IF(SUMIF(Sheet2!C:C,C2,Sheet2!I:I)=0,"",SUMIF(Sheet2!C:C,C2,Sheet2!I:I)) |

Press CTRL+SHIFT+ENTER to enter array formulas. |

I hope this is clear enough to any and all who have both come to look and or to help

Thanks once again