Paste Data into Filtered Columns in Excel (Clever Tricks)

[@trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course

[@IvanCortinas_ES]

Excellent analysis. This happens more times than one can think. Thanks for sharing, Sumit.

[@MissSlickOne]

Thank you for ALL of the videos, I am learning so I can do more things at work like making schedules.

[@trumpexcel]

Glad you are finding the videos helpful 🙂

[@KuldeepSingh-nq1vi]

Nice video Bro.. That's what I had been looking for many days.. ❤

[@trumpexcel]

Thanks Kuldeep... Hope the video is helpful 🙂

[@mohammadalbizri2013]

Thank you for usual clever tricks.

[@trumpexcel]

Glad you found the video helpful 🙂

[@BeHealthyEnough]

Thank you, thank you, thank you the VLookup worked perfectly

[@trumpexcel]

Glad the video helped 🙂

[@BeHealthyEnough]

@ ended up having to go over to Google sheets it was so much easier. After I did the V look up and then I re-sorted the original spreadsheet it was wrong and I could not figure out why the values didn’t stay where they were supposed to stay Google sheets was so much easier but now I have to learn Google sheets

[@Karen-dh7nz]

Finally a working VBA code. You saved my day!

[@ReemAbdu-h7p]

Thank you!! This is the only video explaining the situation accurately.

[@Satwant2463]

Very important tips. Thanks a lot.

[@trumpexcel]

Thanks Satwant.. Glad you found the video helpful 🙂

[@VIPULDJHAVERI]

sumit ji , thanks a lot for sharing your brilliance in a very easy and lucid ways making all of us finer excel users ❤

[@trumpexcel]

Thank you so much for the kind words Vipul ji... Glad you found the video helpful 🙂

[@surjitkabla8191]

Thanks man you saved my couple of minutes in manual work. V lookup worked for me❤

[@fabianfernandez6723]

The Fill left option resolved my issue. Thanks!

[@vinodpareek8023]

Very Nice tips for pasting data in filtered column, I was struggling to do this but by your this video my problem is solved easily. Thanks for this

[@trumpexcel]

Glad the video helped 🙂

[@zenkaixzen]

Great video!

[@SivaSakthiRajagopal]

How you will copy and paste from another sheet?

[@weichun2011]

encountered the same issue lol

[@Aravinth_vlogs]

Me too same issue

[@lydethful]

Thanks so much. It has caused me headache for years.

[@rajeevgosavi4514]

Thank you Sumeetji, I really look forward each week for very useful excel tricks and tips. May I please request you to provide a link to download your spreadsheets.

[@trumpexcel]

Thank you Rajeev for the kind words...Have added a link of the download file in the description of the video

[@rajeevgosavi4514]

@@trumpexcel Thanks Sumitji for your kind response. Really appreciate.

[@fernandocaceres9930]

Thanks Sumit!!

[@sudhanshusrivastava1776]

Thank you so much Sir

[@fekadumulugeta1727]

Great video Thank you!

[@avishekdasgupta4262]

Excellent piee of code. I as looking for such a soultion for a very long time.

[@64mustangfan]

Great tips, some I know. I have this situation often, it's quite fiddly, why MSoft has not come up with a better solution like ctrl paste or something...?

[@serhadcizmecioglu2014]

Excellent, thank you...

[@meetkcs]

Good - Informative Content. - Keep up the Good Work !!

[@serdip]

Thank you for posting this informative video. I was able to import the bonus for each marketing employee using the name and also positionally (with the table that contains just bonus values) using Power Query: no formulas and no VBA. Would be great to see how you'd approach these problems using Power Query. Thank you kindly.

[@trumpexcel]

Yeah, can also be done using PQ, but seemed like an overkill unless you're already working with data in PQ. I have not tried it, but I assume it can be done using the name column as the key to combine two tables. And without names, it can be done using an Index column. I will give it a try. If you can share how you did it, that would good learning for all

[@serdip]

Thank you kindly. Since I started learning Power Query ~ 6 months ago, I have tried to use it in solutions that normally use formulas. Too bad KZbin doesn't accommodate posting images. I will try to describe the steps I took: 1) On the web page provided in the description, I did not see a link to download the file for practice. So I took a screenshot of the table with headers [Name], [Department], [Code], and [Bonus] (named "Employees"). I also took a screenshot of the table with headers [Name] and [Bonus] (named "Name and Bonus"). I also took a screenshot of the table with header [Bonus] (named "Bonus"). 2) I used Power Query to extract the data from each of these tables into Excel. 3) I loaded each of these tables into Power Query. 4) I changed the data type of each of the columns in each table to Whole Number or Text, as appropriate. 5) I performed a left outer join between Employees and Name and Bonus tables, on the [Name] field. I then expanded the linked table and selected the [Bonus] column, thus adding it to the Employees table for the Marketing employees. This was the FIRST report. 6) I then created a query named "Mktg_Emp_Indexes" by filtering Employees table on [Department] = "Marketing", with columns [Name], [Department], and [Index], starting at 0. 7) I then created a query named "Indexed_Employees" by performing a left outer join between Employees and Mktg_Emp_Indexes". After expanding, the Employees table now has an [Index] column populated with the indexes from Mktg_Emp_Indexes for the employees who work in the Marketing department. All other rows in the Employees table have null in the [Index] column. 8) I then added an [Index] column, starting at 0, to the Bonus table. 8) Finally, I performed a left outer join on the Indexed_Employees and the Bonus tables on the [Index] column, thus bringing in the values from the Bonus table into the Employees table. This was the SECOND report. 9) Both reports have the bonuses properly matched to the Marketing employees but the order of records in the first report somehow changed while performing the merge. Not sure if any of this is clear. It would be easier to show screenshots of the various queries. Thank you kindly.

[@nadermounir8228]

Thanks Summit. Quick question: in the VBA code: Set destinationcells = Destcells.Offset(1,0).Resize(destinationcells.rows.count) what confuses me here is that resize part because the destinstioncells number will be the same size as the original destionacells so you are increasing the number of rows of the destionacells isn't? Can u please clarify ?

[@trumpexcel]

Since I am using this in the For statement, it runs this statement everytime. So if I don't spcidy Resize(destinationcells.rows.count), it would Set destinationcells to one single cell (destCell.Offset(1, 0)) and the loop completes after 2 runs

[@nadermounir8228]

@@trumpexcel my concern is that Destinationcells will be larger this way and the loop may not end ?

[@trumpexcel]

@@nadermounir8228 Yeah, I see the issue. The destination range keep getting bigger, but it still works as I exit the For loop. Let me see if I can make the code better

[@trumpexcel]

@@nadermounir8228 Have a look at this one. It solves the issue of destination range shifting with each loop iteration Sub PasteintoFilteredColumn() Dim visibleSourceCells As Range Dim destinationCells As Range Dim initialDestinationLastRow As Long Dim sourceCell As Range Dim destCell As Range Set visibleSourceCells = Application.Selection.SpecialCells(xlCellTypeVisible) Set destinationCells = Application.InputBox("Please select the destination cells:", Type:=8) Application.ScreenUpdating = False initialDestinationLastRow = destinationCells.Rows(destinationCells.Rows.Count).Row For Each sourceCell In visibleSourceCells.Cells For Each destCell In destinationCells.Cells If destCell.EntireRow.Hidden = False Then sourceCell.Copy destCell.PasteSpecial Paste:=xlPasteValues If destCell.Row < initialDestinationLastRow Then Set destinationCells = destCell.Offset(1, 0).Resize(initialDestinationLastRow - destCell.Row) End If Exit For End If Next destCell Next sourceCell Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

[@nadermounir8228]

@@trumpexcel Thank you Sumnit. Now the pasting cells not increasing in size. Just one more thing : can we write this: IntialDestinationLastRow = Destination.Rows.Count.Row instead of Destination.Rows(Destination.rows.count).row ?? I am not very good in VBA but thought I would ask this ?

[@FightNightAI]

Brother your video saved me thank you

[@trumpexcel]

Glad the video helped 🙂

[@Sarah-s5v8h]

Thank you SO MUCH

[@martinfernandes5029]

Was looking for this for years ❤

[@ZONALEDUCATIONOFFICEPUTTALAM]

Very useful video. Thank you.

[@trumpexcel]

Glad you found the video helpful 🙂

[@yautja-]

Thanks a lot !

[@BlakeAbbott-s7u]

Thank you for the content. When copying data from another sheet or workbook using the VBA model, can you only copy one column at a time or can you do multiple? I followed the steps in the video but only the first column I had copied made it to my filtered data (I had 8 columns copied). Thank you for your help!

[@jeevanKumar-hm5ob]

Thanks for knowledge share 🎉 it worked

[@davidcocuzzi]

Great VBA code, I have tried it and it works for single columns. But can it be altered to paste multiple columns at once?

[@BlakeAbbott-s7u]

I am having the issue too. Were you able to find a solution?

[@davidcocuzzi]

@@BlakeAbbott-s7u not yet

[@JAAlamon]

How about if you need to copy paste data in different excel sheet ?

[@warwin9913]

what about copying values from cells that contain formula?

[@conniewymer9127]

JESUS CHRIST THANK YOU FOR THIS VIDEO DUDE. ❤❤

[@afriend8961]

I can't find an answer to this online! All my search criteria seems to be off! I'm trying to (simply) add new data (any data) to a filtered column so that the new data now appears in said column for future filtering. I can't seem to remember how to! Thanks!

[@afriend8961]

UPDATE! I literally just typed the data in the cell under the filtered column, and now see it becomes part of the filtered data! Lol. I did say 'simple'. Lol. Thanks!

[@knowndestiny]

Hi, @trumpexcel I Did the vlookup for the filtered cells but now i want to remove the formulae for vlookup as other rows have other formulae in my excel, how can i remove the vlookup, breaklinks will not work as the vlookup is from the same workbook..Any guidance will be helpful

[@AnjaneyOO7]

Could you please make it work for multiple columns data selection and paste into filtered columns through VBA

[@hunthunt2005]

great and thanks, I will try the google one.

[@edwinli2484]

excellent. thank you

[@anandkanke3745]

Awesome 👍

[@abidbn7098]

thank you

[@VanityChick]

How do you do this with different sheets because thats what I'm trying to do and its not working.

[@prathikshetty5905]

Thank You

[@vasudevansure3375]

When we delete the second sheet,Data gone from main sheet how can we solve this

[@Navneetsingh-ud2ru]

Hello Sir, Please help with similarity function.

[@trumpexcel]

I am not aware of any SIMILARITY function in Excel. Are you talking about partial match of fuzzy match?

[@Navneetsingh-ud2ru]

Referring to % similarity of text string. Please check link for more m.kzbin.info/www/bejne/bZCmap97iq2AZ5I&pp=ygUcU2ltaWxhcml0eSBmdW5jdGlvbiBpbiBleGNlbA%3D%3D

[@trumpexcel]

This is not an inbuilt function in Excel, and someone has created it using User defined function in VBA

[@Navneetsingh-ud2ru]

Will you please help with the VBA?

[@vdinesh8894]

Help ful😊

[@kevanodanendra5372]

Cool Mate!

[@nanireviewitall9270]

Thank you so much

[@vinhkopro]

thank you, bro

[@leyingmanlrtdoc8255]

Thanks

[@SudeshSivenarain-sf8ph]

Hi Sumith, really handy, can you share the code.

[@trumpexcel]

Hello Sudesh... You can get the VBA from here - trumpexcel.com/paste-into-filtered-column/#Using-VBA-works-in-all-scenarios

[@muhamedshahidpm8320]

❤❤❤❤

[@mugenjie]

Isn't this just a bug? What's the use case for copying only visible cells but then pasting into hidden cells, that justifies it being default behaviour? It just makes using the software that much more difficult and increases the likelihood of mistakes and data corruption. Requiring VBA for what should be simple copying and pasting? It should be the other way round, where VBA is required for copying visible cells and pasting into hidden cells in filtered ranges.

[@trumpexcel]

I agree... This should be the default option. Sadly it isn't, so we need to resort to these workarounds

[@PankajSharma55298]

😊😊

[@jewleprince1680]

Jesus loves you brother

[@phdsupervisor9512]

Just waste of time , please don't watch this video if you are beginner

[@nurhijjahsyazalinaabdulrah259]

my excel crashed TT

[@arisangelou908]

Thank you captain obvious

[@tilekt4248]

You didn't have to make a 10-minute video.

[@IvanCortinas_ES]

Excellent analysis. This happens more times than one can think. Thanks for sharing, Sumit.

[@trumpexcel]

Thanks Iván.. True, this is a pain point. I wish regular copy paste could work in this scenario. The work around are fine, but seems too much for something that should be easier.