Help with sequential numbering

[quickflip]

Hi there everyone

I'm new here and come here to find some help about a problem that seems easy to solve but i can't do it. It has 2 parts this problem

The first one is i need a solution to automatically fill in the serial number column (column C) sequentialy and acordding to column A (example given). I tried a solution with count ifs but didnt work as expected

The other one is a solution to the ID. I want to create a highlight formula that highlights only the Unique ID's values that are doubled on that column and makes an exception on the "NOT FOUND" ones (since there are a lot of them because they come from a VLOOKUP)

Kind Regards

111.xlsx
	A	B	C	D
4	VARIANT	Prod. ID	SERIAL NUMBER	ID
5	A	1	SA-1	Unique ID
6	B	2	SB-1	Unique ID
7	A	3	SA-2	Unique ID
8	A	4	SA-3	Unique ID
9	C	5	SC-1	Unique ID
10	C	6	SC-2	Unique ID
11	C	7	SC-3	Unique ID
12	C	8	SC-4	NOT FOUND
13	D	9	SD-1	Unique ID
14	A	10	SA-4	Unique ID
15	D	11	SD-2	NOT FOUND
Sheet1

 

[Joe4]

Hi there

I think that that might work, ill try it now

Regarding the other question, ill make an example

Kind regards

OK, I understand that you do not want to include any of the "NOT FOUND" records, but please explain why the first two records are highlighted, and all the other ones marked "Unique ID" are not.

Please walk me through the actual details of those examples explaining exactly why those particular ones are highlighted.

Remember, while this problem is very familiar to you, all that we know about it is what you share with us.
So we are depending on you to fill in all the details we need to know to help you come up with a solution that does what you want.

 

[quickflip]

Ok so I tried your solution with a twist and it's not work the way is intented. I only want to generate that serial if a cell has a value (OK), otherwise dont do that calc (value = NOK).

111.xlsx
	A	B	C	D	E	F
1	Data	VARIANT	Prod. ID	OK/NOK	SERIAL NUMBER	ID
2	12/01/2021	333333	1	NOK	NOK	Unique ID
3	12/01/2021	222222	2	OK	SB-1	Unique ID
4	12/01/2021	111111	3	OK	SA-1	Unique ID
5	12/01/2021	111111	4	NOK	NOK	Unique ID
6	12/01/2021	333333	5	OK	SC-2	Unique ID
7	12/01/2021	333333	6	OK	SC-3	Unique ID
8	12/01/2021	333333	7	OK	SC-4	Unique ID
9	13/01/2021	333333	8	OK	SC-5	NOT FOUND
10	13/01/2021	444444	9	OK	SD-1	Unique ID
11	13/01/2021	111111	10	OK	SA-3	Unique ID
12	13/01/2021	444444	11	OK	SD-2	NOT FOUND
13	13/01/2021	111111	11	OK	SA-4	NOT FOUND
14	13/01/2021		11	OK	#N/A	NOT FOUND
15	13/01/2021		11	OK	#N/A	NOT FOUND
16	13/01/2021		11	OK	#N/A	NOT FOUND
17	13/01/2021		11	OK	#N/A	NOT FOUND
18	13/01/2021		11	OK	#N/A	NOT FOUND
19	13/01/2021		11	OK	#N/A	NOT FOUND
20	13/01/2021		11	OK	#N/A	NOT FOUND
Sheet1

Cell Formulas
Range	Formula
E2:E20	E2	=IF(D2="OK",("S" & LOOKUP(B2,{111111;222222;333333;444444},{"A";"B";"C";"D"}) & "-" & COUNTIF($B$2:$B2,$B2)),"NOK")

 

[quickflip]

OK, I understand that you do not want to include any of the "NOT FOUND" records, but please explain why the first two records are highlighted, and all the other ones marked "Unique ID" are not.

Please walk me through the actual details of those examples explaining exactly why those particular ones are highlighted.

Remember, while this problem is very familiar to you, all that we know about it is what you share with us.
So we are depending on you to fill in all the details we need to know to help you come up with a solution that does what you want.

That was to exemplify that it found 2 equal unique ID's

 

[Joe4]

Ok so I tried your solution with a twist and it's not work the way is intented. I only want to generate that serial if a cell has a value (OK), otherwise dont do that calc (value = NOK).

I don't know how to help you if you keep introducing new details that you failed to mention in your previous posts.
It is very frustrating for us, feeling like we are caught in a classic "Catch 22" situation (every time we give you an answer, you change the criteria)!

When you post question here, you want to first think about it completely and post your questions clearly.
Do not try to "oversimplify" the question for the sake of posting it here.
Otherwise, you may get replies that correctly answer the question you posted here, but do not really work on your real data or situation (because you misrepresented it here).

 

[quickflip]

I don't know how to help you if you keep introducing new details that you failed to mention in your previous posts.
It is very frustrating for us, feeling like we are caught in a classic "Catch 22" situation (every time we give you an answer, you change the criteria)!

When you post question here, you want to first think about it completely and post your questions clearly.
Do not try to "oversimplify" the question for the sake of posting it here.
Otherwise, you may get replies that correctly answer the question you posted here, but do not really work on your real data or situation (because you misrepresented it here).

Hi there

Sorry to hear that from you

ill try to explain the best I can

I provided a sample that shows what i i'm trying to do

I want to create a button that runs a macro that does the following job
IF the item (displayed in this case with column B which only accepts 4 types (24043061,24043065,24043072 and 24043233) is Ready to Ship (OK), then create a customer serial number, being with that key in mind
24043061 - SA
24043065 - SB
24043072 - SC
24043233 - SD

Imagine the following scenario:
I create a part with variant 24043061 (named part A) that passes all those test and gets the OK to ship - gets the serial number SA-001
On the next row or some rows after that a part (named B) with variant 24043061 its not ready to ship. So, it will not have a serial number created to it.
On another row after another part (being part C) that is the same variant that the named part B (variant 24043061) gets the OK to ship. So i want to the serial number to it, keeping in mind that the last OK to ship part was the part A so this part C gets the serial number SA-002

Hope i have been clear enough

Kind regards