Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving data from an Excel database (Excel 2000 SR1)

    I have a large Excel database from which I want to extract data into a column on another sheet...I could if statements or VLOOKUP but the data in the database is not sorted by the column that has the criteria, therefore my extracted list would contain blanks...which I don't want.

    Here are the specifics: Column B of the database contains an account number (which I want to extract into the list)

    Column A contains a switch which is either "P" (for Print) or "N" (do not print).

    I want a formula/macro that looks at whether the row contains a "P" and if it does pulls the account number into a list (column) on a sheet called lists. I want the formula/macro to "skip" the rows that contain "N".

    For example, rows 1 to 5 contain "P" so the formula/macro inserts the account numbers from those rows into rows 1 - 5 on the lists page. Rows 6 and 7 contain "N". Row 8 contains the next "P", so I want the formula/macro to extract the account number in row 8 and place it in row 6 on the lists page.

    I hope this description is not too confusing. Can anyone help me?

    Thanks in advance,

    Christa

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Retrieving data from an Excel database (Excel 2000 SR1)

    This would be a simple macro version, assuming your source data layout is pretty straightforward and resides on a sheet called "Data":

    Sub CopyP2List()
    Worksheets("Data").Activate
    With ActiveSheet.UsedRange 'select all the source data
    .AutoFilter Field:=1, Criteria1:="P" 'filter the first column for P
    'next line of code copies only the P rows to sheet "List" starting in cell A1
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("List").Range("A1")
    .AutoFilter 'turn the autofilter off
    End With
    Worksheets("List").Activate 'switch to the P list
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Retrieving data from an Excel database (Excel 2000 SR1)

    Hi, Christa. The answer depends on what do you want to do with the P's.

    If you want to show only the P's you can use Data, Autofilter, click on the autofilter down-arrow for the column containing P, and select the P's. The effect is that all non-P rows are hidden. You can then print only the P's. This way you wouldn't need a 'List' Sheet.

    If you need to you can copy only the P's to another sheet using Goto, Special, Visible Cells Only, Copy, Paste. Then sort them if desired.

    But if you need to to do an extract routinely, then VBA may be the best way to go.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving data from an Excel database (Excel 2000 SR1)

    Thanks John! with a little tweaking...the macro works perfectly. You saved me a lot of time reading VBA manuals

    Thanks again,

    Christa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •