Pasting into filtered, visible cells only

nukeemaway

New Member
Joined
Jul 12, 2009
Messages
34
I have one file with thousands of cells. I filtered for unique cells. Now I want to take values from another worksheet, and paste those values so that they correspond only with the unique values. For example, if I have 1,000 unique numbers spread out amongst 20,000 cells, I have 1,000 corresponding values from a different worksheet that I want to paste next to those 1,000 filtered unique values.

2) The second thing I want to do, is then unfilter those cells, and then copy each of the pasted values throughout the 20,000 values. For example, if the number "1" in my filtered data corresponds to the value "500", I then want every "1" in the unfiltered data to have a value of "500".

Any suggestions, either internal excel, or via macro?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
For the first item, after executing the filter, you can select the filtered range of cells you'd want to copy, and from the keyboard press Alt+; (that's the Alt and semicolon keys) which will select only visible (hence filtered) cells. Then hit Ctrl+C and paste those selected visible copied cells where you like.

It's probably just me but I do not understand what your second item is meant to do. Consider providing an image or screen shot of your data for clarity of your question.
 
Upvote 0
Thank you, but that's actually not what I want to do. Let me try again with a smaller example.

I have 100 cells which contain the values 1, 2, and 3 in random order. I sort the cells so that all the 1's are together, all the 2's, and all the 3's. So now cells A1-A50 have "1" in them, A51-A75 have "2", and A76-A100 have "3". Now I filter, so that I'm left with A1 with "1", A51 with "2", and A76 with "3".

I have a different worksheet, with 3 values: 200 in B1, 300 in B2, and 400 in B3. I want to copy B1:B3 and paste it into my first worksheet so that in B1 next to the "1" in A1 it says 200; in B51 next to the "2" in A51 it says 300; and in B76 next to the "3" in A76 it says 400.

I then want to defilter so that all my original cells are visible, and have all the "1"'s get a corresponding value of 200, all the "2"'s get a corresponding value of 300, etc.

Back to my real life situation I would do it one by one, except that I am dealing with 2000 unique numbers and 20,000 total cells, so my part (2) is wondering whether there would then be some quick way to fill all the blank cells with the corresponding number (i.e. the 700 A cells with a value of "1" would then have a corresponding "200" in the B column). For example, have some macro run that would start from the top of the column and fill all blank cells with the number above them. That way, it would fill the first blank cell with 200, and continue filling in 200 until it reached the 300 (B51 in my example), at which point it would start filling all the blank cells below the first 300 until it hit the 400 (B76 in my example).

Hope that cleared it up. Thanks a lot.
 
Upvote 0
Hi Nuke, Tom is the nicest here in MRexcel. pls be more polite your responses. However re your question. you may try this

a simple vlookup from you list of possbile no with their corresponding values say 1 100 2 for 200 and so on

no need to filter. then just copy your formula all the way down and that wil have all your corrsponding values for all the nos. paste this as vaues is you want then sort by your column. let me know if this wont work.

Thanks
 
Upvote 0
Thank you. I'll try it just for future reference, but the project I needed it for was already due, and I just had to do it cell by cell.

Not to sound rude now, but what was impolite about my response to Mr. Urtis? I thanked him for his help and tried to clarify what I was asking?
 
Upvote 0
Ok, I see what you were saying to do. The problem is that I had 1500-1600 unique numbers and 19,000 cells, so I imagine it would have been just as time consuming to do 1600 vlookups over that many cells.

Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top