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.
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
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
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
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.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Suggested Courses
Course of the Month11 days, 11 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.