Alert when entering duplicate number?

[hyewiz]

Hello;

I have a workbook that i keep track of our purchase orders, what I like to have is away that when entering the new Purchase order number to check the entire workbook and Alerts me when there is the same purchase order number entered before.

thank you

 

[Joneye]

I use this to find duplicates, any issues let me know. The 4 in Column C shows 1 if not duplicated and if greater its been repeated, note the 4? its been reapted 4 times, if twice then a 2 shows.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">numbers</td><td style=";">PO</td><td style=";">Count Duplicates</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;">400</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;">400</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">6</td><td style="text-align: right;;">400</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">14</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">17</td><td style="text-align: right;;">400</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">21</td><td style="text-align: right;;">21</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">22</td><td style="text-align: right;;">22</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C10</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B10</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B11</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C12</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C13</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B13</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C14</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C15</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B15</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C16</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B16</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C17</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B17</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C18</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B18</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C19</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B19</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C20</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B20</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C21</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B21</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C22</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B22</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C23</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B23</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C24</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B24</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C25</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C26</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$5:$B$26,B26</font>)</td></tr></tbody></table></td></tr></table><br />

 

[scottylad2]

Excel Workbook
	C	D	E	F	G	H	I	J
2			3489
3
4
5	25	3330	4986	2558	7990	5133	7996	4550
6	6958	7000	2594	5726	4614	3103	8486	7128
7	2684	9075	5518	2157	4028	9425	6719	6937
8	9010	3184	3803	2303	5645	6867	6588	6351
9	1225	9387	4603	5935	3429	8183	4122	7353
10	2512	7765	7708	2539	8875	5253	1963	3121
11	6591	3290	8905	5688	3093	6375	6804	4618
12	9164	3101	9920	6134	9696	4841	9978	2425
13	7134	6295	3893	4959	6959	4021	5459	3789
14	4546	9873	9491	5742	3489	9394	5820	9637
15	4540	5435	8454	5082	3356	5219	2929	3225
16	4338	4700	5658	9081	6028	8501	6656	4733
17	8006	5282	4849	6590	4854	2795	9515	5257
18	3512	7685	6730	1821	9876	4644	9632	7673
Sheet1

this formula in CF will work

Excel Workbook
	C	D	E	F	G	H	I	J
2			202
3
4
5	25	3330	4986	2558	7990	5133	7996	4550
6	6958	7000	2594	5726	4614	3103	8486	7128
7	2684	9075	5518	2157	4028	9425	6719	6937
8	9010	3184	3803	2303	5645	6867	6588	6351
9	1225	9387	4603	5935	3429	8183	4122	7353
10	2512	7765	7708	2539	8875	5253	1963	3121
11	6591	3290	8905	5688	3093	6375	6804	4618
12	9164	3101	9920	6134	9696	4841	9978	2425
13	7134	6295	3893	4959	6959	4021	5459	3789
14	4546	9873	9491	5742	3489	9394	5820	9637
15	4540	5435	8454	5082	3356	5219	2929	3225
16	4338	4700	5658	9081	6028	8501	6656	4733
17	8006	5282	4849	6590	4854	2795	9515	5257
18	3512	7685	6730	1821	9876	4644	9632	7673
Sheet1

=OR(E2=C5:J18) in Conditional Formatting and format as you like

 

[T. Valko]

Hello;

I have a workbook that i keep track of our purchase orders, what I like to have is away that when entering the new Purchase order number to check the entire workbook and Alerts me when there is the same purchase order number entered before.

thank you

You can use data validation to do this.

However, "check the entire workbook" is kind of ambiguous.

Can you narrow that down to a specific range on a specific sheet?

If you do mean literally the entire workbook then forget about trying to use data validation!

 

[hyewiz]

Maybe i explained it wrong this will be a new workbook, and i need the formula to Alert me while entering a new Data, its not that important to check the existing because this will be fresh new book,and for the entire workbook this
will fill up so fast that's why i mentioned entire workbook

 

[Joneye]

My formula will work, next to the PO detail have either a grouped or hidden column, then a cell of choose have a forumla that shows if that the hidden column is great that 1, A simple if formula will do this.

If you need me to post data I will.

 

[hyewiz]

My formula will work, next to the PO detail have either a grouped or hidden column, then a cell of choose have a forumla that shows if that the hidden column is great that 1, A simple if formula will do this.

If you need me to post data I will.

Please do so

Thanks

 

[cedricthecat]

Assuming your Product Code is in Column A, starting in A1, put the following into a Helper Column and drag down

=COUNTIF(A:A,A1)>1

Any matches/duplicates will return TRUE, otherwise it will be FALSE

If you format the cell in the Helper Column to white text and a coloured background, you'll only see anything in the Column when you get duplicates

 

[Kentman]

=COUNTIF(A:A,A1)>1

You can also put this in conditional formatting and copy it down to all blanck cells where the number will be entered and if someone enters a duplicate make it so that the cell turms red.

 

[cedricthecat]

You can also put this in conditional formatting and copy it down to all blanck cells where the number will be entered and if someone enters a duplicate make it so that the cell turms red.

Even neater!