+ Reply to Thread
Results 1 to 4 of 4

how to put serial numbers to data rows

  1. #1
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    how to put serial numbers to data rows

    suppose i have a worksheet in which i have some data in col B with a header on B1.
    there are blank rows in between data.i want to give serial numbers to col A irespective of the fact that whether it has data on its right side or it is a blank one.

    i put 1 in col A2. i put =then i click on A2 and then input +1.
    then i press f5 and put A24 pressed the shift key and pressed enter. then i pressed control D.as a result , i get serial numbers from 1 to 24 in col A.

    (suppose there are only 10 data rows and others are blanks.)
    now i want to eliminate the blank rows keeping the serial numbers(in col A) of the data rows in place.

    now i enter =COUNTA(B2) in col C.i copy the formula to the last row of data.
    i get 1 against data rows and zero against blank rows.

    now i apply autofilter to col A1 select zero in col C. i get all blank rows together. i delete them.
    now i get the data without the blank rows. the problem i face is that
    the serial number in col A has disappeared. it says problem with the cell reference. now let me ask whether it is possible to do this task with the serial numbers in place( of course the numbers will be minus the blank rows,like 1,2,4,7,12..... i don't mind)

    -----------------------------
    A B C
    -----------------------------
    SL.NO TITLE
    -----------------------------
    1 abc 1
    2 wett 1
    3 0
    4 jkhghj 1
    5 0
    6 0
    7 rloljo 1
    8 0
    9 0
    10 fddde 1
    11 0
    12 rderu 1
    -------------------------------

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: how to put serial numbers to data rows

    Put this in A2 and copy down:

    =MAX(A$1:A1)+1

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: how to put serial numbers to data rows

    Hi Sumesh,

    If you need the sequence numbers in column A, if corresponding column in b is NOT empty, here is the one way.

    in A2, then copy dow.

    Please Login or Register  to view this content.
    If you are looking to just just returns the rows between them if column B is NOT empty,

    In A2, then copy down.

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,972

    Re: how to put serial numbers to data rows

    an easier way to do this is to use a formula.

    in A2, type in =IF(B2="","",COUNTA($B$2:B2))
    copy this down (either drag using the little black square at the botton right corner of the cell, of use ctrl C - copy, then highlight the range you want with your mouse, and press crtl V - paste)

    then highlight all the data in col A, press ctrl C (copy), right click, and select paste special and select "values" (this will convert the formula to its values

    sort your data on colA, then delete a;; the rows with nothing in them...you will be left with your data and the "serial" numbers

    Hope this helps, let me know how you make out
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1