Leading Zeros To Complete 3 Digit Number

[jgthtrotih]

Hey All

i need a little help with leading zeros in excel i want a complete 3 digit number as shown blow (first chart) i used the " ' " mark to get the zeros in..i want the second chart to look the same with the zeros...

Chart 1: Examples and what I'm trying to get Achieve.
Chart 2: My actual data set.

Excel Workbook
	E	F
28	RANDBETWEEN("000",999)
29
30
31	356	356
32	454	454
33	48	048
34	367	367
35	785	785
36	46	046
37	856	856
38	986	986
39	46	046
40	85	085
41	8	008
Sheet

Excel Workbook
	E	F	G	H	I	J	K	L	M
7	8		401		934		8-401-934
8
9	450		587		16		450-587-16
Sheet14

Have Any Questions Let Me Know!

Using Excel 2003!

Thanks!

 

[jgthtrotih]

Sandeep,

Yes, order does matter..duplication is only counted when the exact same sequence of numbers is entered ie 454-536-636 and 454-536-636 ARE to be counted as duplicates.. 536-454-636 is NOT to be counted a duplicate if entered and thus gets entered into the list as a new number.

Thanks!

 

[Sandeep Warrier]

I think something like this should work. Place this code in a worksheet specific module.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> ConcatVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    ConcatVal = Range("E31").Value & "-" & Range("G31").Value & "-" & Range("I31").Value<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Range("K31").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("K31").Value = ConcatVal<br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Rng = Range("K31", Range("K" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        X = WorksheetFunction.Match(ConcatVal, Rng, 0)<br>        <SPAN style="color:#00007F">If</SPAN> Err.Number > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Range("K" & Rows.Count).End(xlUp).Offset(1).Value = ConcatVal<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jgthtrotih]

Sandeep,

You NEVER cease to amaze me!! once again you have conquered the challenge!!..if i may make one addition..to only calculate up to 100 rows after that i want a message to say "limit Reached"..it is not necessary..just for convenience.

Thanks!

 

[Sandeep Warrier]

This should do the job

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> ConcatVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    ConcatVal = Range("E31").Value & "-" & Range("G31").Value & "-" & Range("I31").Value<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Range("K31").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("K31").Value = ConcatVal<br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Range("K" & Rows.Count).End(xlUp).Row > 130 <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox ("Limit Reached")<br>            <SPAN style="color:#00007F">GoTo</SPAN> 1234<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Rng = Range("K31", Range("K" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        X = WorksheetFunction.Match(ConcatVal, Rng, 0)<br>        <SPAN style="color:#00007F">If</SPAN> Err.Number > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Range("K" & Rows.Count).End(xlUp).Offset(1).Value = ConcatVal<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>1234:<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jgthtrotih]

Sandeep,

Nicely Done! Works Perfectly!

Thanks!