Separate one table into two tables

K

kod2th3e

Board Regular
Joined
Apr 2, 2008
Messages
87
I have data similar to what is in the table below on one worksheet and I'd like to create two tables on another worksheet (one for "Upper" and one for "Lower" type). I was wondering if it's possible to create a lookup/index/match or something else? formula that looks at the "Type" column and displays the entire row of data in the new tables that I create in order from top to bottom or first instance to last instance.

Worksheet1 - 1 Table (assume "Assembly" header starts in cell "A1"
AssemblyUpper P/NQtyLower P/NQtyTypeSubmitted by
111112310UpperCal S.
222212315UpperPam W.
33334565UpperKyle T.
111198715LowerSam A.
444463225LowerElle F.
222233135LowerToni P.
555574520UpperWallace Z.

<tbody>
</tbody>



Worksheet2 - With Two Tables (Assume "Assembly" header starts in cell "A1" for 1st table and cell "G1" for the 2nd table.

AssemblyUpper P/NQtyTypeSubmitted By:
111112310UpperCal S.
222212315UpperPam W.
33334565UpperKyle T.
555574520UpperWallace Z.

<tbody>
</tbody>


AssemblyLower P/NQtyTypeSubmitted By:
111198715LowerSam A.
444463225LowerElle F.
222233135LowerToni P.

<tbody>
</tbody>


Thanks in advance for any help/guidance.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Try

Excel 2010
ABCDEFG
1AssemblyUpper P/NQtyLower P/NQtyTypeSubmitted by
2111112310UpperCal S.
3222212315UpperPam W.
433334565UpperKyle T.
5111198715LowerSam A.
6444463225LowerElle F.
7222233135LowerToni P.
8555574520UpperWallace Z.
Sheet1



Excel 2010
ABCDE
1AssemblyUpper P/NQtyTypeSubmitted By:
2111112310UpperCal S.
3222212315UpperPam W.
433334565UpperKyle T.
5555574520UpperWallace Z.
6     
7     
8     
9     
10     
11     
12     
Sheet2
Cell Formulas
RangeFormula
A2{=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A2))))}
A3{=IF(ROWS(A$2:A3)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A3))))}
A4{=IF(ROWS(A$2:A4)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A4))))}
A5{=IF(ROWS(A$2:A5)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A5))))}
A6{=IF(ROWS(A$2:A6)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A6))))}
A7{=IF(ROWS(A$2:A7)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A7))))}
A8{=IF(ROWS(A$2:A8)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A8))))}
A9{=IF(ROWS(A$2:A9)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A9))))}
A10{=IF(ROWS(A$2:A10)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A10))))}
A11{=IF(ROWS(A$2:A11)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A11))))}
A12{=IF(ROWS(A$2:A12)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A12))))}
B2{=IF(ROWS(B$2:B2)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B2))))}
B3{=IF(ROWS(B$2:B3)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B3))))}
B4{=IF(ROWS(B$2:B4)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B4))))}
B5{=IF(ROWS(B$2:B5)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B5))))}
B6{=IF(ROWS(B$2:B6)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B6))))}
B7{=IF(ROWS(B$2:B7)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B7))))}
B8{=IF(ROWS(B$2:B8)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B8))))}
B9{=IF(ROWS(B$2:B9)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B9))))}
B10{=IF(ROWS(B$2:B10)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B10))))}
B11{=IF(ROWS(B$2:B11)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B11))))}
B12{=IF(ROWS(B$2:B12)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!B$2:B$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B12))))}
C2{=IF(ROWS(C$2:C2)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C2))))}
C3{=IF(ROWS(C$2:C3)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C3))))}
C4{=IF(ROWS(C$2:C4)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C4))))}
C5{=IF(ROWS(C$2:C5)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C5))))}
C6{=IF(ROWS(C$2:C6)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C6))))}
C7{=IF(ROWS(C$2:C7)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C7))))}
C8{=IF(ROWS(C$2:C8)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C8))))}
C9{=IF(ROWS(C$2:C9)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C9))))}
C10{=IF(ROWS(C$2:C10)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C10))))}
C11{=IF(ROWS(C$2:C11)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C11))))}
C12{=IF(ROWS(C$2:C12)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!C$2:C$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C12))))}
D2{=IF(ROWS(D$2:D2)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D2))))}
D3{=IF(ROWS(D$2:D3)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D3))))}
D4{=IF(ROWS(D$2:D4)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D4))))}
D5{=IF(ROWS(D$2:D5)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D5))))}
D6{=IF(ROWS(D$2:D6)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D6))))}
D7{=IF(ROWS(D$2:D7)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D7))))}
D8{=IF(ROWS(D$2:D8)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D8))))}
D9{=IF(ROWS(D$2:D9)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D9))))}
D10{=IF(ROWS(D$2:D10)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D10))))}
D11{=IF(ROWS(D$2:D11)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D11))))}
D12{=IF(ROWS(D$2:D12)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D12))))}
E2{=IF(ROWS(E$2:E2)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E2))))}
E3{=IF(ROWS(E$2:E3)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E3))))}
E4{=IF(ROWS(E$2:E4)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E4))))}
E5{=IF(ROWS(E$2:E5)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E5))))}
E6{=IF(ROWS(E$2:E6)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E6))))}
E7{=IF(ROWS(E$2:E7)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E7))))}
E8{=IF(ROWS(E$2:E8)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E8))))}
E9{=IF(ROWS(E$2:E9)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E9))))}
E10{=IF(ROWS(E$2:E10)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E10))))}
E11{=IF(ROWS(E$2:E11)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E11))))}
E12{=IF(ROWS(E$2:E12)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="upper",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E12))))}
Press CTRL+SHIFT+ENTER to enter array formulas.



Excel 2010
ABCDE
1AssemblyUpper P/NQtyTypeSubmitted By:
2111198715LowerSam A.
3444463225LowerElle F.
4222233135LowerToni P.
5     
6     
7     
8     
Sheet3
Cell Formulas
RangeFormula
A8=IF(ROWS(A$2:A8)>COUNTIF(Sheet1!$F$2:$F$8,"upper"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A8))))
A2{=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A2))))}
A3{=IF(ROWS(A$2:A3)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A3))))}
A4{=IF(ROWS(A$2:A4)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A4))))}
A5{=IF(ROWS(A$2:A5)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A5))))}
A6{=IF(ROWS(A$2:A6)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A6))))}
A7{=IF(ROWS(A$2:A7)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(A$2:A7))))}
B2{=IF(ROWS(B$2:B2)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B2))))}
B3{=IF(ROWS(B$2:B3)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B3))))}
B4{=IF(ROWS(B$2:B4)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B4))))}
B5{=IF(ROWS(B$2:B5)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B5))))}
B6{=IF(ROWS(B$2:B6)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B6))))}
B7{=IF(ROWS(B$2:B7)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B7))))}
B8{=IF(ROWS(B$2:B8)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!D$2:D$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(B$2:B8))))}
C2{=IF(ROWS(C$2:C2)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C2))))}
C3{=IF(ROWS(C$2:C3)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C3))))}
C4{=IF(ROWS(C$2:C4)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C4))))}
C5{=IF(ROWS(C$2:C5)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C5))))}
C6{=IF(ROWS(C$2:C6)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C6))))}
C7{=IF(ROWS(C$2:C7)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C7))))}
C8{=IF(ROWS(C$2:C8)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!E$2:E$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(C$2:C8))))}
D2{=IF(ROWS(D$2:D2)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D2))))}
D3{=IF(ROWS(D$2:D3)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D3))))}
D4{=IF(ROWS(D$2:D4)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D4))))}
D5{=IF(ROWS(D$2:D5)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D5))))}
D6{=IF(ROWS(D$2:D6)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D6))))}
D7{=IF(ROWS(D$2:D7)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D7))))}
D8{=IF(ROWS(D$2:D8)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!F$2:F$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(D$2:D8))))}
E2{=IF(ROWS(E$2:E2)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E2))))}
E3{=IF(ROWS(E$2:E3)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E3))))}
E4{=IF(ROWS(E$2:E4)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E4))))}
E5{=IF(ROWS(E$2:E5)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E5))))}
E6{=IF(ROWS(E$2:E6)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E6))))}
E7{=IF(ROWS(E$2:E7)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E7))))}
E8{=IF(ROWS(E$2:E8)>COUNTIF(Sheet1!$F$2:$F$8,"lower"),"",INDEX(Sheet1!G$2:G$8,SMALL(IF(Sheet1!$F$2:$F$8="lower",ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS(E$2:E8))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
VLook-Up Match first 3 characters of one cell with another column
2
Replies
10
Views
861
sandy666
sandy666
K
  • Solved
Check & Update existing rows of data to fill in the blanks
Replies
1
Views
491
Kemidan2014
K
K
  • Question
Return row data from 1st, 2nd, etc. instance of lookup
Replies
3
Views
586
alansidman
A
H
  • Question
vlookup to check condition if DATE is between two dates (in two separate columns)
Replies
3
Views
357
Fluff
Fluff
P
  • Question
Compare 2 columns delete the row if match found
Replies
1
Views
653
ravishankar
R

Forum statistics

Threads
1,214,976
Messages
6,122,539
Members
449,088
Latest member
RandomExceller01

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