Duplicate entries

[pommesmitmayo]

Hi all, hope well.

So I have had a look and seen Countif, orcountif etc etc and can't seem to see someone that has done this but at the same time as eluding me, it seems simple.
I have a simple sheet as attached that I want to use to manage job numbers.
The idea is that under one sales order there may be a number of jobs and each of these needs to be unique.

At the moment it's pretty manageable but as it gets longer I want employees to not have to search or filter to find out if a duplicate is present. At the moment I can only find solutions that refer to a duplicate of a value in a specific cell.

So in column B we have job numbers in cells B3 to Bx.
I'd like cell B1 to highlight if there is a duplicate anywhere in B3:Bx.

Can somebody point me in the right direction pls?

Book1.xlsx
	A	B	C	D	E	F
1
2	Sales Order	Job Number	Works date	Client
3	27764	1	04/09/2018	Lloyds
4		2	04/09/2018	Lloyds
5	27765	3	23/11/2018	Barclays
6	27766	4	17/05/2019	Halifax
7	27767	5	21/05/2019	HBOS
8	27768	6	15/07/2019	Nationwide
9	27769	7	20/11/2019	Natwest
10	27770	8	12/02/2020	HSBC
11	27771	9	01/06/2020	MBNA
12	27772	10	04/06/2020	Capital One
13	27773	11	07/07/2020	Lloyds
14	27774	12	24/11/2020	JP Morgan
15	27775	13	08/05/2021	Credit Suisse
16	27776	14	08/05/2021	BNP Paribas
17		15	07/07/2022	BNP Paribas
18	27777	16	08/07/2022	Halifax
19
20
21
22
Sheet1

That mini sheet Add in is BRILLIANT!

 

[zero269]

Hi Pomm...,

Considering you're concerned with identifying duplicates in a single column, using the default Conditional Formatting for Duplicates I think is a good first start.
You can select Column B > Conditional Formatting > Highlight Cell Rules > Duplicate Values...
Try that first to see if this is what you're looking for.

Book1
	A	B	C	D
1	Sales Order	Job Number	Works date	Client
2	27764	1	04/09/2018	Lloyds
3	27764	2	04/09/2018	Lloyds
4	27765	3	23/11/2018	Barclays
5	27766	4	17/05/2019	Halifax
6	27767	5	21/05/2019	HBOS
7	27768	6	15/07/2019	Nationwide
8	27769	7	20/11/2019	Natwest
9	27770	8	12/02/2020	HSBC
10	27771	9	01/06/2020	MBNA
11	27772	10	04/06/2020	Capital One
12	27773	11	07/07/2020	Lloyds
13	27774	12	24/11/2020	JP Morgan
14	27775	13	08/05/2021	Credit Suisse
15	27776	14	08/05/2021	BNP Paribas
16	27776	15	07/07/2022	BNP Paribas
17	27777	16	08/07/2022	Halifax
18		16
19		2
20	Total	16		16
Duplicates

Cell Formulas
Range	Formula
B20	B20	=SUBTOTAL(104,[Job Number])
D20	D20	=SUBTOTAL(103,[Client])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B19	Cell Value	duplicates	text	NO

I formatted the data into a Table and then used the Totals Row at the bottom to show the Maximum number in Column B so you'll know what the Next Number should be.

However, a better solution would be to get help with some VBA code that would automatically populate the next value for you when a new Table Row is inserted so you don't have to enter it manually. You'll want to start a new Thread for that if that interests you.

 

[pommesmitmayo]

Hi Pomm...,

Considering you're concerned with identifying duplicates in a single column, using the default Conditional Formatting for Duplicates I think is a good first start.
You can select Column B > Conditional Formatting > Highlight Cell Rules > Duplicate Values...
Try that first to see if this is what you're looking for.

Hi *zero269, thanks for coming back to me so quickly.

I'd looked at that but its not quite the answer. The reason is this;
Some jobs span months so although the job numbers are in numerical order now they won't always be. The same applies to invoice numbers and certain other numbers that aren't shown.
The way I'd like it to be, (I've used the View menu and frozen the top two rows), is that even if we've opened the workbook and are woking on row 2500 we'll still see the top row and therefore if we use any reference twice, we get a highlight in the top cell.
I believe the usual way for the method you suggested will give no indication if that particular cell is not on screen at the time?

 

[pommesmitmayo]

Hi *zero269, thanks for coming back to me so quickly.

I'd looked at that but its not quite the answer. The reason is this;
Some jobs span months so although the job numbers are in numerical order now they won't always be. The same applies to invoice numbers and certain other numbers that aren't shown.
The way I'd like it to be, (I've used the View menu and frozen the top two rows), is that even if we've opened the workbook and are woking on row 2500 we'll still see the top row and therefore if we use any reference twice, we get a highlight in the top cell.
I believe the usual way for the method you suggested will give no indication if that particular cell is not on screen at the time?

EDIT: Also, I;ll duplicate this functionality to other columns at some stage.

 

[zero269]

EDIT: Also, I;ll duplicate this functionality to other columns at some stage.

This might be a better option than the highlights...?

 

[pommesmitmayo]

This might be a better option than the highlights...?

Hi *zero269, thanks for coming back to me again.

So, that's annoying as I'd seen that solution previously. What I should have done is actually tried it/watched the whole video. In that example, the formula used is;
=COUNTIF($A$1:$A$15,A1)=1
The mistake/oversight on my part was assuming that the validation would only apply to the value in A1 but the guy says that teh formula is effectively 'dynamic', in that in copies down and applies to all cell references.
Apologies for wasting your time.