Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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   ...   ...
share|improve this question
    
I think this will take VBA, but we'll see if anyone can come up with something. – Lance Roberts Sep 15 '11 at 21:19
up vote 3 down vote accepted

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))
share|improve this answer
    
Brilliant! Thank you very much. – Gruzzles Sep 16 '11 at 14:56

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!

share|improve this answer
    
+1 Great resource!! I have searched for such feature since long. – venomrld Sep 16 '11 at 4:31
    
Thanks for the resource. – Gruzzles Sep 16 '11 at 14:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .