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"
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Assembly[/TD]
[TD]Upper P/N[/TD]
[TD]Qty[/TD]
[TD]Lower P/N[/TD]
[TD]Qty[/TD]
[TD]Type[/TD]
[TD]Submitted by[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]123[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Upper[/TD]
[TD]Cal S.[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]123[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]Upper[/TD]
[TD]Pam W.
[/TD]
[/TR]
[TR]
[TD]3333[/TD]
[TD]456[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Upper[/TD]
[TD]Kyle T.[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD][/TD]
[TD][/TD]
[TD]987[/TD]
[TD]15[/TD]
[TD]Lower[/TD]
[TD]Sam A.[/TD]
[/TR]
[TR]
[TD]4444[/TD]
[TD][/TD]
[TD][/TD]
[TD]632[/TD]
[TD]25[/TD]
[TD]Lower[/TD]
[TD]Elle F.[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD][/TD]
[TD][/TD]
[TD]331[/TD]
[TD]35[/TD]
[TD]Lower[/TD]
[TD]Toni P.[/TD]
[/TR]
[TR]
[TD]5555[/TD]
[TD]745[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]Upper[/TD]
[TD]Wallace Z.[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Assembly[/TD]
[TD]Upper P/N[/TD]
[TD]Qty[/TD]
[TD]Type[/TD]
[TD]Submitted By:[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]123[/TD]
[TD]10[/TD]
[TD]Upper[/TD]
[TD]Cal S.[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]123[/TD]
[TD]15[/TD]
[TD]Upper[/TD]
[TD]Pam W.[/TD]
[/TR]
[TR]
[TD]3333[/TD]
[TD]456[/TD]
[TD]5[/TD]
[TD]Upper[/TD]
[TD]Kyle T.[/TD]
[/TR]
[TR]
[TD]5555[/TD]
[TD]745[/TD]
[TD]20[/TD]
[TD]Upper[/TD]
[TD]Wallace Z.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Assembly[/TD]
[TD]Lower P/N[/TD]
[TD]Qty[/TD]
[TD]Type[/TD]
[TD]Submitted By:
[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]987[/TD]
[TD]15[/TD]
[TD]Lower[/TD]
[TD]Sam A.[/TD]
[/TR]
[TR]
[TD]4444[/TD]
[TD]632[/TD]
[TD]25[/TD]
[TD]Lower[/TD]
[TD]Elle F.[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]331[/TD]
[TD]35[/TD]
[TD]Lower[/TD]
[TD]Toni P.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for any help/guidance.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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 Question
VLook-Up Match first 3 characters of one cell with another column
2
Replies
10
Views
1K
sandy666
sandy666
K
  • Solved
Check & Update existing rows of data to fill in the blanks
Replies
1
Views
613
Kemidan2014
K
K
  • Question Question
Return row data from 1st, 2nd, etc. instance of lookup
Replies
3
Views
694
alansidman
A
H
  • Question Question
vlookup to check condition if DATE is between two dates (in two separate columns)
Replies
3
Views
427
Fluff
Fluff
P
  • Question Question
Compare 2 columns delete the row if match found
Replies
1
Views
759
ravishankar
R

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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