Hi,
Column B contains alpha-numeric values I need a formula or macro which will move the duplicates, search and move complete row once duplicate value is found in column B.
Help.
Thanks.
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Hi,
Column B contains alpha-numeric values I need a formula or macro which will move the duplicates, search and move complete row once duplicate value is found in column B.
Help.
Thanks.
By duplicate, if b2="a" and b3="a: then b3 is the duplicate or both? I don't know what row means. All of a row or for columns 1-4 or? Copy where, new sheet?
If the former case, add a helper column. Then you can use a macro or Advanced filter. If the value is 0, it is the first one. Of course there are several ways that macros can do it.
e.g. B2:B5 has the values with uniques and duplicates. Copy and file down in helper column to number of
=COUNTIF(B$2:B$5,"=" & B4)-COUNTIF(B4:B$5,"=" & B4)
Hi,
Thanks for reply.
I was not clear in questioning lets say,
Col B---Col F(Helper)
123------3 displaying total 3 found.
123------3
123------3
789------0
456------2 displaying total 2 found.
456------2
300------0
Thanks in advance.
Post #3 is even more unclear than post #1. Obviously, your column F values did not use my formula.
The point you need to explain is what should be moved and to where. Cell B2, B2:E2, B3:B4, A3:E4, etc. Attach a file or show an image or mark in bold or explain words that defines a row to move.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 88, align: right"]123[/TD]
[TD="width: 88"] [/TD]
[TD="width: 88, align: right"]0[/TD]
[TD="width: 476"]<-=COUNTIF(B$2:B$8,"=" & B2)-COUNTIF(B2:B$8,"=" & B2)[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]0[/TD]
[/TABLE]
Sub MoveDupsAndKeepFirst()
Dim r As Range, c As Range, cn As Integer, lr As Long, col$
Dim wsU As Worksheet, wsD As Worksheet, rr As Range
Set wsU = Worksheets("Uniques")
Set wsD = Worksheets("Duplicates")
col = "B"
With wsU
Set rr = .UsedRange
Set r = .Range(col & 2, .Cells(Rows.Count, col).End(xlUp))
cn = rr.Columns.Count
lr = r.Rows.Count + 1
'H2=COUNTIF(B$2:B$8,"=" & B2)-COUNTIF(B2:B$8,"=" & B2)
.Cells(r.Row, cn + 1).Formula = "=COUNTIF(" & col & "$2:" & col & "$" & lr & "," & """" & "=" & """" & _
"&" & col & "2)-COUNTIF(" & col & "2:" & col & "$" & lr & "," & """" & "=" & """" & "&" & col & "2)"
.Cells(r.Row, cn + 1).Copy r.Offset(, cn - 1)
'Filter, copy, and delete filtered rows.
.UsedRange.AutoFilter cn + 1, ">0"
With Intersect(StripFirstRow(.AutoFilter.Range.SpecialCells(xlCellTypeVisible)), rr)
.Copy wsD.Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Delete xlUp
End With
.AutoFilterMode = False
Intersect(.UsedRange, .Columns(cn + 1)).Delete 'Delete helper column
End With
Application.CutCopyMode = False
End Sub
Function StripFirstRow(aRange As Range) As Range
Dim i As Long, j As Long, r As Range, z As Long, idx As Long
For i = 1 To aRange.Areas.Count
For j = 1 To aRange.Areas(i).Rows.Count
z = z + 1
If z = 1 Then GoTo NextJ
If r Is Nothing Then
Set r = aRange.Areas(i).Rows(j)
Else
Set r = Union(r, aRange.Areas(i).Rows(j))
End If
NextJ:
Next j
Next i
Set StripFirstRow = r
End Function
Display More
Hi,
Thank You & Thank You very much.
Solved.
Don’t have an account yet? Register yourself now and be a part of our community!