How to copy lines in Excel file that contain a value from a list

Question

Description

I have a CSV file with 6000+ lines in it. I have a list of about 300 words that each appear in only one row of the CSV file.

Problem

How do I extract only the rows that contain a word from a list so that I am left with about 300 rows; one for each word in the list.

Example:

    user_a   info 1   ...   ...
    user_b   info 2   ...   ...
    user_c   info 3   ...   ...
    user_d   info 4   ...   ...
    user_e   info 4   ...   ...
    user_f   info 4   ...   ...
    user_g   info 4   ...   ...
    ...      ...      ...   ...
    ...      ...      ...   ...

In the above, I have a list: "user_a, user_c, user_g". How would I get a CSV with only:

    user_a   info 1   ...   ...
    user_c   info 3   ...   ...
    user_g   info 4   ...   ...

Answer 1

I've assumed that your 6000 lines are in a worksheet called "Records" and that the data starts in row 2 and column A. In another sheet, copy your list in Column A in rows 2 through 300. Now in column B, starting in Row 2 copy this formula down to row 300 and over as many columns as needed:

=INDEX(Records!B$2:B$7,MATCH($A2,Records!$A$2:$A$7,0))

My assumption is that each word in your list forms the entire contents of a cell, e.g., "user_a" appears alone in a cell, but I'm not entirely sure that's the case. If, instead, cells in the Records worksheet contain values like "other stuff user_a more stuff" then add wildcards to the formula like this:

=INDEX(Records!B$2:B$7,MATCH("*" & $A2 & "*",Records!$A$2:$A$7,0))

Answer 2

I know just posting a link isn't super helpful, but there is a lot of text and pictures, and I don't want to write it all here. D= http://www.contextures.com/xladvfilter01.html It's a simple way, you'd just have to make a list of what you want first. Look at "Excel Advanced Filter Criteria Examples" I think this might be a quick easy way to do what you want (and you can post it into another sheet, then save your filtered list as a new .csv). I hope this helps!