[Solved] Data filter advice

[mrcastle]

Hi, my goal is to divide a spreadsheet into 5 separate spreadsheets. The master spreadsheet has about 1000 line items. The only thing I've found so far is the data filter, which allows me to select similar items and then I'm able to copy those items to a specified range. That's great except it only copies that data to the new range. I actually want to move that data to the new range. So I can create spreadsheet1,2,3,4 and then whatever is left over would be spreadsheet 5. I'm stuck and appreciate any advice!

[RusselB]

Are you using the Copy Results option under More Options in the Standard or Advanced Filter selection?
If so, then you could run the filter a second time, but don't copy the data to a new location, thus highlighting/organizing the data into a range that you should be able to delete using the delete key.
If you aren't, then I suspect you are using copy & paste (ctrl+c/ctrl+v) to copy the data. If this is true, then use Cut (ctrl+x) rather than Copy (ctrl+c)

[mrcastle]

Yes, you're right. I am selecting the copy results option. This brings the desired line items to the range I've defined. I just copy them to a new spreadsheet and I'm on to item #2 of five. I tried doing what you suggested: I end up with the items I want to filter out but the remainder of the line items are gone. From your description, it sounds like the second time should be highlighting the items I just copied to the new range but that isn't happening. Everything disappears except for the items I selected to filter. I tried it several time with no luck.

So I have a spreadsheet with 1000 items of sheet music. I want to pull the authors verdi, handel, mozart, and friml out into their own individual spreadsheets. I'm able to do this using the filter but the 1000 line spreadsheet remains at 1000 lines. I tried what you suggested. It basically elimates all of the lines of the spreadsheet except for the line items I selected to be filtered. I'm trying to figure out a way to pull those 4 composers out of the spreadsheet and then whatever is left-over becomes my miscellaneous spreadsheet. Sorry to repeat myself but hopefully this will give you an idea what I'm doing wrong.... thx

[MrProgrammer]

Hi, and welcome to the forum.

.I want to pull the authors verdi, handel, mozart, and friml out into their own individual spreadsheets. I'm able to do this using the filter but the 1000 line spreadsheet remains at 1000 lines.

Data → Filter → Standard Filter allows the selection of four items. After copying the four composers to new sheets, select all of them in the original sheet using OR in Standard filtter. You want to delete those rows and keep the rest. Select these filtered rows → Edit → Delete Cells. (If you have cells selected instead of rows, Shift+Space will select the entire row.) The Delete Cells operation will delete those rows, but row deletion does not touch rows hidden by the filter. Then use Data → Filter → Remove filter to reveal the hidden rows again. Read section 7. Analyzing and modifying your data in Ten concepts that every Calc user should know which discusses using filters in this way.

Quite a few people who have split a master list into separate sheets later regret that decision and re-combine them. Be sure you never want to do any analysis which includes all the composers. Analyzing data stored in separate sheets is difficult, at best. I'd suggest leaving the data where it is and using filters when you want to work with subsets, say individual composers, or just Handel and Mozart, however I did want to provide you with an answer to your question as this may also help others with similar situations.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[mrcastle]

Awesome! Sorry RusselB, you were saying pretty much the same thing but I just didn't get it. Removing the filtered rows did the trick. Thanks to both of you.