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.

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

2 Answers 2

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. –  Jesse 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. –  Jesse Sep 16 '11 at 14:56

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.