How to create serial numbers

Solved/Closed
arun - Updated on Nov 25, 2017 at 02:13 AM
 Reham - Nov 24, 2017 at 06:33 AM
Hello,

I have an excel format shows different values of schedule of correspondence and some of the mails are gone to different addresses and the mail ref. colum shows only once. I want to count the mails serially. how to create exact serial numbers?

I want to skip the rows that have no values i.e. ref of the mails.

Example :

04/02/2010 C. Fox Al-Futtaim Carillion M. LEWIS

04/02/2010 A. Simp Al-Futtaim Carillion I. AL-QUDAH
M. LEWIS
04/02/2010 R. Pal Al-Futtaim Carillion A. Kumar
M. LEWIS

Please provide a formula.

Regards,

ARUN

6 replies

Friends,You Can Use ROW() function for generating serial numbers,
for example,

in A1 cell write

=ROW() and press Enter,that will give 1 as answer.

Now click on A1(i.e. Select it)-> Press Ctrl+Shift+Down(to select the below fields which will get its serial now..) -> After selecting the column Goto--EDIT->FILL->DOWN

This will fill all the cells with the respective ROW number...

U can also use arithmetics with ROW(),if any such requirements are there.

This method is for generating manually.
For automatic ID generation,you can refer to MACROs in excel.

Do put Thanks,if this was helpful because I tried for the whole day to find this....See you..
17
tenks
0
Thanks a lot. Really helpful.
0
thank you so much
0
thank you
0
Hi,

I have an excel worksheet contains various rows and I want to make a serial number automatically how to get it through a formula.

few rows contains values and others dont.

Regards,

ARUN
5
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 15, 2010 at 07:25 AM
Assumptions
1. You want series to appear in Column A
2. If a row has a data, then column B of that row would have data
3. Row 1 is a header

Write in Cell A2

=IF($B2="","",COUNTA($B$2:$B2))


Drag this formula down
0
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 5, 2010 at 10:02 AM
Dear rizvisa1,

how to edit this formula if I want the serial no start with JNP folow by no.

Thanks
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 5, 2010 at 10:04 AM
To add JNP and have the series


=IF($B2="","", "JNP " & COUNTA($B$2:$B2))
0
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 5, 2010 at 10:08 AM
Thank alot
0
Friends,You Can Use ROW() function for generating serial numbers,
for example,

in A1 cell write

=ROW() and press Enter,that will give 1 as answer.

Now click on A1(i.e. Select it)-> Press Ctrl+Shift+Down(to select the below fields which will get its serial now..) -> After selecting the column Goto--EDIT->FILL->DOWN

This will fill all the cells with the respective ROW number...

U can also use arithmetics with ROW(),if any such requirements are there.

This method is for generating manually.
For automatic ID generation,you can refer to MACROs in excel.

Do put Thanks,if this was helpful because I tried for the whole day to find this....See you..
4
Thank you very much.. I got it,
0
And for BLANK Rows U can use if condition accordingly...
2

Didn't find the answer you are looking for?

Ask a question
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 14, 2010 at 08:20 AM
Not sure what you are looking for. The rows number have series. If you want to additionally show numbers as serial, you can write 1 at the top of the row and drag it down to have a Serial Number.
1
This is very nice and simple solution on the preposition.
GREAT JOB. WELL DON. THANKS.
0
assalam o alykum

thanks lot
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 12, 2010 at 04:19 PM
WS and you are very welcome
0