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

sdm100

New Member
Joined
Jun 15, 2013
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Jun 15, 2013
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,359
Members
416,245
Latest member
Xterminat

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
Top