Index/Match or Vlookup from a large table and order into a new table

[sdm100]

Hello,

I have a large list of vessels which have visited a harbour. Cols A:L show various details such as date of visit, name of vessel, captain etc.

I mark in the last column if the community has receieved a complaint associated with that vessel with a simple 'Y'

In order to store additional information about the complaint, I'd like in a different sheet to output all the 'Y' rows (could be one in every 50 rows or so) then order them by the date the vessel visited the harbour which is stored in column A of the particular row, followed by some other supporting information from Cols B:E in the same row....then I will perform my additional work for the complaint in the Cols to the right of E.

I don't want to just use a Vlookup as I will have a load of blank rows if I replicate it down.

I'd like a seperate table that is pulling in the 40 or so 'Y' rows from the list of 10,000 rows on the main table in a seperate workbook.

Hope that makes sense and any advice would be great on how to do this!

Mr Sean

 

[mumps]

I think you will need a macro to do what you want. What is the name of the sheet containing the list of vessels? Which column will contain the "Y"? Does the first row contain the headers with data starting in row 2? Are there any blank rows in your data?

 

[sdm100]

Hello, thanks for the reply, I was thinking that I might need a macro too, but was hoping to avoid it!

The name of the sheet with the vessels is Prod Liftings and the 'Y' will be in Col P.

Headers are on row 3 and the first row with vessel information is on row 4.

Appreicate any advice on VBA for this one.

Sean

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet
    Set srcWS = Sheets("Prod Liftings")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS.Cells(3, 1).CurrentRegion
        .AutoFilter 16, "Y"
        Intersect(srcWS.Rows("3:" & LastRow), srcWS.Range("A:E")).SpecialCells(xlCellTypeVisible).Copy 'Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With
    Workbooks.Add
    With Sheets("Sheet1")
        .Cells(1, 1).PasteSpecial
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With Sheets("Sheet1").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:E" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    srcWS.Range("P1").AutoFilter
    Application.ScreenUpdating = True
End Sub