Difficulties with loops

[Minh92]

Hi,

I have two columns: one with IDs and one with the number of Unique ID (0 or 1). I would like to create a macro that checks all the IDs, find the duplicates and sum the number of Unique ID for these duplicates. If the sum is different thant 1, I want the Unique ID for the first duplicate to be 1 and for the others duplicates to be 0. If there are no duplicates, the unique ID for the ID should be 1.

For the moment the code will do the following:

APID Unique ID

ID 1 1
ID 1 1
ID 1 1
ID 1 0
ID 2 1
ID 3 1
ID 4 1
ID 4 1
ID 4 0

And I want it to do:

APID Unique ID

ID 1 1
ID 1 0
ID 1 0
ID 1 0
ID 2 1
ID 3 1
ID 4 1
ID 4 0
ID 4 0

Any clue? Thanks for your help

Sub findDuplicates()

Dim i As Integer
Dim j As Integer
Dim APID As Integer
Dim UniqueAP As Integer

Dim Lastrow
Lastrow = Sheets("Open APs").Cells(Rows.Count, 1).End(xlUp).Row
APID = 38
UniqueAP = 39

j = 2
For i = 5 To Lastrow
    If Sheets("Open APs").Cells(i, APID).Value = Sheets("Open APs").Cells(i + 1, APID).Value And Sheets("Open APs").Cells(i, UniqueAP).Value = 0 _
        And Sheets("Open APs").Cells(i + 1, UniqueAP).Value = 0 Then
        Sheets("Open APs").Cells(i, UniqueAP).Value = 1
    ElseIf Sheets("Open APs").Cells(i, APID).Value = Sheets("Open APs").Cells(i + 1, APID).Value And Sheets("Open APs").Cells(i, UniqueAP).Value = 1 _
        And Sheets("Open APs").Cells(i + 1, UniqueAP).Value = 1 Then
        Sheets("Open APs").Cells(i + 1, UniqueAP).Value = 0
    ElseIf Sheets("Open APs").Cells(i, APID).Value = Sheets("Open APs").Cells(i + 1, APID).Value And _
        Sheets("Open APs").Cells(i, APID).Value = Sheets("Open APs").Cells(i + j, APID).Value Then
        j = j + 1
        If Application.Sum(Range(Cells(i, UniqueAP), Cells(j, UniqueAP))) <> 1 Then
            Sheets("Open APs").Cells(i, UniqueAP).Value = 1
        End If
    End If
Next i

End Sub

[JasperD]

How about this:

Sub excelforum()
Dim lr&, x&, cell As Range

lr = Cells(Rows.Count, 38).End(xlUp).Row
Range(Cells(4, 38), Cells(lr, 38)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, Columns.Count), Unique:=True
Range(Cells(5, 39), Cells(lr, 39)).Value = 0
x = Cells(Rows.Count, Columns.Count).End(xlUp).Row

For Each cell In Range(Cells(2, Columns.Count), Cells(x, Columns.Count))
Cells(WorksheetFunction.Match(cell, Columns(4), 0), 39) = 1
Next cell

Columns(Columns.Count).Delete

End Sub

[Minh92]

Hi,

Thank you for your help but I don't really understand the logic behind this. I don't understand what is the use of x and columns.count, could explain in few words what is the logic?

Thanks a lot

[Minh92]

Basically, I want the first i value = 1 and the others i to equal 0 without getting out of my loop. I just don't know how I could write it in code...

[JasperD]

My code replaces your whole loop.

What it does is this:
* It finds all the unique values in your 'duplicate range' and puts them in a temporary column.
* It places a 0 next to all IDs
* It loops through the unique values and places a 1 next to each first value it finds

Should be what you're looking for and a lot quicker too.

Example
STEP 1: There's a bunch of IDs in your column 38 like below.
ID1
ID2
ID1
ID2
ID3
ID3
ID4
ID1
ID5
ID4

STEP 2: a list is made with all unique IDs in that column
ID1
ID2
ID3
ID4
ID5

STEP 3: a 0 is placed next to all IDs in your range
ID1 0
ID2 0
ID1 0
ID2 0
ID3 0
ID3 0
ID4 0
ID1 0
ID5 0
ID4 0

STEP 4: for each first occurrence of the ID in your range, the 0 is replaced by a 1
ID1 1
ID2 1
ID1 0
ID2 0
ID3 1
ID3 0
ID4 1
ID1 0
ID5 1
ID4 0

As far as I understood, that's what you wanted, correct?

[Minh92]

This is definitely a better and faster solution than mine. Thank for your explanation.

I am also highly interested in knowing why my solution did not work if you have a clue. This shows that I don't understand loopings and I need to improve my looping skills. Thanks