How do I Add same sequence number for duplicates in the file

[demongyrl]

Add same sequence number for duplicates in the file


How do I add the same sequence number for duplicate files in excel 2007?

For example.. I have a list of 1400 orders. Some of them are duplicates and I can tell by their order number. I want to assign the duplicates the same sequential number so that I can sort out all the duplicates that are the same and move them to a new file.

Example of how i want it to look:

Customer Name BTN Order ID Seq Number
John Doe 2165551414 eftgr 1
Jane Doe 2165551413 eftgr 1

Thank you!!!!

 

[MickG]

Hi, A set of Before and After data would be very helpful!!!!
Regards Mick

 

[demongyrl]

The data columns look like this:

CustomerName Address City State OrderID PhNbr
John Doe 1515 Smith Street Hartford CT 54t67 2165551111
Jack Smith 1617 Allen Road Hartford CT 67b4 3305551212
Beth Hart 1444 Jack Road Hartford CT 89Z6 3305561717
Missy Doe 1345 Apple Road Hartford CT 54t67 4405451213

Now after the two that have the same order ID, I want both of them to have a 1 after them. And as I move down the list, I want sequential numbers to go after the ones that have the same order ID.
I know this is confusing.. does this make more sense? I have a list of orders. Some of them are duplicates, based on the order ID. I just want to not have to go through them manually and put sequential numbers by the duplicates. I figured there was a formula or a function I can do.
Let me know if you need more detail! (Or less..lol)

Thanks!

 

[MickG]

Hi, Try this:-
The code assumed your ID Numbers are in column "E" and the sequential numbers will be in column "G".
Please Note:- The easiest way to paste data in a Thread is to place a Border arouind each cell of the range you want to show, so The range appears as a Matrix of data, then copy & paste the range to the Thread.

[COLOR="Navy"]Sub[/COLOR] MG19Jan27
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("E1"), Range("E" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
.Add Dn.Value, Array(Dn.Offset(, 2).Address, 1)
[COLOR="Navy"]Else[/COLOR]
Q = .Item(Dn.Value)
    [COLOR="Navy"]If[/COLOR] Q(1) = 1 [COLOR="Navy"]Then[/COLOR]
        Range(Q(0)) = Q(1)
        Q(1) = Q(1) + 1
        Dn.Offset(, 2) = Q(1)
    [COLOR="Navy"]Else[/COLOR]
        Q(1) = Q(1) + 1
        Dn.Offset(, 2) = Q(1)
    [COLOR="Navy"]End[/COLOR] If
    .Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

NB:- Here is a Formula that should do the same:-
Place in "G1" & Drag Down

=IF(COUNTIF($E$1:$E$30,E1)>1,COUNTIF($E$1:$E$30,E1)-COUNTIF($E1:$E$30,E1)+1,"")

Regards Mick