Help with sequential numbering

quickflip

New Member
Joined
Jul 27, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
ABCD
4VARIANTProd. IDSERIAL NUMBERID
5A1SA-1Unique ID
6B2SB-1Unique ID
7A3SA-2Unique ID
8A4SA-3Unique ID
9C5SC-1Unique ID
10C6SC-2Unique ID
11C7SC-3Unique ID
12C8SC-4NOT FOUND
13D9SD-1Unique ID
14A10SA-4Unique ID
15D11SD-2NOT FOUND
Sheet1
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to the Board!

Assuming that your data is in column A, and starts on row 2 (title row in row 1), then here is a formula for your Serial Number you can place in row 2 and copy down for all rows:
Excel Formula:
="S" & A2 & "-" & COUNTIF($A$2:$A2,$A2)

I am not sure I follow your second question.
Can you try explaining that again?
Is that "ID" column already populated, or do you need a formula for it?
 
Upvote 0
Hello there.

Thanks for the reply. Ill try the first solution you gave me

So the unique ID comes automatically from another worksheet as I said and I want to create a highlight conditional formatting rule that only highlights the unique ids that could be duplicate and leaves the duplicates not found on the side, without formatting.

Kind Regards.
 
Upvote 0
So the unique ID comes automatically from another worksheet as I said and I want to create a highlight conditional formatting rule that only highlights the unique ids that could be duplicate and leaves the duplicates not found on the side, without formatting.
I guess I do not understand how to determine which ones "may be duplicates".
Can you explain that logic a little more clearly?
And tell us in your example, which ones should be highlighted by Conditional Formatting?
 
Upvote 0
Also, the solution is not quite that, for the first question

More or less is like this that I want

The variant type has a data validation list with 4 variants (111111,222222...)

111.xlsx
ABCD
21111111SA-1Unique ID
32222222SB-1Unique ID
41111113SA-2Unique ID
51111114SA-3Unique ID
63333335SC-1Unique ID
73333336SC-2Unique ID
83333337SC-3Unique ID
93333338SC-4NOT FOUND
104444449SD-1Unique ID
1111111110SA-4Unique ID
1244444411SD-2NOT FOUND
Sheet1
 
Upvote 0
Sure I can explain.
Imagine that i insert on column B2 and B3 two diferent values. On D column, with a VLOOKUP, its going to give me those unique ID's for each cell. But i want to make sure that there is no duplicate id's that come from that vlookup so i want to highlight them. So if the vlookup value = NOT FOUND or ERROR, ignore the formatting rule. else, try to find the values that are duplicate on column D
 
Upvote 0
Also, the solution is not quite that, for the first question

More or less is like this that I want

The variant type has a data validation list with 4 variants (111111,222222...)

111.xlsx
ABCD
21111111SA-1Unique ID
32222222SB-1Unique ID
41111113SA-2Unique ID
51111114SA-3Unique ID
63333335SC-1Unique ID
73333336SC-2Unique ID
83333337SC-3Unique ID
93333338SC-4NOT FOUND
104444449SD-1Unique ID
1111111110SA-4Unique ID
1244444411SD-2NOT FOUND
Sheet1
That changes the questions quite a bit, and shows how important it is to accurately depict your examples, as it looked like the SERIAL NUMBER field used the VARIANT value. The fact that it does not changes things quite a bit.

How many possible different VARIANT values might you have?
If more than 26, what happens after you get to "SZ..."?
What would the 27th SERIAL NUMBER value look like?
 
Upvote 0
That changes the questions quite a bit, and shows how important it is to accurately depict your examples, as it looked like the SERIAL NUMBER field used the VARIANT value. The fact that it does not changes things quite a bit.

How many possible different VARIANT values might you have?
If more than 26, what happens after you get to "SZ..."?
What would the 27th SERIAL NUMBER value look like?
I have 4 variants (1111,2222,3333,4444) that i want to assemble at a assembly line. after that, i want to give them a serial number based on their status. The total number of variants (parts) would be a 1000 (1000 rows)

Kind regards
 
Upvote 0
Sure I can explain.
Imagine that i insert on column B2 and B3 two diferent values. On D column, with a VLOOKUP, its going to give me those unique ID's for each cell. But i want to make sure that there is no duplicate id's that come from that vlookup so i want to highlight them. So if the vlookup value = NOT FOUND or ERROR, ignore the formatting rule. else, try to find the values that are duplicate on column D
I am sorry, I am just not getting what you are saying.
Please try to walk through the data example you posted and explain for those particular records which ones should be highlighted and why.

I have 4 variants (1111,2222,3333,4444) that i want to assemble at a assembly line. after that, i want to give them a serial number based on their status. The total number of variants (parts) would be a 1000 (1000 rows)
OK, try this then:
Excel Formula:
="S" & LOOKUP(A2,{111111,222222,333333,444444},{"A","B","C","D"}) & "-" & COUNTIF($A$2:$A2,$A2)
 
Upvote 0
Hi there

I think that that might work, ill try it now

Regarding the other question, ill make an example

Kind regards
 

Attachments

  • aaaa.PNG
    aaaa.PNG
    46.6 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top