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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
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?
 

quickflip

New Member
Joined
Jul 27, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
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?
 

quickflip

New Member
Joined
Jul 27, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

quickflip

New Member
Joined
Jul 27, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

quickflip

New Member
Joined
Jul 27, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
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)
 

quickflip

New Member
Joined
Jul 27, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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: 6

Forum statistics

Threads
1,144,164
Messages
5,722,856
Members
422,461
Latest member
kelleys315

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