Results 1 to 3 of 3

Thread: Extract Selected Data from Excel - Advanced Filter

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10

    Lightbulb Extract Selected Data from Excel - Advanced Filter

    Hi All,

    One can extract selected columns of data from a data set either by using VBA or Microsoft query.
    But one can achieve the same, with the less explored Excel's in-built tool Advanced filter.

    The figure 1 shows the whole data set (List Range)

    Sheet1

    *ABCDEFGHIJ
    1IDOrder IDProductQuantityUnit PriceDiscountStatus IDDate AllocatedPurchase Order IDInventory ID
    22730Northwind Traders Beer100$14.000.00%Invoiced*9683
    32830Northwind Traders Dried Plums30$3.500.00%Invoiced**63
    42931Northwind Traders Dried Pears10$30.000.00%Invoiced**64
    53031Northwind Traders Dried Apples10$53.000.00%Invoiced**65
    63131Northwind Traders Dried Plums10$3.500.00%Invoiced**66
    73232Northwind Traders Chai15$18.000.00%Invoiced**67
    83332Northwind Traders Coffee20$46.000.00%Invoiced**68
    93433Northwind Traders Chocolate Biscuits Mix30$9.200.00%Invoiced*9781
    103534Northwind Traders Chocolate Biscuits Mix20$9.200.00%Invoiced**69
    113635Northwind Traders Chocolate10$12.750.00%Invoiced**70
    123736Northwind Traders Clam Chowder200$9.650.00%Invoiced*9879
    133837Northwind Traders Curry Sauce17$40.000.00%Invoiced**71
    143938Northwind Traders Coffee300$46.000.00%Invoiced*9977
    154039Northwind Traders Chocolate100$12.750.00%Invoiced*10075
    164140Northwind Traders Green Tea200$2.990.00%Invoiced*10173
    174241Northwind Traders Coffee300$46.000.00%Allocated*102104
    184342Northwind Traders Boysenberry Spread10$25.000.00%Invoiced**84
    194442Northwind Traders Cajun Seasoning10$22.000.00%Invoiced**85
    204542Northwind Traders Chocolate Biscuits Mix10$9.200.00%Invoiced*103110
    214643Northwind Traders Dried Plums20$3.500.00%Allocated**86
    224743Northwind Traders Green Tea50$2.990.00%Allocated**87
    234844Northwind Traders Chai25$18.000.00%Allocated**88
    244944Northwind Traders Coffee25$46.000.00%Allocated**89
    255044Northwind Traders Green Tea25$2.990.00%Allocated**90


    Excel tables to the web >> Excel Jeanie HTML 4

    ... continued..
    Last edited by Admin; 06-21-2013 at 11:48 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    and here is the Copy to range

    Sheet1

    *MN
    1Order IDStatus ID


    Excel tables to the web >> Excel Jeanie HTML 4


    Now goto the Advanced filter, select the list range, check on Copy to another location, select M1:N1 as the Copy to range, and it's done.


    and the result is

    Sheet1

    *MN
    1Order IDStatus ID
    230Invoiced
    330Invoiced
    431Invoiced
    531Invoiced
    631Invoiced
    732Invoiced
    832Invoiced
    933Invoiced
    1034Invoiced
    1135Invoiced
    1236Invoiced
    1337Invoiced
    1438Invoiced
    1539Invoiced
    1640Invoiced
    1741Allocated
    1842Invoiced
    1942Invoiced
    2042Invoiced
    2143Allocated
    2243Allocated
    2344Allocated
    2444Allocated
    2544Allocated


    Excel tables to the web >> Excel Jeanie HTML 4

    I hope this will find useful.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    13
    Awesome ---

Similar Threads

  1. Replies: 2
    Last Post: 04-14-2013, 09:15 PM
  2. Auto filter and sum up data
    By Ryan_Bernal in forum Excel Help
    Replies: 6
    Last Post: 01-02-2013, 06:42 PM
  3. Replies: 2
    Last Post: 09-24-2012, 06:24 PM
  4. Copy selected data to other excel sheet
    By dhiraj.ch185 in forum Excel Help
    Replies: 2
    Last Post: 02-02-2012, 06:23 AM
  5. Extract data using Advanced Filter
    By PcMax in forum Excel Help
    Replies: 4
    Last Post: 01-02-2012, 02:31 AM

Tags for this Thread

Posting Permissions

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