how to copy paste in a filtered list

[zazzz]

I have a filtered list. There's 500 visible cells and about 500 filtered out.

I need to copy most of the visible cells in col U to col V. But I cant do so properly.

For example rows 15 and 19 are visible and in between they're filtered out. If I highlight from 15-19 and copy and try pasting into col V I only get one value there rather than 2.

I dont think a picture will help much but attaching just in case.

Can someone advise?

Thanks!
Dan


Excel Filter.PNG

[FlameRetired]

Try the F5 function key. Click Special then select Visible cells only. Then copy those.

[Rick Palmer]

This is the same as FlameRetired's answer. The only difference is using mouse clicks instead of pressing F5.
...
Select cells you want to copy > Find & Select > choose Visible cells only > copy then paste where you want it.
Note: Depending on what you are doing you might want to Paste Special and choose what you need (column widths, formatting, etc.).

[Rick Palmer]

Oops, duplicate post

[Rick Palmer]

Oops, duplicate post. Sorry.

[Rick Palmer]

Moderator, please delete duplicates. I was trying to edit to correct misspellings but my answer was posted multiple times instead.

[FlameRetired]

Moderator, please delete duplicates. I was trying to edit to correct misspellings but my answer was posted multiple times instead.

Rick,

You may not be aware of it, but you can edit the duplicates and replace the text of those posts with something like "Duplicate post". That's the only remedy I've found.

[zazzz]

Excel - Filter 2.PNG

Thanks a lot guys. I'm guessing you're very close to the answer as I also suspected its something to do with visible cells, but these solutions as is aren't working for me. I've attached a pic to make it clearer.

The filters are applied. I copied U913:943 and pasted them at Z1390 to see if just 2 values got pasted or 30. I was expecting that if I simply copied I'd get 30 values but if I copied only visible cells I'd get 2 values. That wasn't the case though. When I copied normally it only copied 2 values. So copying just visible cells doesnt seem to be the solution.

Any further help you guys can give?

Thanks!

[FlameRetired]

zazzz,

Many of us cannot view pics. I am one of them. Could you upload an Excel workbook with de-sensitized data? This way we can have a hands-on example to work with.

Thanks,
Dave

[zazzz]

zazzz,

Many of us cannot view pics. I am one of them. Could you upload an Excel workbook with de-sensitized data? This way we can have a hands-on example to work with.

Thanks,
Dave

OK sure, I've attached it.

Excel Filters - 2.xlsx

In col B if you filter by Boss, you'll see 48,54,68 highlighted. I'd like to copy these 3 into col C. If I try to do so only the first one goes over though.

If someone could advise I'd love that.

Thanks!

[newdoverman]

Select the cells that you want in the filtered list. Go to Find & Select, Go to Special, click on Visible Cells Only. Click on OK. Click on Copy. Go to the place that you want to paste these values and click on Paste. To keep the cells together, don't paste into rows involved in the filter. You say that you want to paste into column C so paste in column C under the filtered area.

[DebbieT69]

I'm having an issue doing something similar - I didn't know there was a problem copying with a filter, I appear to be able to do so no problem but....

the same (copied) values don't add up to the same amount as the total I've copied from..... no idea how this can be but I'm sure it's something obvious.

I have a master spreadsheet - just one big list of what's come out of our budget, Cost and VAT are totalled then the grand total is a sum of those two columns.

I've now been asked to create a "notional" budget sheet - each line on the master spreadsheet has been assigned a notional budget name - there are 6 in total. I've filtered to show each notional budget and copied this into a new sheet, one tab per notional budget and totalled each in the same way as the main budget spreadsheet. I've then added a formula to the front sheet of the new notional budget sheet and totalled the totals from each notional budget - should be the same total as the total from the big list on the main budget sheet, right?

It's about £172 out, the new sheet showing more than the original budget sheet.

I've done some quick calculations and double check there are no hidden lines etc, can't see where this has gone wrong.

Can someone please point out the stupidly simple mistake I've made please!

[newdoverman]

@DebbieT69

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

So, start a thread of your own and attach a file (not a picture) and mock up the result in the file. This will aid in helping you.

[DebbieT69]

ok, thanks

[FlameRetired]

zazzz,

If your upload is representative of the layout and challenge, and since the target cells for paste are in the next column this would be another way.

With your table filtered on "Boss" select cells B48:C68. Then hold down Ctrl while hitting R. If you are not aware of it this is a keyboard shortcut for fill / copy Right. Remove the filter from "Boss" to see if this has done what you want.

[newdoverman]

Create a formula that extracts the records according to the filter that you applied to the data. When I opened your file, the data was filtered to show records for "Boss" in column A. To get the values from column B that match Boss, enter this in column C and fill down:

Please Login or Register  to view this content.

The copy method outlined by FlameRetired works beautifully. Follow the directions exactly as he set them out.