+ Reply to Thread
Results 1 to 4 of 4

Filtering One Sheet with Data From Another

  1. #1
    Registered User
    Join Date
    10-19-2022
    Location
    US
    MS-Off Ver
    Microsoft 365
    Posts
    3

    Post Filtering One Sheet with Data From Another

    I have two worksheets. One is a massive list of data (10,000+ entries). A second list contains about 400 entries. Both use a unique six digit number to identify a specific organization (111245 is Organization A, 456789 is Organization B, etc.) and then have similar columns for various data points. I've been scratching my head trying to figure out how to filter the larger dataset, using the unique ID, down to just the organizations in the second data sheet. Short of adding a filter and clicking the 400 individual organizations, is there an easier way to accomplish this?

    UPDATE: Included two files as an example. File A is a complete dataset where file B is a smaller subset of the larger dataset. How do I take the organizations from File B to filter File A to show only those organizations. (Again the actual files have 10000+ lines of data that need to be filtered.
    Attached Files Attached Files
    Last edited by fp_dude1987; 01-25-2023 at 07:21 PM. Reason: Added Example

  2. #2
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Filtering One Sheet with Data From Another

    Hello,
    an example workbook with some sketchy information would be helpful, but at some point you'll need the advanced filter.
    Select a table from the first header sheet, then go to the data tab and find the advanced filter button .
    for criteria range select the list of ID's from the second sheet
    Last edited by tanasedn; 01-25-2023 at 06:52 PM.
    Diana Tanase


    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Filtering One Sheet with Data From Another

    With 365, you can use the FILTER function combined with COUNTIF to extract a list to another location or another worksheet.


    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Filtering One Sheet with Data From Another

    With Power Query, you can join the two tables on the common "ID" field.

    For simplicity sake I put the two tables in the same workbook and then joined them.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to Calculate Win/Loss Records from Sheet 1 to Sheet 2 After Filtering Data
    By chaskin in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-26-2022, 02:40 AM
  2. [SOLVED] Filtering one sheet using column of data from another sheet
    By LilSisKin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2021, 03:00 PM
  3. Filtering one sheet based off the data filtered in another sheet
    By sgupta4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2019, 12:07 PM
  4. Macro filtering on main data sheet and creating new sheet with the results
    By will5will in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2018, 11:43 AM
  5. Filtering Data From Another Sheet
    By sorce in forum Excel General
    Replies: 5
    Last Post: 04-06-2017, 11:27 AM
  6. Filtering Data From Another Sheet
    By sorce in forum Excel General
    Replies: 2
    Last Post: 04-05-2017, 11:52 AM
  7. Filtering data to another sheet
    By lsy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2005, 09:05 AM

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