A Sum If formula, but copy row and don't total based on one value in column ???

natheplas

Board Regular
Joined
Aug 28, 2016
Messages
97
Hi,

I need to extract rows from a master spreadsheet based on the department name which is located in column A.

For example:

[TABLE="width: 1818"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]STOCK_CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]QTY_IN_STOCK[/TD]
[TD]ORDER_NUMBER[/TD]
[TD]ORDER_DATE[/TD]
[TD]NAME[/TD]
[TD]QTY_ORDER[/TD]
[TD]QTY_DELIVERED[/TD]
[TD]Outstanding[/TD]
[TD]DESPATCH_DATE[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A1.5MS/R152[/TD]
[TD]152mm x 1.5m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]107949[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]Naples Components Limited[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A1.5MS/R152[/TD]
[TD]152mm x 1.5m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]107936[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]WS Westin Ltd (account 1)[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]A3M2PLY102[/TD]
[TD]102mm x 3m Flexible Aluminium Hose[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]107940[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]North West Aluminium[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R102[/TD]
[TD]102mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]107961[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Vapourflow Ltd Stores[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R127[/TD]
[TD]127mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]107876[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD]Kair Ventilation Ltd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Hoses[/TD]
[TD]A3MS/R127[/TD]
[TD]127mm x 3m Semi-Rigid Aluminium Hose[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]107961[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Vapourflow Ltd Stores[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]ALUM102X10[/TD]
[TD]102mm x 10m Flexible Aluminium[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]107813[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD]Worth Electrical Wholesalers Ltd[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]05/01/2018[/TD]
[/TR]
[TR]
[TD]Purchasing[/TD]
[TD]ALUM102X10[/TD]
[TD]102mm x 10m Flexible Aluminium[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]106813[/TD]
[TD="align: right"]29/11/2017[/TD]
[TD]Vortice Elettrosociali S.p.A £ A/C[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]08/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

Then I want a formula on another worksheet that copies each row that contains 'hoses' in the first column.

The table is live data attached to a MS query, so its refreshing each day.

I know I could use versions of copying etc, but I need the data to flow into the next sheet without manual intervention.

The department column is a manually entered column and I tried making it a table then filtering it by hoses, but when I refresh the table it looses its filters each time.

Please help! Really stuck with this one. I want something like the sum ifs formula, but I don't want any adding up of data etc.

Thank you!
 

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

Excel 2010
ABCDEFGHIJK
1DepartmentSTOCK_CODEDESCRIPTIONQTY_IN_STOCKORDER_NUMBERORDER_DATENAMEQTY_ORDERQTY_DELIVEREDOutstandingDESPATCH_DATE
2HosesA1.5MS/R152152mm x 1.5m Semi-Rigid Aluminium Hose321079494/1/2018Naples Components Limited6068/1/2018
3HosesA1.5MS/R152152mm x 1.5m Semi-Rigid Aluminium Hose321079364/1/2018WS Westin Ltd (account 1)2002010/1/2018
4PurchasingA3M2PLY102102mm x 3m Flexible Aluminium Hose3041079404/1/2018North West Aluminium300308/1/2018
5HosesA3MS/R102102mm x 3m Semi-Rigid Aluminium Hose141079615/1/2018Vapourflow Ltd Stores5058/1/2018
6HosesA3MS/R127127mm x 3m Semi-Rigid Aluminium Hose1151078763/1/2018Kair Ventilation Ltd5058/1/2018
7HosesA3MS/R127127mm x 3m Semi-Rigid Aluminium Hose1151079615/1/2018Vapourflow Ltd Stores5058/1/2018
8PurchasingALUM102X10102mm x 10m Flexible Aluminium10431078132/1/2018Worth Electrical Wholesalers Ltd2025/1/2018
9PurchasingALUM102X10102mm x 10m Flexible Aluminium104310681329/11/2017Vortice Elettrosociali S.p.A £ A/C9098/1/2018
Sheet6



Cell Formulas
RangeFormula
A2{=IF(ROWS(A$2:A2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A2))))}
A3{=IF(ROWS(A$2:A3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A3))))}
A4{=IF(ROWS(A$2:A4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A4))))}
A5{=IF(ROWS(A$2:A5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A5))))}
A6{=IF(ROWS(A$2:A6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A6))))}
A7{=IF(ROWS(A$2:A7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A7))))}
A8{=IF(ROWS(A$2:A8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A8))))}
A9{=IF(ROWS(A$2:A9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A9))))}
A10{=IF(ROWS(A$2:A10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A10))))}
A11{=IF(ROWS(A$2:A11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A11))))}
A12{=IF(ROWS(A$2:A12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A12))))}
A13{=IF(ROWS(A$2:A13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A13))))}
A14{=IF(ROWS(A$2:A14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A14))))}
A15{=IF(ROWS(A$2:A15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!A$2:A$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!A$2:A$9)-ROW(Sheet6!A$2)+1),ROWS(A$2:A15))))}
B2{=IF(ROWS(B$2:B2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B2))))}
B3{=IF(ROWS(B$2:B3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B3))))}
B4{=IF(ROWS(B$2:B4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B4))))}
B5{=IF(ROWS(B$2:B5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B5))))}
B6{=IF(ROWS(B$2:B6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B6))))}
B7{=IF(ROWS(B$2:B7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B7))))}
B8{=IF(ROWS(B$2:B8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B8))))}
B9{=IF(ROWS(B$2:B9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B9))))}
B10{=IF(ROWS(B$2:B10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B10))))}
B11{=IF(ROWS(B$2:B11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B11))))}
B12{=IF(ROWS(B$2:B12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B12))))}
B13{=IF(ROWS(B$2:B13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B13))))}
B14{=IF(ROWS(B$2:B14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B14))))}
B15{=IF(ROWS(B$2:B15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!B$2:B$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!B$2:B$9)-ROW(Sheet6!B$2)+1),ROWS(B$2:B15))))}
C2{=IF(ROWS(C$2:C2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C2))))}
C3{=IF(ROWS(C$2:C3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C3))))}
C4{=IF(ROWS(C$2:C4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C4))))}
C5{=IF(ROWS(C$2:C5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C5))))}
C6{=IF(ROWS(C$2:C6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C6))))}
C7{=IF(ROWS(C$2:C7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C7))))}
C8{=IF(ROWS(C$2:C8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C8))))}
C9{=IF(ROWS(C$2:C9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C9))))}
C10{=IF(ROWS(C$2:C10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C10))))}
C11{=IF(ROWS(C$2:C11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C11))))}
C12{=IF(ROWS(C$2:C12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C12))))}
C13{=IF(ROWS(C$2:C13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C13))))}
C14{=IF(ROWS(C$2:C14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C14))))}
C15{=IF(ROWS(C$2:C15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!C$2:C$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!C$2:C$9)-ROW(Sheet6!C$2)+1),ROWS(C$2:C15))))}
D2{=IF(ROWS(D$2:D2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D2))))}
D3{=IF(ROWS(D$2:D3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D3))))}
D4{=IF(ROWS(D$2:D4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D4))))}
D5{=IF(ROWS(D$2:D5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D5))))}
D6{=IF(ROWS(D$2:D6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D6))))}
D7{=IF(ROWS(D$2:D7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D7))))}
D8{=IF(ROWS(D$2:D8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D8))))}
D9{=IF(ROWS(D$2:D9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D9))))}
D10{=IF(ROWS(D$2:D10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D10))))}
D11{=IF(ROWS(D$2:D11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D11))))}
D12{=IF(ROWS(D$2:D12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D12))))}
D13{=IF(ROWS(D$2:D13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D13))))}
D14{=IF(ROWS(D$2:D14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D14))))}
D15{=IF(ROWS(D$2:D15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!D$2:D$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!D$2:D$9)-ROW(Sheet6!D$2)+1),ROWS(D$2:D15))))}
E2{=IF(ROWS(E$2:E2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E2))))}
E3{=IF(ROWS(E$2:E3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E3))))}
E4{=IF(ROWS(E$2:E4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E4))))}
E5{=IF(ROWS(E$2:E5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E5))))}
E6{=IF(ROWS(E$2:E6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E6))))}
E7{=IF(ROWS(E$2:E7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E7))))}
E8{=IF(ROWS(E$2:E8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E8))))}
E9{=IF(ROWS(E$2:E9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E9))))}
E10{=IF(ROWS(E$2:E10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E10))))}
E11{=IF(ROWS(E$2:E11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E11))))}
E12{=IF(ROWS(E$2:E12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E12))))}
E13{=IF(ROWS(E$2:E13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E13))))}
E14{=IF(ROWS(E$2:E14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E14))))}
E15{=IF(ROWS(E$2:E15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!E$2:E$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!E$2:E$9)-ROW(Sheet6!E$2)+1),ROWS(E$2:E15))))}
F2{=IF(ROWS(F$2:F2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F2))))}
F3{=IF(ROWS(F$2:F3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F3))))}
F4{=IF(ROWS(F$2:F4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F4))))}
F5{=IF(ROWS(F$2:F5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F5))))}
F6{=IF(ROWS(F$2:F6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F6))))}
F7{=IF(ROWS(F$2:F7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F7))))}
F8{=IF(ROWS(F$2:F8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F8))))}
F9{=IF(ROWS(F$2:F9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F9))))}
F10{=IF(ROWS(F$2:F10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F10))))}
F11{=IF(ROWS(F$2:F11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F11))))}
F12{=IF(ROWS(F$2:F12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F12))))}
F13{=IF(ROWS(F$2:F13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F13))))}
F14{=IF(ROWS(F$2:F14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F14))))}
F15{=IF(ROWS(F$2:F15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!F$2:F$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!F$2:F$9)-ROW(Sheet6!F$2)+1),ROWS(F$2:F15))))}
G2{=IF(ROWS(G$2:G2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G2))))}
G3{=IF(ROWS(G$2:G3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G3))))}
G4{=IF(ROWS(G$2:G4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G4))))}
G5{=IF(ROWS(G$2:G5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G5))))}
G6{=IF(ROWS(G$2:G6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G6))))}
G7{=IF(ROWS(G$2:G7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G7))))}
G8{=IF(ROWS(G$2:G8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G8))))}
G9{=IF(ROWS(G$2:G9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G9))))}
G10{=IF(ROWS(G$2:G10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G10))))}
G11{=IF(ROWS(G$2:G11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G11))))}
G12{=IF(ROWS(G$2:G12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G12))))}
G13{=IF(ROWS(G$2:G13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G13))))}
G14{=IF(ROWS(G$2:G14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G14))))}
G15{=IF(ROWS(G$2:G15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!G$2:G$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!G$2:G$9)-ROW(Sheet6!G$2)+1),ROWS(G$2:G15))))}
H2{=IF(ROWS(H$2:H2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H2))))}
H3{=IF(ROWS(H$2:H3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H3))))}
H4{=IF(ROWS(H$2:H4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H4))))}
H5{=IF(ROWS(H$2:H5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H5))))}
H6{=IF(ROWS(H$2:H6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H6))))}
H7{=IF(ROWS(H$2:H7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H7))))}
H8{=IF(ROWS(H$2:H8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H8))))}
H9{=IF(ROWS(H$2:H9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H9))))}
H10{=IF(ROWS(H$2:H10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H10))))}
H11{=IF(ROWS(H$2:H11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H11))))}
H12{=IF(ROWS(H$2:H12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H12))))}
H13{=IF(ROWS(H$2:H13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H13))))}
H14{=IF(ROWS(H$2:H14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H14))))}
H15{=IF(ROWS(H$2:H15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!H$2:H$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!H$2:H$9)-ROW(Sheet6!H$2)+1),ROWS(H$2:H15))))}
I2{=IF(ROWS(I$2:I2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I2))))}
I3{=IF(ROWS(I$2:I3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I3))))}
I4{=IF(ROWS(I$2:I4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I4))))}
I5{=IF(ROWS(I$2:I5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I5))))}
I6{=IF(ROWS(I$2:I6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I6))))}
I7{=IF(ROWS(I$2:I7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I7))))}
I8{=IF(ROWS(I$2:I8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I8))))}
I9{=IF(ROWS(I$2:I9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I9))))}
I10{=IF(ROWS(I$2:I10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I10))))}
I11{=IF(ROWS(I$2:I11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I11))))}
I12{=IF(ROWS(I$2:I12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I12))))}
I13{=IF(ROWS(I$2:I13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I13))))}
I14{=IF(ROWS(I$2:I14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I14))))}
I15{=IF(ROWS(I$2:I15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!I$2:I$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!I$2:I$9)-ROW(Sheet6!I$2)+1),ROWS(I$2:I15))))}
J2{=IF(ROWS(J$2:J2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J2))))}
J3{=IF(ROWS(J$2:J3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J3))))}
J4{=IF(ROWS(J$2:J4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J4))))}
J5{=IF(ROWS(J$2:J5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J5))))}
J6{=IF(ROWS(J$2:J6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J6))))}
J7{=IF(ROWS(J$2:J7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J7))))}
J8{=IF(ROWS(J$2:J8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J8))))}
J9{=IF(ROWS(J$2:J9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J9))))}
J10{=IF(ROWS(J$2:J10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J10))))}
J11{=IF(ROWS(J$2:J11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J11))))}
J12{=IF(ROWS(J$2:J12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J12))))}
J13{=IF(ROWS(J$2:J13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J13))))}
J14{=IF(ROWS(J$2:J14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J14))))}
J15{=IF(ROWS(J$2:J15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!J$2:J$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!J$2:J$9)-ROW(Sheet6!J$2)+1),ROWS(J$2:J15))))}
K2{=IF(ROWS(K$2:K2)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K2))))}
K3{=IF(ROWS(K$2:K3)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K3))))}
K4{=IF(ROWS(K$2:K4)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K4))))}
K5{=IF(ROWS(K$2:K5)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K5))))}
K6{=IF(ROWS(K$2:K6)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K6))))}
K7{=IF(ROWS(K$2:K7)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K7))))}
K8{=IF(ROWS(K$2:K8)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K8))))}
K9{=IF(ROWS(K$2:K9)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K9))))}
K10{=IF(ROWS(K$2:K10)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K10))))}
K11{=IF(ROWS(K$2:K11)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K11))))}
K12{=IF(ROWS(K$2:K12)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K12))))}
K13{=IF(ROWS(K$2:K13)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K13))))}
K14{=IF(ROWS(K$2:K14)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K14))))}
K15{=IF(ROWS(K$2:K15)>COUNTIF(Sheet6!$A$2:$A$9,"Hoses"),"",INDEX(Sheet6!K$2:K$9,SMALL(IF(Sheet6!$A$2:$A$9="Hoses",ROW(Sheet6!K$2:K$9)-ROW(Sheet6!K$2)+1),ROWS(K$2:K15))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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