Return row data from 1st, 2nd, etc. instance of lookup

[kod2th3e]

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 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: grid, width: 500"]
<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: grid, width: 500"]
<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: grid, width: 500"]
<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.

 

[alansidman]

Here is a VBA solution. Name three sheets as shown below.

Option Explicit

Sub UpLow()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
    Set s1 = Sheets("Data")    'Rename as needed
    Set s2 = Sheets("Upper")
    Set s3 = Sheets("Lower")
    Dim lr As Long, lr2 As Long, lr3 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Dim arrU As Variant
    Dim arrL As Variant
    arrU = Array("Assembly", "Upper P/N", "Qty", "Type", "Submitted by")
    arrL = Array("Assembly", "Lower P/N", "Qty", "Type", "Submitted by")
    s2.Range("A1:E1") = arrU
    s3.Range("A1:E1") = arrL
    Application.ScreenUpdating = False
    For i = 2 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        lr3 = s3.Range("A" & Rows.Count).End(xlUp).Row
        If s1.Range("B" & i) <> "" Then
            Application.Union(Range("A" & i & ":C" & i), Range("F" & i & ":G" & i)).Copy s2.Range("A" & lr2 + 1)
        Else
            Application.Union(Range("A" & i), Range("D" & i & ":G" & i)).Copy s3.Range("A" & lr3 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[kod2th3e]

Alan,

Thanks a ton for your suggestion, do you know if there is a way of using a vlookup or index/match function to accomplish this? The reason I ask is two fold: the 1st reason is Sheet2 will be saved as a webpage to be displayed on monitor in a department and the 2nd reason is the data in the table on Sheet1 will be rotating. By rotating I mean I have a form that allows a user to input new data and remove old data.

 

[alansidman]

An alternative and easy way to do this is to use the Get and Transform function in Power Query which is part of the latest versions of Excel. It is quick and easy to do. If you need assistance, there are many tutorials on line.

1. Open File and select Data-->Get and Transform
2. Select From Table. Highlight your table
3. Filter out Lower P/N
4. Delete columns that don't apply
5. Export to your Excel file
6. Repeat process for Upper P/N

Takes a few seconds to do this.