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.
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.
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:
It lets you
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
(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)
Create List with filtering criteria:
Column name on top, all following lines are combined with "OR" operator (Cells A11:A13in my example
Select cell range which shall be filtered (A2:E6 in the example)
Go to menu Data / Filters / Advanced Filters...
Define filtering criteria:
results shall be shown in new cell range
criteria in range A11:A13
results will be in range A20
After confirming with ok, you should have the filtered rows in Cells A20:E22 like that:
You'll also find a tutorial video for Excel 2010 on Youtube
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.
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:
