excel

January 27, 2009 4:48 PM

Excel question: I have 50K records. Now the first column contains a code which goes something like: boulangeKL12223_wwetr. Now what I want to do is, extract all those rows that contain 'boulangeKL' in the first column, and save this as a separate xls file. How do I do this? PS: IF you can show me some good resources for Excel, much appreciated.
posted by whiterussian to work & money (10 comments total)

Well, the fast-and-dirty way, and the way I'd go about it, since it only takes a few seconds: sort all records alphabetically by the first column. Now, all records whose first rows start with 'boulangeKL' are in one place. Save As the file you want to create and delete everything before and after the 'boulangeKL' records.

Hope that helps.
posted by koeselitz at 4:55 PM on January 27 [1 favorite has favorites]

is the boulangeKL bit always the first part of the code? If so, sort the records by the code column, select the first boulangeKL row, scroll down to the last one, hold shift and select the last row to select all the rows inbetween, cut, paste, job done.
posted by derbs at 4:55 PM on January 27

snap :)
posted by derbs at 4:56 PM on January 27

The microsoft.public.excel newsgroup is a great resource when you've got questions - quick responses from people who know their stuff, most of whom are volunteering their expertise.
posted by Picklegnome at 5:00 PM on January 27

data>autofilter
then go to the column and use the dropdown box to select your criteria
highlight the resulting filtered sheet
copy and paste to your desired location.
posted by lester's sock puppet at 5:05 PM on January 27

If "boulangeKL" is not always the first part of the code, then you may need to do something like the following:

Fill up a neighboring column with this formula: IF(ISERR(FIND("boulangeKL",A1)), "no", "yes") where e.g.: the first row in your code column is A1. Then, do a Data->Autofilter on this new column.
posted by mhum at 5:19 PM on January 27 [3 favorites has favorites]

To refine what others have said:

Select the column.
Go to data->filter->autofilter.
Select the new filter button at the top of the column
From the list shown by the filter button, select 'Custom'
On the left of the new dialog, there's a dropdown. In this, select "contains".
Type in your "boulangeKL" in the textbox and hit 'OK'.
Re-select the column, and then copy and paste to whereever.
posted by pompomtom at 7:58 PM on January 27

There isn't much context here, but if you're on the verge of doing a lot of data manipulation like this, you might want to check out some other software. If you're willing to broaden your horizons a little bit, you could do this in R (free statistics program) with three lines of code.

Save your worksheet as a .csv. Download R, open it, then paste in (one line at a time):

olddata<-read.csv(file.choose()) #Choose the file, then press "Enter"

newdata<-olddata[,1][substr(olddata[,1],1,10)=="boulangeKL"] #Literally: newdata should be the first column of olddata wherever the first ten characters are "boulangeKL"

write.csv(newdata,file.choose(),row.names=FALSE) #Write a new .csv without any pesky row names.

This is overkill, and if I were doing this just once, I would definitely use koeselitz's method. But if you're going to do this a lot, or want to do something more complex (say, automatically generate separate files for the boulangeKL, boulangeGL,boulangeFL entries), R is pretty much freaking awesome, in my estimation. Feel free to MeFi mail me if you have questions.
posted by McBearclaw at 12:03 AM on January 28 [1 favorite has favorites]

Excel resources? Mr Excel is one of my favorites, and actually the excel tag here on AskMe is great, too.
posted by soelo at 9:17 AM on January 28

I second the recommendation for MrExcel.com -- go to the messageboards and post your question. I have been saved by geniuses there more times than I can count and always with really fast response times (international helpers means there are people online 24/7).
posted by giddygirlie at 7:05 PM on January 31