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
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.
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.
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 CZcams 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 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.
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!
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 ?
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 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)
@@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 ?
Referring to % similarity of text string. Please check link for more m.czcams.com/video/7_C9lEUHM10/video.html&pp=ygUcU2ltaWxhcml0eSBmdW5jdGlvbiBpbiBleGNlbA%3D%3D
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
Excellent analysis. This happens more times than one can think. Thanks for sharing, Sumit.
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.
Thanks man you saved my couple of minutes in manual work. V lookup worked for me❤
Thank you for ALL of the videos, I am learning so I can do more things at work like making schedules.
Glad you are finding the videos helpful 🙂
Nice video Bro.. That's what I had been looking for many days.. ❤
Thanks Kuldeep... Hope the video is helpful 🙂
sumit ji , thanks a lot for sharing your brilliance in a very easy and lucid ways making all of us finer excel users ❤
Thank you so much for the kind words Vipul ji... Glad you found the video helpful 🙂
How you will copy and paste from another sheet?
encountered the same issue lol
Thanks so much. It has caused me headache for years.
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.
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 CZcams 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 for usual clever tricks.
Glad you found the video helpful 🙂
Very important tips. Thanks a lot.
Thanks Satwant.. Glad you found the video helpful 🙂
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.
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.
Thanks for knowledge share 🎉 it worked
Was looking for this for years ❤
Thank you so much Sir
Thanks Sumit!!
Very useful video. Thank you.
Glad you found the video helpful 🙂
Brother your video saved me thank you
Glad the video helped 🙂
Great video!
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!
excellent. thank you
thank you, bro
Help ful😊
Good - Informative Content. - Keep up the Good Work !!
Could you please make it work for multiple columns data selection and paste into filtered columns through VBA
great and thanks, I will try the google one.
Great VBA code, I have tried it and it works for single columns. But can it be altered to paste multiple columns at once?
I am having the issue too. Were you able to find a solution?
Thank you so much
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 ?
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 ?
How do you do this with different sheets because thats what I'm trying to do and its not working.
How about if you need to copy paste data in different excel sheet ?
how i can only link Filtered Data (Visible Cells)in excel
❤❤❤❤
😊😊
Hello Sir,
Please help with similarity function.
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.czcams.com/video/7_C9lEUHM10/video.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?
Hi Sumith, really handy, can you share the code.
Hello Sudesh... You can get the VBA from here - trumpexcel.com/paste-into-filtered-column/#Using-VBA-works-in-all-scenarios
Just waste of time , please don't watch this video if you are beginner
Excellent analysis. This happens more times than one can think. Thanks for sharing, Sumit.