How to assign unique number to column duplicates?

Hi All,
I need to assign a unique number to a set of duplicates all in one column in 
Excel 2007. 

so columnA will has about 9000 numbers, some of them unique, and others are 
duplicates of 2-4 approx. 

I used to conditional formatting to show which are duplicates, but need to 
be able to assign a unique number to each set duplicates, that will be in 
sequential order...

e.g.
ColumnA    ColumnB(unique ID)
01233         0001
01233         0001
01234         -
01255         0002
01255         0002
etc....

Any ideas please? I don't know how to do programming, just formulas in excel.
Thanks
Heather


0
Utf
12/23/2009 12:40:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
3156 Views

Similar Articles

[PageSpeed] 41

This should do it even if you have more than 2 dups. Must be SORTED

option explicit
Sub uniquenums()
Dim i As Long
Dim un As Long
Dim mc As Double
un = 1
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
mc = Application.CountIf(Columns(1), Cells(i, 1))
If mc > 1 And Cells(i - 1, 1) <> Cells(i, 1) Then
Cells(i, 2).Resize(mc) = un
un = un + 1
End If
Next i
Columns(2).NumberFormat = "0000"
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Heather" <Heather@discussions.microsoft.com> wrote in message 
news:3B2B8D54-27D0-40DD-962A-BD54B35B9496@microsoft.com...
> Hi All,
> I need to assign a unique number to a set of duplicates all in one column 
> in
> Excel 2007.
>
> so columnA will has about 9000 numbers, some of them unique, and others 
> are
> duplicates of 2-4 approx.
>
> I used to conditional formatting to show which are duplicates, but need to
> be able to assign a unique number to each set duplicates, that will be in
> sequential order...
>
> e.g.
> ColumnA    ColumnB(unique ID)
> 01233         0001
> 01233         0001
> 01234         -
> 01255         0002
> 01255         0002
> etc....
>
> Any ideas please? I don't know how to do programming, just formulas in 
> excel.
> Thanks
> Heather
>
> 

0
Don
12/23/2009 3:02:25 PM
Excel 2007 Table
Choice of six different formulas.
http://www.mediafire.com/file/wdhcztdyu3m/12_23_09.xlsx

0
Herbert
12/23/2009 5:01:04 PM
Hi Herbert,
thanks for that, it looks like exactly what I need. I've tried copying the 
formula into my table and then substituting your "NyNB" for my array, which I 
drag teh cursor over to select. Excel doesn't seem to like this!?

How did you select your "NyNB"?

Thanks
Heather


"Herbert Seidenberg" wrote:

> Excel 2007 Table
> Choice of six different formulas.
> http://www.mediafire.com/file/wdhcztdyu3m/12_23_09.xlsx
> 
> .
> 
0
Utf
1/4/2010 10:37:02 AM
Reply:

Similar Artilces:

Duplicate emails
Hi all just wondering why a user using outlook...send an email, then 8 mins later the recipient gets the same email again>?? Any ideas.... Cheers Very likely some intermediate SMTP server or Cisco firewall causing duplicates. Compare Internet headers (time in Received: ) of two successive duplicates. The first Received: with different times will preceed server, that is causing the problem. Andrew wrote: > Hi all just wondering why a user using outlook...send an email, then 8 mins > later the recipient gets the same email again>?? > > Any ideas.... > > Cheers Y...

columns in RMSSO
Hi There, Is it possible to change the columns on the forms ?? For example: Receiving po contains: Type, Item Lookup Code, Reorder Number, Description, OtyOrd, QtyRTD, QtyRcv, Cost, Price, NewPrice, Extended We need: Item Lookup Code, Description, MPQ, Price, BinLocation We need to modify few other forms as well. I think I've seen that in one of the add-ons while ago, but I cant remember which one was it and I'm not 100% sure if I really did. Thanks, Arthur ...

Deleting Non-Duplicate Rows
Have done tihs is the past, but can't remember how: Have a sheet with 9500+ rows. Column C contains a storage bin number. Want to delete all rows that DO NOT have a duplicate (trying to resolve items that have a duplicate bin number.) Have sorted the sheet on Column C. THX. . . -- BillW ------------------------------------------------------------------------ BillW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27516 View this thread: http://www.excelforum.com/showthread.php?threadid=470299 You could use a helper column of formulas: =countif(c:c,c1)...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Serial number error in sales order entry window (2nd post)
I'm reposting this because there was no response to my first post. I'm sure I'm not the only one that has had this problem. I noticed that one of my coworkers invoiced and added (overided) a serial number into the Great Plains instead of allocating one of the existing ones. How do I return this serial number w/out affecting my inventory? I need to: 1. Return the item w/the wrong serial number. 2. Reinvoice the correct serial number. 3. NOT HAVE THE WRONG SERIAL NUMBER STUCK IN MY INVENTORY. How do I do this? Hi Paoakalani Try looking at Serial Number edit, by Blue Moon...

Reference column question
Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is when I do my formula which is a subtraction for each column,I want to use a refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to each column how can I get to use f1 as reference for that column G1 for that column.Thanx If I understand correctly, try putting a $ in front of the row reference. Also, sum is not necessary for what you are doing. =F$1-D3 as you copy that down it becomes =F$1-D4 copied across to the ri...

Odd Looking Number
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I l...

How to make Unique coloumn in Excel sheet ?
I want to make one coloumn in excel sheet as unique, so no entry is repeated in that particular column. See http://www.contextures.com/xlDataVal07.html on this. HTH. Best wishes Harald "V JHANJI" <V JHANJI@discussions.microsoft.com> skrev i melding news:5D9465E8-EE69-4D59-8BD7-B0115D09145C@microsoft.com... > I want to make one coloumn in excel sheet as unique, so no entry is repeated > in that particular column. Hi check out http://www.cpearson.com/excel/NoDupEntry.htm for detailed instructions on how to do this. Cheers JulieD "V JHANJI" <V JHANJI...

Numbers turn red when equals to 0
Good day, Im monitoring our stock inventory using excel, i just want to know if I can make a certain cell turn red if my inventory is >5 or equal to zero.? Thanks -- shiela21cute ------------------------------------------------------------------------ shiela21cute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33169 View this thread: http://www.excelforum.com/showthread.php?threadid=529926 Check out Conditional formatting Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum...

All Columns are not to move
How can I stop the columns from moving to the left of the Table? -- Roger On Sun, 3 Feb 2008 17:28:39 -0800, Roger <Roger@discussions.microsoft.com> wrote: >How can I stop the columns from moving to the left of the Table? You'll have to explain what on Earth you're talking about, Roger. What columns are "moving"? How? John W. Vinson [MVP] Good evening John, I found my own answer:by bringing the cursor to the first cell, the first column will not scroll to the left anymore; however, when I bring the cursor to any other cell and scroll to the rig...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

Excel 2003 fuzzy search for duplicate.
Hello everyone, does anyone know of an application or vb code that can be used to search in two worksheets (like VLOOKUP) on similar matches using fuzzy logic? For example, if on one sheet I have I.B.M and the other IBM or James Smith on one and Jim Smith on the other, is there anyway of giving confidence codes to the match? Thanks very much for your help, Hi Asha, Look at soundex codes as explained by John Walkenbach http://www.j-walk.com/SS/excel/tips/tip77.htm You can use the SOUNDEX function as a worksheet formula, and then use VLOOKUP on that. In your example, both James Smith and ...

when I type a long number it shows up as smaller number and +
when converting from xls to csv format, some of my longer mortgage numbers get condensed into a smaller number with a letter, a plus sign and another number. example: 100020013120 turns into 1.0002E+11. Any way to get rid of this? Thanks, Yes, just specify the format that you want. Format Cells...>Number>0 decimal places Regards, Fred "SandyC" <SandyC@discussions.microsoft.com> wrote in message news:FA52E426-360E-4BE5-B63B-1209E673CA65@microsoft.com... > when converting from xls to csv format, some of my longer mortgage numbers > get conde...

Sort column with first and last name by last name
Hello, I have a mailing list with 10,000 names. The first cell has first an last name in the same cell. First name is listed first. Is there formula to sort and/or separate text in a cell. I would like to sort o separate the first name from the last in the cell to allow for a mai merge by name. Using Excel 2002. Thank you -- Message posted from http://www.ExcelForum.com If you have just first and last names separated by a space, Data>Text to Columns would be the easiest method to split into two columns. If more than that, like names with van or von or de etc. you may need a different m...

Rotating page numbers
I have a document with both page layouts (landscape and portrait) however the page numbers obviously will be pointing in the direction of the associated layout does anyone know how to be consistent in this? ...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr´┐Żm Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

deleting duplicate cells
I am back again... Thanks to everyone's help here last time, I was able to finish all my work and do it correctly. Ken- I asked last time i was here about deleting duplicate cells. Some of the names(address, etc) are repeated in my sheet. I want to delete the extra cell of the people who are in here twice. Not jus the cell but their record, name, address, city, state zip when they are in their twice so that they will only be listed once. You told me how to do this once but i cant find where it is on the board. Thanks for all the help.... BR4 -----------------------------------------...

How do I assign a macro I have created to a Command Button in Exc.
I believe on previous versions of excel you right-clicked on the command button and then clicked on assign marco, but this does not seem to appear in the 2003 version of excel. Any help is appreciated. If you add a button from the Forms toolbar, you'll see this option. I think you added a commandbutton from the control toolbox toolbar. Andrew7675 wrote: > > I believe on previous versions of excel you right-clicked on the command > button and then clicked on assign marco, but this does not seem to appear in > the 2003 version of excel. Any help is appreciated. -- D...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

How to assign value to controls on a Report
I am trying to assign value on a txtbox control from a form to a txtbox control on a report. Form txtbox control:txtBeginDateRange Report txtbox control:txtStartDate reportName = "Report by DR and Vendor" I am have a problem with this line of code Reports![reportName]!txtStartDate = Me.txtBeginDateRange DoCmd.OpenReport reportName, acViewPreview Any help will be appreciated. Thank you. Ayo. Answered in another post where you asked the same question. ...

Sequential numbering of invoices
I have recently downloaded an Excel template for invoices from Microsoft's website. Is there any way to fix it so that every time I create a new document based on this template, it automatically gives it an invoice number one greater than the previous invoice (eg, starting at 100 and then the next one I open would be 101, then 102 and so on)? http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Regards, Peo Sjoblom "Nick Xylas" <nickxylas@wmconnect.com> wrote in message news:1180551140.693535.260930@g4g2000hsf.googlegroups.com... >I have recently download...

Convert a number to a time
I have the following number 232.5 which is 232 hours and 0.5 of an hour. I want to convert this to 232 hours and 30 minutes or 232:30. How can i do this? I know i need to multiply the decimal by 60, but how can i perform a calculation on just the decimal part of a number and not the whole number? Depending what you wish to do excel stores times as well as dates as numbers, with 1 being a full day. An option is to divide everything by 24 and use a custom format of dd hh:mm although this will express the hours as days and hours It depends what you want to do really -- Dav --------------...

how do I change list order in color catagory assignment in office
only 15 color catagories can be displayed in color assignment in office outlook 2007. I wish to change the order of the list. I tried to number them 1-15, but it did not change the list. It did show 10-15 in the top of the list, and 1-9 on the bottom. I wish it to show them in order of 1-15, not necessarily with numbers, but with the color and catagory that I have assigne to them. any help would be appreciated THNX ...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...