Number rows based on change in field

Posted on 2012-08-30
Last Modified: 2012-10-09
I have a query which is pulling records and I need to change the Client_id to an incrementing INT number when the value of a row changes.  Example, I have multplie contacts for a customer being returned and when the client_id (which is the same for all the contacts changes) I want to increment the value in a column by 1 like shown.

Dont care if this is done in SQL or EXCEL.  Just needed one time


NewID      Client_   ContaNumber         name
5000      00100000001rlgsAAA      1      CNet
5001      00100000001rlgtAAA      1      VeriSign, Inc.
5002      00100000001tre3AAA      1      21st Century Scientific
5002      00100000001tre3AAA      2      21st Century Scientific
5003      00100000001tre4AAA      1      3 V Co.
5003      00100000001tre4AAA      2      3 V Co.
5004      00100000001tre5AAA      1      3-K Machinery Company Inc.
5004      00100000001tre5AAA      2      3-K Machinery Company Inc.
Question by:red_75116
    LVL 22

    Assisted Solution

    Assuming NewID is column A and Cliebt in column B, in A3 paste this formula:


    Copy down as far as you need.

    LVL 4

    Accepted Solution

    Excel has a way of auto-incrementing data in a column when you select two or more cells that exhibit an incrementing pattern.  First select some cells, then grab the control node in the lower-right corner of the selection rectangle and drag it down.

    Here are some examples:
    LVL 39

    Assisted Solution

    what is the version of sqlserver you are using?
    if you are using sqlserver 2005 or above you can use "Row_number() over(Partition by fieldnames order by orderfieldnames)" function in your sql to generate auto increment numbers.
    post the table structure and indicate the fields on which you want to check for the change and the columns to use in ordering.

    Author Closing Comment

    thanks for the excel suggestions as that worked quick. I plan to try this with SQL next time it comes up.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now