Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
9 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Cut and Paste Rows to another sheet based on duplicate value
Posted on 2013-01-04
Good Afternoon
I am looking for a macro which will compare the values in column B and if duplicates exist, cut each instance of that duplicate row and paste it in a new sheet. For example:
A B
Test 1
Test 1
Test 2
Test 3
Test 3
In this example the rows with 1 and 3 would be cut and pasted into a new sheet.
Thanks
I am looking for a macro which will compare the values in column B and if duplicates exist, cut each instance of that duplicate row and paste it in a new sheet. For example:
A B
Test 1
Test 1
Test 2
Test 3
Test 3
In this example the rows with 1 and 3 would be cut and pasted into a new sheet.
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
4
11 Comments
You can try something like this:
Sub Duplicates()
Dim x As Integer
Columns("A:B").Select
Range("B1").Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:B")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
x = 2
Do While Cells(x, 1).Value <> ""
If Cells(x, 2).Value = Cells(x - 1, 2).Value Then
Rows(x).Select
Selection.Cut
Sheets("NameOfSecondSheet").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("NameOfFirstSheet").Select
End If
x = x + 1
Loop
End Sub
Active 1 day ago
Hi
Is this what your looking for ?
Load the file and make sure your macroes are enabled and run the button Transfer Duplicates.
Let me know
gowflow
TransferDup.xls
Is this what your looking for ?
Load the file and make sure your macroes are enabled and run the button Transfer Duplicates.
Let me know
gowflow
TransferDup.xls
gowflow
Sorry for the delay in my reply, its been a busy weekend :(
Thank you for your reply's, I am sorry but I do not think I was clear enough, I require that all the rows with duplicates are copied, including the original row. i.e.
Test 1
Test 1
Both rows would be copied to the new sheet, not just one row.
Thank you
Sorry for the delay in my reply, its been a busy weekend :(
Thank you for your reply's, I am sorry but I do not think I was clear enough, I require that all the rows with duplicates are copied, including the original row. i.e.
Test 1
Test 1
Both rows would be copied to the new sheet, not just one row.
Thank you
gowflow
That's a lot closer, you seem to be checking both column A and B have matching duplicates?
Could the cut and paste just work on duplicates in column B, so from you example spreadsheet (which are great, thank you) I would expect the row:
do 6
To also be cut and pasted as its the 6 which is the duplicate.
Thanks
That's a lot closer, you seem to be checking both column A and B have matching duplicates?
Could the cut and paste just work on duplicates in column B, so from you example spreadsheet (which are great, thank you) I would expect the row:
do 6
To also be cut and pasted as its the 6 which is the duplicate.
Thanks
Active 1 day ago
Boy I was mislead and did not understand your post !!!
Chk this and let me know.
gowflow
TransferDup.xls
Chk this and let me know.
gowflow
TransferDup.xls
gowflow
Yes only duplicates in column B. Thanks for the example, this still leaves the following row:
go 6
on Sheet1?
Thanks
Yes only duplicates in column B. Thanks for the example, this still leaves the following row:
go 6
on Sheet1?
Thanks
Featured Post
In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 1 hour left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.