Looking for dups numbers in groups of numbers in single column

[JTL9161]

I have a column of numbers. I watered them down for this example below.

I am looking for a macro that will search EACH GROUP of numbers and find any dups. If there are none then go on to the next group and so on. I am not trying to find dup numbers in the entire column, just in each group.

After it finds a dupe I need it to note in a previous column.

The group size will be larger and vary in size but there should be at least 1 empty line between each grouping.

I've tried the macro's that look at the entire column but nothing seems to work for what I need.

Thanks for your help
James

	26
Dupe	34
Dupe	34
	42
	9
	15
	17
	22
	22
Dupe	2
Dupe	2
	13
	14
	49
	2
	6
	14
	21
	28
Dupe	1
Dupe	1
	8
	11
	24

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

 

[JTL9161]

Again Thanks for you help.

Getting back to it today. In my effort to simplify I only gave you 3 columns for the data. There is actually several columns in-between the data (duped numbers) and the letters. My letters are in column "B" and my possible duped numbers are in column "Z"

I tried tweaking your code above changing the Columns (3) to (25) thinking that is how many columns there are. Got the error "No Cells were found"

How would I tweak the above to compensate for the columns in-between using this setup

Letters (a,b,c..etc) are in Column "B" Possible Duped numbers are in column "Z" and I need the word "dupe" to show up in column "C"

Thanks again.
James

 

[Fluff]

Ok, try

Sub FindDupe()

    Dim ar As Areas
    Dim rng As Range
    Dim Cl As Range
    Dim Val As String
    
    Set ar = Columns(26).SpecialCells(xlConstants).Areas
    For Each rng In ar
        Val = ""
        For Each Cl In rng
            If WorksheetFunction.CountIf(rng, Cl) > 1 Then
                Cl.Offset(, -23).Value = "Dupe"
                If Val = "" Then
                    Val = Cl.Offset(, -24).Value
                Else
                    Cl.Offset(, -24).Value = Val
                End If
            End If
        Next Cl
    Next rng

End Sub

 

[JTL9161]

You are incredible! Almost there. Of course I didn't give you all the parameters so I'm back.

Within the A, B, C, D...etc there could be numbers (as text) 1. 99. 76. etc. After your code runs when it goes to copy the above line (column A) it does not copy it as text. Meaning:

A 889
B 876
99. 999
97. 999

Comes out like:

A 889
B 876
99. Dupe 999
99 Dupe 999

Any way to correct that? So the "." is also copied. Not a biggie but just want to keep everything the same till I manually edit it.

Also had this:
W 99
X 99
G 77
H 77

Come out like this:

W Dupe 99
W Dupe 99
W Dupe 77
W Dupe 77

when it should have came out like this
W Dupe 99
W Dupe 99
G Dupe 77
G Dupe 77

It needs to tell when a new set of dupes is found. Not sure how often this will happen but if its fixable that would be one less thing to worry about.


Thank you, thank you
James

 

[Fluff]

Give this a go

Sub FindDupe()

    Dim Ar As Areas
    Dim rng As Range
    Dim Cl As Range
    Dim Val As String
    
    Set Ar = Columns(26).SpecialCells(xlConstants).Areas
    For Each rng In Ar
        Val = ""
        For Each Cl In rng
            If WorksheetFunction.CountIf(rng, Cl) > 1 Then
                Cl.Offset(, -23).Value = "Dupe"
                If Val = "" Then
                    Val = Cl.Offset(, -24).Value
                Else
                    Cl.Offset(, -24).Value = "'" & Val
                    Val = ""
                End If
            End If
        Next Cl
    Next rng

End Sub

 

[JTL9161]

Great again! Just one more thing and I think you can get rid of me.

When there are 3 dups it doesn't copy the data in column "A" all the way down.

For example:


A 999
B 999
C 999
D 897
E 555

Comes out as:

A DUPE 999
A DUPE 999
C DUPE 999
D 897
E 555

In this case there is a third dupe so the A has to come down and overlay the "C". I don't think this will happen much, same a 4 dupes and the "D" would need to be an "A"


Thanks again,
James

 

[Fluff]

How about

Sub FindDupe()

    Dim Ar As Areas
    Dim Rng As Range
    Dim Cl As Range
    Dim Val As String
    
    Set Ar = Columns(26).SpecialCells(xlConstants).Areas
    For Each Rng In Ar
        Val = ""
        For Each Cl In Rng
            If WorksheetFunction.CountIf(Rng, Cl) > 1 Then
                Cl.Offset(, -23).Value = "Dupe"
                If Cl.Value = Cl.Offset(-1).Value Then
                    Cl.Offset(, -24).Value = "'" & Cl.Offset(-1, -24).Value
                End If
            End If
        Next Cl
    Next Rng

End Sub

 

[JTL9161]

Bingo!

I still need to try it on a much larger file but it should be good! I am only trying it on a 100 line spreadsheet now. I will check on a larger one.

While I still have you. Totally separate code.

Select 1 column only. (for example: "D")

Search for "0" (zero's) Match entire cell contents

When a "0" is found. Go 1 row up and copy that cell down 1 row.


So like this:

D1 5
D2 0
D3 16
D4 10
D5 11
D6 0
D7 20

I would need it to copy the "5" in cell D1 over the "0" in D2. Then search for the next "0" and copy the "11" in cell D5 over the "0" in D6. Then when there are no more "0" have a msgbox come up saying "no more zero's"


Thanks
James

 

[Fluff]

One option would be

Sub chk()
    With Range("D2", Range("D" & Rows.Count).End(xlUp))
        .Value = Evaluate("if(" & .Address & "=0," & .Offset(-1).Address & "," & .Address & ")")
    End With
    MsgBox "Done"
End Sub

But if you are likely to have 2 consecutive rows with a 0, this won't work.

 

[JTL9161]

Thanks. I will give it a try. Not as important as the DUPE one.

If you don't mind I will keep this link so if I have any other questions in the future I can contact you.


Thank you for your help.
James