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.
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!