Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a huge spreadsheet and column C contains IDs such as 1, 99, 732387, 28. I have a separate list of IDs such as 23, 87, 902 and I only want to display the rows whose column C value belongs to my list. What is the simplest way to do that?

Example:
My spreadsheet:
A B 1 C D
E F 3 G H
I J 8 K L
M N 9 O P
My list: 3, 9.
I want to see 
E F 3 G H 
M N 9 O P
and hide other rows.
share|improve this question

3 Answers 3

Depending on how big the data set is, I can think of two approaches.

The first would be to use filters. There's a decent tutorial on this here

The second one be to use VLOOKUP. You can do this with VLOOKUP with something like:

=IF(ISNA(VLOOKUP(C1;F:F;1;FALSE));"";C1)

Breaking this formula down, the inner most statement is the VLOOKUP, where you look for an exact match for the the value in C1 in column F. If no match is found, an #N/A will be returned, and ISNA() will return True. The True clause for the IF statement says make the cell "", otherwise, keep it.

The full implementation would be something like putting that formula in column A on a new worksheet, and columns B through F would be of the form:

=IF(ISBLANK(A1),"",B1)

And repeat that for the other columns, and fill down.

Once that's done, you could copy and paste special, values only, and sort the data to get rid of the empty rows.

share|improve this answer

Excel has a handy tool called "format to table" which allows you to put your data into a table that can be sorted or filtered easily.

Here's MS own piece of it: http://office.microsoft.com/en-us/excel-help/create-or-delete-an-excel-table-previously-a-list-in-a-worksheet-HA010342376.aspx

Basically all you do is select your data, choose "format to table" from HOME section of ribbon and you have column headers with a little arrows at the lower right corner from which to filter data by unticking checkboxes.

Like so: enter image description here

enter image description here

share|improve this answer
    
The list is too big to check/uncheck boxes manually. –  Gambit Aug 28 '13 at 23:54
    
If you check above the checkboxes I think there is a search function so you can filter with that. Try it out in small scale and see if it works. –  Grietom Aug 29 '13 at 9:54

I think, filtering is the way to go.

As described in earnie's tutorial and in Grietom's answer, you can select filtering criteria by enabling/disabling checkboxes.

Advanced filtering in Excel

If your list of lines to show is longer (and maybe changes from time to time), you could also use the approach of advanced filtering as described here in detail:

http://office.microsoft.com/en-001/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx

It lets you

  • use a range of cells for definition of filtering criteria
  • define more complex combinations of criteria with AND/OR, ...
  • you can filter the original table directly or let Excel write the results into another table

taken from the above mentioned Microsoft tutorial:

The following sections provide examples of complex criteria.

  • Multiple criteria in one column
  • Multiple criteria in multiple columns where all criteria must be true
  • Multiple criteria in multiple columns where any criteria can be true
  • Multiple sets of criteria where each set includes criteria for multiple columns
  • Multiple sets of criteria where each set includes criteria for one column
  • Criteria to find text values that share some characters but not others
  • Criteria created as the result of a formula
    • Filtering for values greater than the average of all values in the data range > * Filtering for text by using a case-sensitive search

Step by step tutorial

(sorry, I don't have Excel 2010, but an older version, so maybe the menus are a little different, but in general it should work like that)

  1. Create List with filtering criteria:
    Column name on top, all following lines are combined with "OR" operator (Cells A11:A13in my example enter image description here

  2. Select cell range which shall be filtered (A2:E6 in the example)

  3. Go to menu Data / Filters / Advanced Filters...
    enter image description here

  4. Define filtering criteria:
    results shall be shown in new cell range
    criteria in range A11:A13
    results will be in range A20 enter image description here

  5. After confirming with ok, you should have the filtered rows in Cells A20:E22 like that:
    enter image description here

Additional ressources

You'll also find a tutorial video for Excel 2010 on Youtube

share|improve this answer
    
Precisely how? I went through Excel help for filtering but I did not find the answer. –  Gambit Aug 28 '13 at 23:55
    
see my edits - this should also be possible with Excel 2010 –  Martin Aug 29 '13 at 2:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.