Assign a Value to a Duplicate Entry Within the Same Column

Within a single worksheet I have thousand-plus rows of data and one of the 
columns within that worksheet I have a series of numbers.  After I've 
identified the duplicates within that column I want to assign a value based 
on the first time that number shows up (in this case by I'v sorted by date), 
so the first duplicate would have a  value like "1."  I then want to assign 
the next duplicate(s) sequentially (2, 3, 4, etc.)  When finished I would 
want a new column of data that would have far more "1s" than "2s," more "2s" 
than "3s," etc.

How do I accomplish this?
0
Utf
10/21/2005 11:13:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
501 Views

Similar Articles

[PageSpeed] 26

Hi,

Supposing Column D has those numbers (say, D2:D2001, type in the following 
formula in E2 

=COUNTIF($D$2:$D$2001,D2)-COUNTIF(D2:$D$2001,D2)+1

and fill-in the formula down the column.

Regards,
B. R. Ramachandran


"Scott C." wrote:

> Within a single worksheet I have thousand-plus rows of data and one of the 
> columns within that worksheet I have a series of numbers.  After I've 
> identified the duplicates within that column I want to assign a value based 
> on the first time that number shows up (in this case by I'v sorted by date), 
> so the first duplicate would have a  value like "1."  I then want to assign 
> the next duplicate(s) sequentially (2, 3, 4, etc.)  When finished I would 
> want a new column of data that would have far more "1s" than "2s," more "2s" 
> than "3s," etc.
> 
> How do I accomplish this?
0
10/22/2005 12:51:04 AM
Hi,

Here is a simpler formula than the one I suggested in my previous reply (I 
am embarrassed about the earlier round-about formula!)

=COUNTIF($D$2:D2,D2)

Regards,
B. R. Ramachandran

"Scott C." wrote:

> Within a single worksheet I have thousand-plus rows of data and one of the 
> columns within that worksheet I have a series of numbers.  After I've 
> identified the duplicates within that column I want to assign a value based 
> on the first time that number shows up (in this case by I'v sorted by date), 
> so the first duplicate would have a  value like "1."  I then want to assign 
> the next duplicate(s) sequentially (2, 3, 4, etc.)  When finished I would 
> want a new column of data that would have far more "1s" than "2s," more "2s" 
> than "3s," etc.
> 
> How do I accomplish this?
0
10/22/2005 2:23:03 PM
Worked liked a charm!  Thanks B. R.Ramachandran!

"B. R.Ramachandran" wrote:

> Hi,
> 
> Here is a simpler formula than the one I suggested in my previous reply (I 
> am embarrassed about the earlier round-about formula!)
> 
> =COUNTIF($D$2:D2,D2)
> 
> Regards,
> B. R. Ramachandran
> 
> "Scott C." wrote:
> 
> > Within a single worksheet I have thousand-plus rows of data and one of the 
> > columns within that worksheet I have a series of numbers.  After I've 
> > identified the duplicates within that column I want to assign a value based 
> > on the first time that number shows up (in this case by I'v sorted by date), 
> > so the first duplicate would have a  value like "1."  I then want to assign 
> > the next duplicate(s) sequentially (2, 3, 4, etc.)  When finished I would 
> > want a new column of data that would have far more "1s" than "2s," more "2s" 
> > than "3s," etc.
> > 
> > How do I accomplish this?
0
ScottC (7)
10/24/2005 11:30:02 PM
Reply:

Similar Artilces:

Exchange / Outlook 2003 Public Folder Shared Calendar
We run an Exchange 2003 server with Outlook 2003 clients (via Citrix), on Windows 2003 Server. I have a bit of an odd problem with a public folder shared calendar I created for one of our offices. I created it in the normal way in Outlook, and applied appropriate permissions to all the users in that group. They can all see the calendar, and although they can add entries to it, they cannot see the appointments added by their colleagues - which pretty much negates the point of having it in the first place. I don't think it is an issue with the free / busy replication, as there is only one se...

Counting cells with a value in them
We are running an "If" equation on a column to determine if an event i taking too long to do. The resulting equation will yield an "X" if i is taking to long and a " " if not. I would like to have an equatio calculate the number of "X" in the column. Any ideas? Chris Nelso -- chris ----------------------------------------------------------------------- chrisn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=650 View this thread: http://www.excelforum.com/showthread.php?threadid=31880 We had a brain cramp and one of th...

BCP Computed Column
Hi. I have a table with a computed column. When I try to import data into table through BCP it generates error due to computed column. So how to handle a computed column while importing data into table through bcp from a text file. Regards, Muhammad Bilal You need to use a format file that tells BCP to skip that column. See BOL for info on bcp format files. Tom "Muhammad Bilal" <MuhammadBilal@discussions.microsoft.com> wrote in message news:5AD65421-C599-4773-B56F-24841FE05D5A@microsoft.com... > Hi. > > I have a table with a computed column...

data labels from second column
Hi Column A is list of names (Bob, Sue, etc); column B is how much they collected (58, 12, etc); Column C is the date they did it on - so record 1 says Bob collected 58 on 10/07/07, for instance. I want to create a chart with the date for the x axis, amount collected as the y axis and data labels at each point within the graph giving the collector's name. So at X=12/07/07, y=58 I want it to say Bob within the chart area. Any help much appreciated. Regards Chandler On Mon, 3 Sep 2007, in microsoft.public.excel.charting, Chandler <Chandler@discussions.microsoft.com> said: &...

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

Secondary Value within Bar
I am trying to set up a simple bar chart. Example: Total$ 2003, 2002, 2001. Within each of those bars I need to fill the bar up with another value. Example Total$ was 10000, but 5000 when towards another category. I need to show the total as 10000, but fill the bar up to 5000 with another design. Make sense? Pat - You could make a stacked bar (or column) chart. In these you do not plot the total, but all the constituents of the total. If you don't know them all, use 'Other' and use a formula to subtract all the known constituents from the total. So your chart would ha...

FUTA / SUTA Values are incorrect
I need to print FUTA reports but the numbers are incorrect. Does anyone know how to adjust the FUTA / SUTA values in Great Plains? If you're wanting to change the rates, go to Microsoft Dynamics GP>Tools>Setup>Payroll>Unemployment Tax. From here you can select the state(s) involved and change the values. -- Phil Camp Director of Information Hopkinsville Milling Company "Jay" wrote: > I need to print FUTA reports but the numbers are incorrect. Does > anyone know how to adjust the FUTA / SUTA values in Great Plains? > . > ...

Calculations not working on pasted values from Access
I'm using Office 2000 on W2k, and when I copy and paste a column of numbers from MS Access to MS Excel, I am unable to perform a Sum function on the pasted values. The function always returns zero. If I then type in the values directly it does work. Anyone know why? Is this caused by a virus and is there a patch? It is because it is seen as text, try to copy an empty cell, select the imported numbers, do edit>paste special and select add. -- Regards, Peo Sjoblom "Scott Sullivan" <anonymous@discussions.microsoft.com> wrote in message news:0c0001c3a22c$790cd250...

Locking a Cell after It's Value is Calculated.
I have One Cell whose value is constantly changing once a week. The results of that cell, I would like to have placed in differents cells, once a week. EX: In worksheet1 Input in cell WK3, resulting calculation ends in cell M3, then cell M3 is copied to worksheet2, A1 (=worksheet1M3) I got this! Input in cell WL3, resulting calculation ends in cell M3, then M3 is copied to worksheet2, A2: The problem is that cell A1 changes to the new number HELP! Can I lock cell A1 after the value is copied to it? Can I set conditions (time / date) on the cell after the value i copied to it so it won...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Return Value from cells which match criteria (complex)
I have a worksheet I want to compute mileage on. Right now we're doing everything manually, but I want to see if I can automate the process in Excel without resorting to a lot (if any) VB code. The formula needs to examine 4 columns for the start point, then 43 or so columns for the destination point. From there it can reference another sheet with the mileage between two points and insert the correct value. The logic looks something like this: "Look for data in array a and in array b then depending on which columns hold the data, lookup the corresponding result in ...

Contact List
How come whne I put in a contact in Outlook 2004, and then I add a fax #, it lists the contact twice on my main list? It's because there is no Outlook 2004. The standard behavior for the Outlook Address book is to display all electronic addresses (both fax and e-mail). That behavior cannot be changed. The only way to prevent the display of fax numbers is to store them in a different field or to disguise them (e.g., precede them with an alpha character) so that Outlook won't recognize them as phone numbers. There are also a number of utilities available that can do this for you automat...

populate a listbox with values from rows in another worksheet
How would I populate a list box with values from rows in anothe worksheet -- Message posted from http://www.ExcelForum.com Hi Dovrox, > How would I populate a list box with values from rows in another > worksheet. > Give the range with the values a name (insert, name, define) and use that name as the listsource property. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com You'll have to use a named range. Select the range and press Ctrl+F3 and give it a name like "mylist" (no quotes). Then under Properties of the Listbox use mylist for the ListFillRange. H...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

value of value of a variable.
I tried searching, but no use! I have a Const NameA = "BLA BLA" I have a variable NameB Value of NameB is NameA. How do i get the text "BLA BLA" from NameB variable Is there anyway to do that? something like,, VALUE(NameB) Thanks & Regards Joe Hi Dim NameB as String NameB =3D NameA in a cell you could have range("A1").Value =3D NameB would now have content "BLA BLA" regards Paul On May 21, 12:35=A0pm, Joe <joe.varghese.j...@gmail.com> wrote: > I tried searching, but no use! > > I have a Const Name...

T-SQL script to update A/P account value of Vendor Card
Hello: For some reason, I just noticed that a question that I posed over an hour ago still has not posted to the newsgroups. So, I'm going to try to repost, here. I am trying to develop a simple T-SQL script in Fabrikam where I update the A/P account of the vendor card with a different account number from what is presently there. When I try each of the scripts below, SQL returns 0 results. Why? UPDATE PM00200 SET PM00200.PMAPINDX = GL00100.ACTINDX from PM00200 as PM00200 join GL00100 as GL00100 on PM00200.PMAPINDX = GL00100.ACTINDX WHERE PMAPINDX IN( select ACT...

Linking text within Excel-- help!
I have a mailing list that I will be importing into Excel, and am trying to link the names on the list to invoices for those people on the list. Can I do this in Excel by using links? The Excel help feature only seems to describe links with figures (numbers), not text. e.g. Mr. Bill Jones, 123 Main Street, Middletown, OK 40404 (each word in its own cell in the mailing list worksheet) ....would link to a worksheet that had Bill Jones' name and address but also indicted that he purchased a $100 product on June 15 and his bill has been paid in full. My questions: 1. Each of the 300 or...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Matching and Copying entries
I have two worksheets, on sheet1 there are 4 columns, columnA is the customers account number. On sheet2 I have 5 columns and again columnA is the customers account number. I want to keep all records from sheet1, but I want to add two extra columns of data that is currently held on sheet2. So I think I need to compare the customers account numbers between sheet1 and sheet2 and where they match copy over columns C and E from sheet2 to two new colmns on sheet1. What would be the best eay to approach this problem. (I have approx 20,000 rows on sheet1 and nearly 50,000 on sheet2.) You say tha...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

multiple entries in outlook 2003
i have one user who has mulitple entries of users showing in his contacts window. I am not talking double entries (one must be a business fax), but more like 8 or 9 entries all the same. I exported his contacts to a csv file to see if they have slight differences but no they seem to be exactly the same. So far hea is deleting old ones, but they keep coming back. Now for the fun bit. It is not all users multiplying, just about 50% of them. it seems to happen every time he logs on he gets another replication. He also has the issue that if he creates a new contact in his contacts window it...

Time Entry
A simple problem I would appreciate your help with. I have a work sheet with several cells where time is to be entered in the format hh:mm. Unfortunately a lot of users are enetering it in the format hh.mm. Is there any way I can automatically convert to the right format, or not accept the (.) but only (:). Thanks for your help. Private Sub Worksheet_Change(ByVal Target As Range) If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then MsgBox "invalid entry" End If End Sub Enter the above code in the sheets module Mangesh -- mangesh_yadav -------------------...

Matching a List Containing Redundant Values
I have a set of numbers (auto mileage), located in a horizontal range named Rental_Totals. I have a corresponding set of addresses, located in a horizontal range named Rental_Addresses, matched to Rental_Totals. I need the two ranges automatically sorted vertically in ascending order of Rental_Totals. To do this, I located a post from Bob Phillips ( http://tinyurl.com/6k462 ) that almost* solves my problem, and I handled it like this: Under heading Auto Mileage, I drag this down a column starting at F4: = SMALL( Rental_Totals, ROW(A1) ) Under heading Rental Addresses, I drag this down a co...

How do I assign labels to scatter charts
Hi. I'm making an x-y scatter chart but can't assign labels to the individual points. How do I do this? Many thanks in advance for your help. Tom Hi, Here is an explanation of how to link a chart text element to a cell. http://www.andypope.info/tips/tip001.htm But if you have more than a couple of data labels to do you really need to use code. This free addin will do it for you. http://www.appspro.com/Utilities/ChartLabeler.htm Cheers Andy ThomasStudd wrote: > Hi. > > I'm making an x-y scatter chart but can't assign labels to the individual > points. H...