# Inserting Sequential Numbers Into A Column

Oct 1, 2009

I have a column of an undefined number of rows where I need to add item numbers from 1 to however many items there are, starting from A9 downwards.

The last 3 used rows on the sheet contain signatures etc so it should not number the bottom 3 rows.

pretty sure its fairly simple code but i dont have anything similar from previous files that i can re-use to do this :p

just needs a simple

count how many rows are blank from A9 downwards (to say A200)
for num=1 to count do
Cell range(A(9+num) = num
end

i just dont know the code well enough to write it and make it work :p

## Check & Add Sequential Numbers In Column

Jan 26, 2008

I have two columns A and B filled with numbers. Column A has the identifier number and Column B has number that represents intensity for the data set.

Column A can be anything from 75-1000, however, sometimes the adjacent rows are not sequential (i.e. 101, 102, 104).

1) Is there a way to automatically check for skipping numbers in Column A.
2) When it finds a skipped number, insert a row for column A and B.
3) Then put the correct number in Column A (i.e. 103) then put 0 in for column B

## Fill Range With Sequential Numbers Based On Corresponding Column Groups

Feb 15, 2010

In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?

## Copy / Drag From Sequential Column To Non-Sequential Ones While Retaining Sequence

Jan 29, 2014

I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.

I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.

Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.

I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.

## Using If To Sum() Non-sequential Numbers

Jan 23, 2009

I need a formula to add data in it's respective column, only if the month and year match.

The columns of data will be very long, thousands of lines possibly.

So, basically, in this example.

in O1, if the the data in the columns below match the month(M1) and year (N1), then sum those variables.

## Add Sequential Numbers To Range

Aug 17, 2007

I have created two names within column A (ie: Insert/Name/Define)

in cell A1 I've named StartA01
in cell A5 I've named EndA05

I would like to create a sequential number within this range via script, whereby when new rows are inserted, the script will update the order.

eg:

begin with:

A01
A02
A03
A04
A05

3 rows inserted will update to:

A01
A02
A03
A04
A05
A06
A07
A08

## Inputting Sequential Numbers From One Cell

Nov 28, 2008

I am trying to make excel list in a column like 1,2,3,4,5. this is dependant on a value i place in one cell eg 5

i want excel to then place 1,2,3,4,5 in seperate cells down a column. does this make sense.

then the calculations will only appear the the numbered cells.

## Display Missing Sequential Numbers?

Apr 17, 2013

I have a sheet where one column has sequencial numbers from lowest to highest with some missing. So for example: A1 1, A2 3, A3 5, A4 6. Is there a formula that will display all the numbers that are missing from this column without skipping rows? So let's say: B1 2, B2 4 ect.

Basically I will be putting this on a different tab, so someone can go to that tab and see what numbers have not been used. It is a large range and is difficult for someone to scan through it all.

## Autofill Rows With Sequential Numbers

Oct 8, 2011

I currently have an array that is 6 columns by 7 rows that is populated by referencing other workbooks and returning the sum of their values. For issue tracking revison etc... I would like to have the rows incrementally numbered (0,1,2... until it reaches number "X" which is a grand total in one of the fields.

This would happen for column 1 row 1 all the way through column 6 row 7 and fields with 0 as a total don't get a number or can have a 0 or blank.

Ex.
1 2 3 4 5 6 (rows)
1 3 x x x x x
2 x 7 x x x x
3 4
4
5
6
7

row numbering...
1
2
3 (skip col. 1 row 2)
1
2
3
4..... continues on till the end.

I would like this to be something somewhat automatic to eliminate the redundancy of having to manually perform this task.

## Update Sequential Numbers After Row Deletion

Oct 16, 2007

How do I get my worksheet (ServicePlan) to update column A (plan number) after deleting a the row from a userform. When I delete the row the plan numbers do not update.

Example: I delete plan #1 from the worksheet. The plan number #2 should now be amended to read plan #1 and the plan number for record #3 should now be amended to read Plan #2, however the plan numbers #2 and #3 remain the same.

Plan #Rv# Name:Dept: Rep:
11.1mikeEnvironmental Cathy S.
22.1TomCasualty Rich A.
3 1.5 Michelle Special Programs Diane

Here is the code I used for the delete plan # button. Thank you

Private Sub CommandButton15_Click()

If MsgBox("Are you sure you wish to delete this record?", vbYesNo, "Confirm Deletion") = vbYes Then

sheet1.Activate

Dim strFind
Dim Nullstring
Dim rSearch As Range 'range to search
Set rSearch = sheet1.Range("A2:A1000")
Dim c
Dim r As Long

## Enter Sequential & Random Numbers

Feb 1, 2008

I am trying to create a summary sheet that will look on certain worksheets based on on a cell. For example:

If cell E1 equals 10-Oct-30 then it will pull data from tab 'End Oct 30' I would prefer to use a formula for this calculations. to aid in another persons potential use. A custom function would be OK.

## Using A Text File To Hold Sequential Numbers

Oct 14, 2009

i found a macro that im trying to get working from the site below to generate a number everytime my template is opened.

ive put the code in the thisworkbook module as directed and changed the relevant path but it doesnt do anything i think im missing something but cant see what. this is the code ive placed in thisworkbook module of my template

## Fill In Missing Sequential & Repeating Numbers

Dec 10, 2008

I am busy with a very large dataset +-20 000 records showing the movement data
and I am having problem with inserting missing data: I have columns titled day hour and position, but multiple records for each as data was initally recorded by second. Data looks like this

Day Hour Position
1 2 Z5
1 2 Z5
1 3 Z5
1 3 Z4
1 16 Z1
12 2 Z3
13 0 Z3

What I need to end up with is a sequential dataset running from day 1 to 388 with hours 1-24 of each day. Positions need to be filled in for all data recorded (see above). The big problem is that movements between positions need to be recorded and accounted for i.e if more than >80 of time was spent at the position Z3 then that is the position for the hour.

## Roll Sequential Numbers Back & Forward

Jan 28, 2008

I am running a worksheet that runs through 4 week periods.

Example: 10.1 10.2 10.3 10.4 11.1 11.2 11.3 etc.

I wish to insert a formula that will roll forward or back this data when required.

I have no problem rolling whole numbers & dates.

Iam using Excel 2000.

## Insert Rows For Missing Sequential Numbers

Feb 19, 2008

The excel file has industrial information arranged by sector. The sectors are represented by six digit codes that are not strictly sequential. For example, a typical example of consecutive codes is 120011,120012,120030,120040,130011. There are upwards of three hundred sectors.

The data I am using is missing certain sectors - sometimes more than 30 at a time. I would like to find a quick way to identify the missing sectors and insert rows there, preferably with the first cell in the row being the missing sectoral code.

I have a similar problems with columns as well. But I can always just transpose the data and use whatever solution works for the rows.

## Data Validation Dynamic Sequential Numbers List

Jul 21, 2007

I am looking for a way to define a 'Data Validated' Cell to hold a List of Dynamic Sequential Numbers - from 1 to the Value of cell A4. For example:

If cell A4 has a value of 5 the 'Data Validated' List will be 1,2,3,4,5

I have tried to 'name' the Array-Formula {=ROW(INDIRECT("1:"&A4))} and put the 'name' as the List source but without success.

## Print Same Sheet Multiple Times With Sequential Page Numbers?

Dec 21, 2012

I am trying to set up excel sheets for documents that we print often. I would like to print them with page numbers in right footer that increases sequentially.

setting up a macro. But very time I print it starts from same number instead of the number after where I left off. Example: if I print today with page numbers 1 through 12, next time I want it to print number 13 through whatever no of copies printed.

Here is the code I copied from one of the threads here:

[Code] .....

I need the page number in right footer and also the macro to save the last number printed.

## Excel 2007 :: Autofill Column B With Sequential Values Based On Value In Column A

Nov 27, 2011

Autofill column B with sequential values based on whether value in column A changes its value.

I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.

This is what I imagine.

A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"

I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.

I'm using MS Excel 2007 in Windows 7.

## Counting Sequential Zeroes In Column

May 21, 2014

How do I go about having a formula which will return the number of times 0 is repeated in a column sequentially?

e.g.:

0
1
0
0
0
0
0
1
0
0
0

The idea being that after row 7 (there have been 5 consecutive zeroes), the count would reset when it hits a value greater than zero, and then count again once it hits a zero again.

Output for the example above should be 3.

## Create Sequential List That Resets To 1 When Value In Another Column Changes?

Sep 18, 2013

EQJq7ZC.jpg

I have three columns of data (A,B,C) and I want to add a formula or script to create the data in column D. The 'XXX's are all numerical values and will vary.

Column D should be a sequential list, which restarts at 1 any time that either:
- the value in column A changes, OR
- column A stays the same, but the value in column B goes from something to nothing, together with the value in column C going from nothing to something (or vice versa).

## How To Add Sequential Numbering Down A Column Until The Next Number 1 And Start Over

Feb 13, 2014

I have a column that is listing steps in multiple processes. I have each step 1 marked with a number 1 and would like to have a macro run that will read the column and when it sees a 1 move down to the next row and insert a 2, 3, 4, etc. until it hits the next 1 and then repeat the process. I am unsure how to build this as I am just starting to learn some VERY basic VB scripting.

## Sequential Numbering Based On Column Having Text?

May 28, 2014

If a cell in column B has text in, is it possible for column A to auto populate a sequential number? so basically if data is entered in a cell in column B I want column A adjacent cell to auto populate a sequential log number?

## Sequential Numbering For Duplicate Cells In Column

Feb 28, 2008

In my worksheet i have several rows that are duplicates and i need to give each row a unique number. For example the first duplicated row needs to be 1 the second 2 etc.

## Inserting Text Infront Of Numbers

May 18, 2009

Yesterday I got the solution to insert the text by using custom format. Exampe: 112233 to Ab-112233 by using "Ab-"General

But when I tried the same method to inserset the Ab on 11-1122

Like 11-1122 into Ab-11-1122 in same cell, it doesn't work.

## Automatically Inserting Numbers Into Text

Jul 25, 2008

How can I have a number inserted into text on an excel sheet. for example if I have the number 100 in cell A1 and I want it inserted into the following sentence in sell A2:

You are 100 years old. I want the number to be able to change automatically in this sentence when the number in A1 also changes.

## VLookup Only Right Most 4 Digits Of The 6 Digits Sequential Numbers

Apr 30, 2014

I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.

=IF(ISERROR(VLOOKUP(B4,' cmfs01home\$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!\$B\$2:\$F\$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home\$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!\$B\$2:\$F\$301,4,FALSE)

## Inserting A Text(alphabate) In A List Of Numbers

May 16, 2009

suppsose i have 50 list of numbers in column A. I want to insert a text "AAB-" in whole list. How can I do that.

FROM:

1122
1123
1124
1125

To:

AAB-1122
AAB-1123
AAB-1124
AAB-1125

Mar 19, 2009

i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet

i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.

Numbers with Dr as suffix denote positive numbers
and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.

## Inserting Columns With Numbers Relative To Previous Data

Jan 19, 2012

the following issue:

I have a spreadsheet of questionnaire responses which range from 1-7

For example:

Respondent Q1

1 4
2 3
3 7
4 6

So each row is a new respondent and each column is their response from the scale.

What I need to do is code the responses into a different form. I need them to be represented as follows:

1 0 0 0 1 0 0 0
2 0 0 1 0 0 0 0

So that each number then represents the place on the scale from which it was chosen.

I tried recording a macro but I think this requires something a lot more complex.

## Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.