Ad-Free CAclubindia and Daily Updates on your mobile. Subscribe Now!

Query on unique numbers in Excel sheet

Anil (1638 Points)

21 July 2009

There are lot of accounting code numbers mentioned in one coloumn of excel sheet. I want to be sure that each one of them is unique and that there is no repetion.

How to check it one go? is there any short cut to this?

Thanks,

Anil.

Reply
Follow
Share

5 Replies

Kuldip (Finance Manager) (31 Points)
Replied 28 July 2009

Hi Anil,

Please refer to the example below:

Sometimes you just don't want data to repeat itself. On an order form or an inventory sheet, for instance, you may not want a part number entered in one cell to repeat in another cell. You can use Excel's Data Validation feature to to prevent a value from appearing more than once in a range.

In the example below, the range A2:A20 requires unique part numbers. If the user enters a number that already exists, a message box pops up and asks for a different one.

To create this type of message box for your worksheet:

Select the cells for which you need to punch in unique entries (here, the correct range to select is A2:A20).
Choose Data, Validation and click the Settings tab.
Choose Custom from the Allow drop-down list. The Custom option requires a logical formula that will return either "True" or "False." This example requires a formula that will return "True" only if the content of the cell does not match one that already exists in the range. The COUNTIF function will do the job. Type the following formula into the Formula field:
```
=COUNTIF($A$2:$A$20,A2)=1 
```
This formula counts the number of cells in range A2:A20 that contain the same value that appears in cell A2. If the count is 1, the formula returns "True"; otherwise, it returns "False." Notice that the first argument for COUNTIF is an absolute reference to the entire validation range. In contrast, the second argument is a relative reference to the upper left cell in the validation range (it will be adjusted for each of the other cells in the range).
Next, to create the warning that appears in a pop-up message box when a duplicate value is entered into the selected range, click the Error Alert tab in the Data Validation dialog box. For the Style, select Stop (this option rejects existing values). Enter a title for the message box (such as Duplicate Data) and type your error message.
Click OK and try it out.

You'll find that you can enter any data into the validation range, but if you type an entry that already exists, you'll get the warning message. Click Retry to edit the cell's contents or choose Cancel to clear the cell.

While Data Validation is a useful feature, it contains a potentially serious design flaw. If you copy a cell and paste it to a cell that uses the feature, the Data Validation rules are wiped out. This problem also applies to cells that use Conditional Formatting. You'll need to keep this in mind when you're cutting and pasting in mission-critical applications.

Upgrade to CAclubindia Pro. Subscribe Now!

Anil (1638 Points)
Replied 28 July 2009

Hi,

Thanks for your message. The solution you have given is helpful if one is proceeding to enter data that has to be unique.

In my case the data is already entered. I want to make sure all the data entered are unique.

Thanks and regards,

Anil.

Ganesh Narayan.S (CA Student) (77 Points)
Replied 28 July 2009

Select the dtat which you want to make unique. Then follow : Data --> Filter --> Advanced Filter --> check unique records only

Alt + D + F + A +Alt R

Anil (1638 Points)
Replied 29 July 2009

Thanks

Anil.

Ashwin (Executive) (70 Points)
Replied 06 August 2009

Dear Anil,

There is one more option, you may use the Data-Sort option- and it will sort all the values. Now if you want to check wheather the numbers coming in the next cell is diff. from the upper one = you may use a very simple formula for example the data is in A colmn so in the cell B1 put the formula like =A1=A2 and you can copy this to the whole colmn. And check the results

Regards