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"

Assembly	Upper P/N	Qty	Lower P/N	Qty	Type	Submitted by
1111	123	10			Upper	Cal S.
2222	123	15			Upper	Pam W.
3333	456	5			Upper	Kyle T.
1111			987	15	Lower	Sam A.
4444			632	25	Lower	Elle F.
2222			331	35	Lower	Toni P.
5555	745	20			Upper	Wallace Z.

<tbody>
</tbody>

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

Assembly	Upper P/N	Qty	Type	Submitted By:
1111	123	10	Upper	Cal S.
2222	123	15	Upper	Pam W.
3333	456	5	Upper	Kyle T.
5555	745	20	Upper	Wallace Z.

<tbody>
</tbody>

Assembly	Lower P/N	Qty	Type	Submitted By:
1111	987	15	Lower	Sam A.
4444	632	25	Lower	Elle F.
2222	331	35	Lower	Toni P.

<tbody>
</tbody>

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.