CODEWITHSUNDEEP
excelvba
Wolfschmitt
Wolfschmitt

Reputation: 33

Excel macro for changing ID value's

For example:

I got this list of ID's (every number corresponds to a person) However these numbers for example 695678, 695683, 696217 don't go up by one. They are all ranked from low to high. Is there a way to automatically change these values to 1,2,3,... and so on by changing the lowest value to 1 and the second lowest value to 2 and so on. (can't figure out how to do it with macro's)

One note is that the IDs are repeated as these people made more then one transaction.

thanks!

Upvotes: 0

Views: 213

Answers (4)

David Vandenbroucke
David Vandenbroucke

Reputation: 114

@Gary's Student, your solution close to perfect, I've looked into this and added a small correction, Compiling it gives an runtime error, your K value is 1 and in your else clause it just takes the same value again.

After adjusting it to their excel file it worked perfect:

Sub Renumber()
Dim N As Long, I As Long, OldValue As Long
Dim K As Long
K = 1
N = Cells(Rows.Count, 2).End(xlUp).Row
OldValue = Cells(2, 2).Value
For I = 2 To N
    If Cells(I, 2).Value = OldValue Then
        Cells(I, 2).Value = K
    Else
        OldValue = Cells(**I + 1**, 2).Value
        K = K + 1
        Cells(I, 2) = K
    End If
Next I
End Sub

The I + 1 between ** is the part that bugged. I've also changed the cell indexes accordingly.

Kind regards,

David

Upvotes: 0

user2140261
user2140261

Reputation: 7993

Another formula incase your data ever needs to be unsorted, and should be faster then any Macros: 

=SUMPRODUCT( (FREQUENCY($A$1:$A$9, $A$1:$A$9) > 0) * (A1 >= $A$1:$A$10) )

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Please try: 

=IF(ISBLANK(A1),1,IF(A1=A2,B1,B1+1))

copied down to suit (with a blank in the top row of data).

To try to clarify, assumes data is in ColumnA but starting in A2 (A1 and B1 being blank), that the formula above is placed in B2 and copied down to suit.

First the test is whether A1 is blank (if True, returns 1 the start point - though this could be keyed into B2 and a simpler formula then used in B3 and so on). If A1 is not blank then there is a further test, whether or not the value has changed. If it has not (True), then use the value immediately above, if it has changed (False) use the value immediately above incremented by one.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

If you require a VBA solution, then:

Sub Renumber()
    Dim N As Long, I As Long, OldValue As Long
    Dim K As Long
    K = 1
    N = Cells(Rows.Count, "A").End(xlUp).Row
    OldValue = Cells(1, 1).Value
    For I = 1 To N
        If Cells(I, 1).Value = OldValue Then
            Cells(I, 1).Value = K
        Else
            OldValue = Cells(I, 1).Value
            K = K + 1
            Cells(I, 1) = K
        End If
    Next I
End Sub

Upvotes: 3

Related Questions