Paste Data into Filtered Columns in Excel (Clever Tricks) 

Glad you are finding the videos helpful 🙂

Thanks Kuldeep... Hope the video is helpful 🙂

Thanks Satwant.. Glad you found the video helpful 🙂

Glad you found the video helpful 🙂

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

Glad you found the video helpful 🙂

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

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 RU-vid 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.

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

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

Glad the video helped 🙂

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

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

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

@@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

@@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

@@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 ?

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

Referring to % similarity of text string. Please check link for more m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7_C9lEUHM10.html&pp=ygUcU2ltaWxhcml0eSBmdW5jdGlvbiBpbiBleGNlbA%3D%3D

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

Will you please help with the VBA?

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

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.