Share via

Filter data in Sheet 1 based on list in Sheet 2

Anonymous
2012-06-25T00:02:00+00:00

PREFACE: I AM AN EXCEL DUMMY! And I am working with Excel 2008 for Mac. 

I have two sheets of data. Sheet 1 is my Long List. It contains over 40,000 rows of data. Column A in the Long List contains ID numbers, labelled "ID". There are several rows of data for each ID on the Long List. Sheet 2 is the Short List. It contains different data pertaining to a subset of IDs that are on the Long List. Column A in the Short List contains a subset (almost 3,000, unique values) of the IDs in Column A in the Long List. I want to filter the Long List based on Column A in the Short list, so that I can look at only the data pertaining to those IDs that are included Short List. HOW THE HECK DO I DO THIS?

PLEASE EXPLAIN IT TO ME LIKE I AM A DUMMY!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-06-25T13:07:55+00:00

    One way, using Advanced Filter.

    1. select Sheet 2 column A and name it "ShortListIDs" (without the quotes) by entering the name in the Name box on the left side of the Formula Bar (alternatively, use Insert/Name/Define with the column selected, and enter the name).
    2. On Sheet 1, choose a column to the right of your data (for this example, say, column J) and enter in row 2 (leaving row 1 blank):

    =COUNTIF(ShortListIDs, A2)

    1.  Select cell A2, and choose Data/Advanced Filter. In the dialog, select the "Filter the list, in place" radio button,
    2. Verify the List Range contains the range of your data.
    3. In the Criteria Range box, enter J1:J2 (make sure you include row 1)
    4. Click OK

    60+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-02-09T13:11:49+00:00

    Genius, I would give you a hug if you were near me

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-06-06T05:51:19+00:00

    There is also a way to add Autofilter by taking criteria list from another sheet. Actually it will be useful if you need to add mutliple criteria to a autofilter. Only thing is that you need some basics about how to write a VBA Excel macro code.

    Click here to know how to implement it with VBA with a simple command.

    0 comments
  3. Bob Jones AKA CyberTaz MVP 435K Reputation points
    2012-06-25T13:00:17+00:00

    It's hard to offer a suggestion because it really isn't clear how the data is arranged or what constitutes a record. From your description it sounds as though everything is in Column A. If you have the data set up such as:

    Column A

    ID [1]

    data item 1

    data item 2

    data item 3

    ID [2]

    data item 1

    data item 2

    data item 3

    ... and so on, there is no way I know of to effectively "filter" the data or manage it in any other way. This is particularly true in Excel 2008 where VBA isn't an option. Excel is designed to work with lists of records in which case the same example data above would occupy several columns, i.e.;

     A                 B                   C                 D

    ID Field     Field 2          Field 3         Field 4

      [1]            Data              Data            Data

      [2]            Data              Data            Data

    So each field of data is in a separate column & each record occupies a single row.

    If I've misinterpreted, please clarify what it is that you're working with. Complete explicit examples are quite helpful.

    Regards,

    Bob J.

    0 comments
  4. Anonymous
    2012-06-25T12:55:56+00:00

    the Only way it can be done that I can find Is

    1. fix your two Sheets
    2. Now on Sheet two  in a Blank Column  in row 1(using an Example) type formula: =Sheet1!A1.
    3. Now Drag formula down as many cells as are needed (mouse to bottom right corner of cell when + appears drag down as many places as you need.
    4. Now choose all column then go to sort and choose the column  from sheet one after checking the check mark for has header titles. do sort.
    5. As information in Sheet1 (with the column ID is updated) click on entire sheet two and do another sort base in ID.
    6. when you don't need to look at  Column ID in second sheet you can always click on that column and hide it.
    0 comments