Copy&Paste function messes up because of Custom Sort/Filter

**hoshinaru** · 06-05-2013, 09:45 PM

Hello!

First let me describe what I do with excel:

I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.

Here's the problem:

Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.

In case this isn't clear enough...

Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10

Once I filter/custom sort, I see rows 1,2,3,8,9,10

When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.

Any help is appreciated!

**FDibbins** · 06-06-2013, 12:06 AM

Hi and welcome to the forum

I just tried to emulate your problem with 20 000 rows, and the copy/paste worked just fine?

**hoshinaru** · 06-06-2013, 01:07 AM

Then I envy you

I get this error all the time, including when I try to transfer data from 2 separate excel files. I've gotten by this problem at times by alternating between cut/copy or shuffling between windows.

I took a zoomed out screen shot of the problem. This is immediately after I pasted. I took the liberty of adding the red box where the copy's dotted line is since it apparently disappears during print screen. You can see that the paste is shorter than the copy.

asdf.jpg

**FDibbins** · 06-06-2013, 01:13 AM

OK any chance of uploading a sample workbook? (no sensitive data)
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**hoshinaru** · 06-06-2013, 01:27 AM

Thanks for the quick responses!

I'm not authorized to release the actual excel file I'm having trouble with. But I did manage to reproduce the problem when I made a random spreadsheet.

**FDibbins** · 06-06-2013, 01:30 AM

THanks for the file.

What did you filter on?

**hoshinaru** · 06-06-2013, 02:06 AM

I'm not really sure what you mean by ON. I assumed the file would show everything already filtered.

I filtered row 1, and in the drop down, selected every other description (battery, monster, pencil, rock). Then I copied values in the Number column and copied it to column C. As you can see, the last bit of copied information never materialized.