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

N

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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes

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



Excel 2010
ABCDEFGHIJK
1DepartmentSTOCK_CODEDESCRIPTIONQTY_IN_STOCKORDER_NUMBERORDER_DATENAMEQTY_ORDERQTY_DELIVEREDOutstandingDESPATCH_DATE
2HosesA1.5MS/R152152mm x 1.5m Semi-Rigid Aluminium Hose3210794943191Naples Components Limited60643313
3HosesA1.5MS/R152152mm x 1.5m Semi-Rigid Aluminium Hose3210793643191WS Westin Ltd (account 1)2002043374
4HosesA3MS/R102102mm x 3m Semi-Rigid Aluminium Hose1410796143221Vapourflow Ltd Stores50543313
5HosesA3MS/R127127mm x 3m Semi-Rigid Aluminium Hose11510787643160Kair Ventilation Ltd50543313
6HosesA3MS/R127127mm x 3m Semi-Rigid Aluminium Hose11510796143221Vapourflow Ltd Stores50543313
7           
8           
9           
10           
11           
12           
13           
14           
15           
Sheet7
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
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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