?
Solved

Assign a number to each group of unique values in a column.

Posted on 2011-03-15
1
Medium Priority
?
677 Views
Last Modified: 2012-05-11
I have 43,504 rows of data.
Row 1 contains column headers.

I want Column A to have an "ID" number (starting with a value of "1" in A2 and ascending by "1") based on the value of Column B.

Start by comparing B2 to B3.
If B2 and B3 are the same, label A3 as "1".
If B2 and B3 are NOT the same, label A3 as "2" (=A2+1).
(then compare B3 to B4)
If B3 and B4 are the same, label A4 as "1" (=A3)
If B3 and B4 are NOT the same, label A4 as "2" (=A3+1)
(continue until a blank cell/row is reached.)

For example, the 2nd and 3rd rows of Column B contain "57827". I want these 2 rows of Column A to contain the value of "1".
The  4th row through 11th row of Column B contain "61034". I want these rows in Column A to contain "2".

ID-Value-Example.jpg
0
Comment
Question by:nicholasjwolf
1 Comment
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35142456
Place in A2:

   1

Place in A3 and copy down:

   =IF(B2=B3,A2,A2+1)

Kevin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question