+ Reply to Thread
Results 1 to 2 of 2

Filter external data based on value in Excel?

  1. #1
    Registered User
    Join Date
    01-24-2008
    Posts
    2

    Filter external data based on value in Excel?

    I'm not really sure if this would go under "General" or "Programming", but here goes a shot. I've never really used Excel before for anything serious. Anyways, in my current job I'm MIS Director for a small online retailer, and most of my day consists of running various reports sent to me via Excel against either Access or SQL back-ends for business operations.

    In Excel 2007 I know how to import data from an external source (in this case, an Access database, but more often than not it's against a SQL Server instance). However, my current problem is thus: I have an Excel spreadsheet with some 7,000 product IDs in them, and I wish to check their price and run some minor calculations. While for this particular project I could easily import it into Excel and run a query, I'm thinking long-term.

    Is there any way to filter imported data (i.e. a WHERE clause) based on the value of an Excel column? e.g. tell Excel (in pseudocode): "Import data from this Access database, for each row match up the ProductID in the database with the value of column 'A', and insert its corresponding Price value into column 'B'". I'm not seeing a way to do this, and it would make my job so much easier if I didn't have to import into SQL Server or Access, run queries and export back to another Excel file.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try this.

    1) Access database c:\temp\test3.mdb
    2) Table: access_table2
    3) The table has 2 fields COL_1 and COL_2
    4) Range A1:A3 has the Product IDs and these would be found in COL_1 of the database.

    The code below will loop through the items in column A, and bring back the result from COL_2 for the match.

    Please Login or Register  to view this content.
    HTH

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1