Custom functionality for a column

[GirishSharma]

We have an excel sheet (version 2003) in which I have to enter the data of our customers, visitors, consultants etc. We get these data in different format from many sources like emails, whatsapp chat, facebook comments etc. The data which we collects is as under :
name, age, country, mobile no, email id, xtype

Till now, I have entered around 150 rows and now it seems that duplicate data may be entered, because there is no fixed format, sequence and sources of raw data; so before entering the data I need to search the mobile number in whole sheet and if it found it puts me there in the cell and if not finds, I come to know that these data must be entered.

So rather than searching by mobile number in the column of mobile number, I wish to get the functionality of mobile number column of the last row of sheet, if I enters a number and if it is exists in the sheet, control should be at name column of the row, and if not finds, control should be at the name column of last row by storing entered number, so that I may know, this is new entry.

Kindly ask me, if I am unclear and/or need more explanation please.

 

[etaf]

is the mobile number always entered without spaces
you could use conditional formatting
so that the cell highlights if it finds a duplicate

using
countif(Mobile column, mobile column+1 ) ie if in column C =countif(C:C , C1)

 

[GirishSharma]

Mobile number is 10 char long value having all digits without any space and non numeric characters. Mobile number is just a name of column D in which I am storing the values for mobile numbers.
I am not getting what do you mean by your solution, kindly elaborate more.

Thanks.

 

[etaf]

if you setup conditional formatting to highlight any duplicate numbers
using
=countif(D:D, D1) >1
when you enter a number that is already duplicated it will colour the cell you are entering the number in and also the cell where the duplicate exists
very simple solution
however if you need the sheet to take you to the actual cell , you probably are going to need some VBA code, which i dont do

for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting


Highlight applicable range >>

$D:$D



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



=countif(D:D, D1) >1



Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[GirishSharma]

Thank you. I will try and let you know, if I sticks at any point, but my requirement may be fulfilled by VBA though.

Thanks again for good info.

 

[GirishSharma]

I tried it on a test worksheet but it is not showing me duplicates. In D column I entered 1,2,3,4 and when I entered 1 in D5, it showed highlighted 3 and now D7 is also highlighted, bit confusing working.

Kindly help me to highlight duplicates correctly.

 

[etaf]

Excel Workbook
	A	B	C	D
1				phone
2				1
3				2
4				3
5				4
6				5
7				3
8
9
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	1. / Formula is =COUNTIF(D:D,D1)>1	Abc

 

[GirishSharma]

Ok. I did not put "phone" word in D1, so may be this one is a reason, but it is still not a good solution for me, because what if there are more entries in the sheet and excel simply highlight with color, so I need to scroll up to see those duplicates (it is almost time consuming as search for the number), that's why I thought for a piece of VBA which will take me to the actual cell and if it not duplicate then I can find it a new entry, and now I have to enter rest data for this unique number.