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?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,504
Members
430,436
Latest member
fefenouil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top