Automatically increment a number when a duplicate occurs

jwcoleman

New Member
Joined
Sep 23, 2014
Messages
6
So I have a table, it looks kinda like this:

Code:
[TABLE="width: 1089"]
<tbody>[TR]
[TD]Serial Number[/TD]
[TD]Tote[/TD]
[TD]Date Assigned[/TD]
[TD]Assigned To[/TD]
[TD]Disposition[/TD]
[TD]Last Modified[/TD]
[TD]Actual Age[/TD]
[TD]Misdiagnosed[/TD]
[TD]Problem[/TD]
[/TR]
[TR]
[TD]CND8451VPS[/TD]
[TD]T33999[/TD]
[TD="align: right"]9/10/2014[/TD]
[TD]Parent[/TD]
[TD]OOW Approved[/TD]
[TD]Parent[/TD]
[TD="align: right"]23[/TD]
[TD]N[/TD]
[TD]Wrong Part[/TD]
[/TR]
[TR]
[TD]6CZ325B3HJ[/TD]
[TD]T48188[/TD]
[TD="align: right"]9/18/2014[/TD]
[TD]TITO[/TD]
[TD]Pre-Quoting[/TD]
[TD]TITO[/TD]
[TD="align: right"]23[/TD]
[TD]N[/TD]
[TD]Wrong Part[/TD]
[/TR]
[TR]
[TD]4E164097P[/TD]
[TD]T34964[/TD]
[TD="align: right"]9/10/2014[/TD]
[TD]Chris W[/TD]
[TD]Awaiting instal[/TD]
[TD]Chris W[/TD]
[TD="align: right"]21[/TD]
[TD]N[/TD]
[TD]DOA Part[/TD]
[/TR]
[TR]
[TD]CNU02639VQ[/TD]
[TD]T37959[/TD]
[TD="align: right"]9/15/2014[/TD]
[TD]Parent[/TD]
[TD]Unit Repairing[/TD]
[TD]Bowlin[/TD]
[TD="align: right"]20[/TD]
[TD]Y[/TD]
[TD]Triage[/TD]
[/TR]
[TR]
[TD]NXY3NAA003342228E43400[/TD]
[TD]T61882[/TD]
[TD="align: right"]9/10/2014[/TD]
[TD]Booth[/TD]
[TD]Awaiting instal[/TD]
[TD]Parent[/TD]
[TD="align: right"]17[/TD]
[TD]Y[/TD]
[TD]Root Cause[/TD]
[/TR]
[TR]
[TD]NXRN5AA00322705C623400[/TD]
[TD]T90975[/TD]
[TD="align: right"]9/23/2014[/TD]
[TD]CODY[/TD]
[TD]Pre-Quoting[/TD]
[TD]MIKE YI[/TD]
[TD="align: right"]17[/TD]
[TD]Y[/TD]
[TD]Triage[/TD]
[/TR]
[TR]
[TD]5CD2216JDF[/TD]
[TD]T58016[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD]MINA[/TD]
[TD]Awaiting instal[/TD]
[TD]TONY[/TD]
[TD="align: right"]16[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G7CV0X1[/TD]
[TD]T21462[/TD]
[TD="align: right"]9/23/2014[/TD]
[TD]BARRY[/TD]
[TD]Final Testing[/TD]
[TD]TYLER[/TD]
[TD="align: right"]16[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6CC3482Y5J[/TD]
[TD]T58477[/TD]
[TD="align: right"]9/23/2014[/TD]
[TD]CODY[/TD]
[TD]Final Tested[/TD]
[TD]CODY[/TD]
[TD="align: right"]16[/TD]
[TD]Y[/TD]
[TD]Triage[/TD]
[/TR]
[TR]
[TD]CND3521W0D[/TD]
[TD]T19073[/TD]
[TD="align: right"]9/23/2014[/TD]
[TD]RON[/TD]
[TD]Final Testing[/TD]
[TD]LUCAS[/TD]
[TD="align: right"]16[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCG7YC1[/TD]
[TD]T79640[/TD]
[TD="align: right"]9/9/2014[/TD]
[TD]Chris Fox[/TD]
[TD]Final Testing[/TD]
[TD]Chris Fox[/TD]
[TD="align: right"]14[/TD]
[TD]N[/TD]
[TD]BOM Issue[/TD]
[/TR]
[TR]
[TD]CCG7YC1[/TD]
[TD]T79640[/TD]
[TD="align: right"]9/23/2014[/TD]
[TD]TYLER[/TD]
[TD]Final Testing[/TD]
[TD]TYLER[/TD]
[TD="align: right"]14[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CNU00137FQ[/TD]
[TD]T80987[/TD]
[TD="align: right"]9/9/2014[/TD]
[TD]Parent[/TD]
[TD]Awaiting Parts[/TD]
[TD]Parent[/TD]
[TD="align: right"]13[/TD]
[TD]Y[/TD]
[TD]Triage[/TD]
[/TR]
[TR]
[TD]NXWZFAA00122115CF61601[/TD]
[TD]T91115[/TD]
[TD="align: right"]9/12/2014[/TD]
[TD]Booth[/TD]
[TD]Awaiting instal[/TD]
[TD]Mark[/TD]
[TD="align: right"]13[/TD]
[TD]Y[/TD]
[TD]Root Cause[/TD]
[/TR]
[TR]
[TD]NXMABAA0153360A0237601[/TD]
[TD]T91111[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD]RON[/TD]
[TD]Awaiting instal[/TD]
[TD]RON[/TD]
[TD="align: right"]13[/TD]
[TD]Y[/TD]
[TD]Triage[/TD]
[/TR]
[TR]
[TD]7D024198Q[/TD]
[TD]T60336[/TD]
[TD="align: right"]9/10/2014[/TD]
[TD]BEN[/TD]
[TD]Awaiting instal[/TD]
[TD]Tyler[/TD]
[TD="align: right"]12[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7D024198Q[/TD]
[TD]T60336[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD]TYLER[/TD]
[TD]Awaiting instal[/TD]
[TD]TYLER[/TD]
[TD="align: right"]12[/TD]
[TD]Y[/TD]
[TD]Root Cause[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

I have considered a problem I may have in the future. Serial numbers that are sent to the awaiting parts status will eventually be brought back into the system and tracked for a second repair cycle. The point of this spreadsheet is to track my awaiting install units and their misdiagnoses status.

I understand that my key for this table will need to be composite, It would include serial number, the assigned technician, and finally something else. One thing to understand is taht a unit may be prequoted and put back into the repair cycle on the same day, so date is not a valid option for a third part of the composite key.

What I want to do is keep track of unit cycles, when I scan in serial numbers, i'd like it to check for duplicates, and if so, increment the cycle, starting at one, and onwards, for how many times the unit went to and from a hold status.

What are some options I have for doing this? Which paths should I research in order to accomplish my goal?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jwcoleman

New Member
Joined
Sep 23, 2014
Messages
6
To clarify, my goal is to number the duplicate records of a serial number 1-x where x is the most recent occurrence of the serial number and 1 is the first occurrence, based off of the date in the date assigned field. I understand index and match, i think I get vlookup. What I don't really understand is how to number the occurrences of the serial numbers based off of their date.

What im really getting at, is the answer to whether or not this can be done relatively easy without the use of vba macros
 

mohan.pandey87

Board Regular
Joined
Sep 14, 2012
Messages
146
If you want the most recent occurence or the first occurence that we can do it using formuls where the value you are looking for is in cell $K$1

Apply this using Ctrl + Shift + Enter

First Occurence:-
Code:
=INDEX($A$1:$I$18,SMALL(IF(--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)=0,"",--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)),1),1)

Recent Occurence:-
Code:
=INDEX($A$1:$I$18,LARGE(IF(--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)=0,"",--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)),1),1)
 

jwcoleman

New Member
Joined
Sep 23, 2014
Messages
6
I'm excited to work out the formula you gave me, and see what I can accomplish with it. My only remaining question would be how to deal with the occurrence of three or more serial numbers? As much as I'd like to say this doesn't happen it inevitably will D:

I can see from what you posted it's not too difficult to get the oldest, or the newest, but what about those nasty in betweens.....

Hmm....
 

jwcoleman

New Member
Joined
Sep 23, 2014
Messages
6

ADVERTISEMENT

If you want the most recent occurence or the first occurence that we can do it using formuls where the value you are looking for is in cell $K$1

Apply this using Ctrl + Shift + Enter

First Occurence:-
Code:
=INDEX($A$1:$I$18,SMALL(IF(--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)=0,"",--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)),1),1)

Recent Occurence:-
Code:
=INDEX($A$1:$I$18,LARGE(IF(--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)=0,"",--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)),1),1)

My brain is stirring.

Theoretically these are the only two formulas I need. I can check to see if recent occurrence matches with the date of an occurrence I just added, if not, then the cycle number will be matched with the former recent occurrence, and have 1 added to it, to increment the cycle...

I never really my excel spreadsheets would get as complicated as my C++ code, but this is starting to touch that level. Now granted this solution does not account for back data, but it would account for all future data.
 

jwcoleman

New Member
Joined
Sep 23, 2014
Messages
6
If you want the most recent occurence or the first occurence that we can do it using formuls where the value you are looking for is in cell $K$1

Apply this using Ctrl + Shift + Enter

First Occurence:-
Code:
=INDEX($A$1:$I$18,SMALL(IF(--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)=0,"",--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)),1),1)

Recent Occurence:-
Code:
=INDEX($A$1:$I$18,LARGE(IF(--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)=0,"",--($A$1:$A$18 = $K$1)*ROW($A$1:$A$18)),1),1)

My question to you sir is what you are trying to represent with K?
 

jwcoleman

New Member
Joined
Sep 23, 2014
Messages
6
I think I've found the sum of my problem, (spreadsheet joke)

Code:
=COUNTIF(A$1:[@[Serial Number]],[@[Serial Number]])

All I've done differently is arrange the serial numbers by date. This works because there is no backdating, and if there was I could just manually edit it. So now I have a cycle count of each individual serial number.

Way simple. thanks for your response! Vector formulas are way beyond my grasp at this point D:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,351
Messages
5,528,192
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top