Sequential Numbering Macro?

Feb 28, 2014

I have a large sheet (currently some 5,000 rows and growing) where each row is allocated a Unique Reference, however that unique reference is based on two criteria, 'Region' and 'Type'.

There are four 'Regions' and three 'Types' across the whole sheet (see attched sample).

Because of the ever increasing number of entries and the fact that the sheet may be sorted so the unique references won't always appear in sequential order, I am looking to try and find a way for the unique refrence for 'new entires' to the sheet to be generated automatically, based on entires in other columns. The sheet structure is relatively simple, with 'Region' shown in one column and 'Type' in another (again, see attached sample).

The unique references adopt this structure - 1st letter of the region (N, S, E or W for North, South, East or West), followed by 1415, followed by the first 4 characters from the 'Type', (REGI, NATI or COUN for Regional, National or County) followed by a sequential 5 digit number 00001, 00002, 00003 etc.

resulting in for example N1415REGI00001 or W1415COUN00012 and so on.

Because there are thousands of entries, I need an automatic way for the unique reference to be generated, ideally once both the 'Region' and 'Type' fields are populated, so the macro (or whatever method works best) will automatically determine the previous highest number for the relevant series and automatically add the next number for the new entry, based on the above criteria.

I don't know if this is possible with a macro or whether there is an easier formulaic way to achieve this?

View 14 Replies


ADVERTISEMENT

Sequential Numbering Macro

Sep 3, 2006

I need a macro that will number a cell (A1 for example) starting with the number 1, and another cell (A2) with the number 2, then back to the first cell with 3, then back to the latter cell with 4 and so on.

View 9 Replies View Related

Sequential Numbering

Oct 25, 2009

I have a workbook with two worksheets. Worksheet #1 is a form that will be populated with data and saved as a new worksheet, then cleared and used repeatedly as a master form. Worksheet #2 is a log / register of the unique forms completed and saved from the master each time. I need to assign a unique sequential # to each form when it is saved and record this number in a column on Worksheet #2 (the Log). I am using some macros for the copy work but struggling with the auto-numbering of the forms when completed and saved.

View 3 Replies View Related

Sequential Numbering With Gaps

Nov 10, 2007

I have a column in which I enter a date, and an adjacent column which automatically enters a sequential number, using ...

View 10 Replies View Related

Generating Sequential Numbering For Documents

Dec 5, 2012

If i had a template in excell would it be possible when it was opened it could generate a new number for the sheet in a cell for Ref ID.

View 3 Replies View Related

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.

View 5 Replies View Related

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?

View 5 Replies View Related

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.

View 7 Replies View Related

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.

View 3 Replies View Related

Numbering Macro

Aug 17, 2008

why the Macro below works fine when the spreadsheet is not filtered, but once you filter the spreadsheet it does not work. and if possible a solution.

Sub Count()
Dim MyInput As Integer
MyInput = InputBox("Enter Start Number")
MsgBox ("Start number is ") & MyInput
mycount = Selection.Rows.Count
MsgBox mycount
ActiveCell.FormulaR1C1 = MyInput
For Num = 1 To mycount
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = MyInput + 1
MyInput = MyInput + 1
Next Num
End Sub

View 9 Replies View Related

Macro To Have A List With Automatic Numbering

May 8, 2013

The context of my question is "Invoice Numbering".

I would like to have a sheet where I can have an incremental number. Every time I run the macro it should strikeout the last number and it provides me an incremental number.

I used such a sheet in a previous job and I cannot find it online.

View 2 Replies View Related

UserForm / Macro To Search And Create New Sequential Reference?

Jun 4, 2014

Every new file I create is named with a sequential reference based on some criteria:

PackageName_(Place+SequentialNumber).

So, in my worksheet I have several columns with package names: LABK, LAVO, LACR… and on the rows I have the file names.

I have this UserForm I created Search.JPG

Basically (despite I imagine it’s not that simple), when I type the package name and the place as follows, for example

Package = LABK
Place = SAO

After clicking on “Generate” button, it should look for all file names containing LABK and SAO, verify what the last sequential number is (highest/MAX) and generate a new one in the text box below the button.So, if the last file name is LABK_(SAO005), it’ll generate a new one as LABK_(SAO006).

View 4 Replies View Related

Macro To Save, Save As With Sequential Name

Feb 5, 2007

My spreadsheet has 32 worksheets, I've recorded a macro to do the job "save, save as with specific name", but what I want is, when save as, I wanted the file name increase in one number, and the date in a specific range "H8:J8" the date should be the next saturday.

here is part of the code.

Range("D11:J65").Select
Selection.ClearContents
Range("J44").Select
Sheets("NsFri").Select
ActiveWindow.SmallScroll Down:=-12
Range("A12:A21,D12:I21").Select

on this example I want the file saved as "PrA4W05.xls", being the next week "PrA4W06.xls", and so on.
and on "H8:J8" the next saturday.

View 9 Replies View Related

Numbering For Coordinates

Oct 24, 2007

how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).

The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)

View 10 Replies View Related

Auto Numbering A Tag Name

Dec 26, 2007

I would like to know if there is a way to Auto number a text.
I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down.
Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on

View 9 Replies View Related

In Sheet Row Numbering And TOC

Nov 3, 2008

is possible to add a hyperlinked TOC (Table of contents) within a spead sheet (Excel 2003) like it is possible to do in Word. Also how do I go about sequential numbering of rows with sub numbering as shown below? Where if i add a row between 1.2.2 and 2 it would be 1.3.

1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
2

View 3 Replies View Related

Automatic Numbering In VBA Etc

Dec 18, 2007

I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...

1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.

2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?

3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?

View 12 Replies View Related

Duplicating A Row And Numbering?

Mar 3, 2009

I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150.

So in the end it would go from: (sorry for the periods.. extra spacing didn't work!)
A........B................C.......D
AAA...123 Street...City...<blank>
BBB...456 Street...City...<blank>
CCC...789 Street...City...<blank>

To:
A........B................C.......D
AAA...123 Street...City...1
AAA...123 Street...City...2
AAA...123 Street...City...3~
AAA...123 Street...City...150
BBB...456 Street...City...1
BBB...456 Street...City...2
BBB...456 Street...City...3~
BBB...456 Street...City...150
CCC...789 Street...City...1
CCC...789 Street...City...2
CCC...789 Street...City...3~
CCC...789 Street...City...150

I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it

I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.

View 2 Replies View Related

Numbering A List

Mar 6, 2009

I'm trying to make a sequential resultlist starting with nr 1, 2, 3, etc under the column: Rank ? This should be part of a macro, so autofill is not an option... As you can see, the number of rows are different from each group, and starts with nr 1 for every group. (Some formatting became all wrong posting this.........

View 5 Replies View Related

Auto-Numbering

Mar 25, 2006

i have formulas in a range L5:L15 which sometimes return some value and
sometimes zero. i want to give them auto numbers in column M in a way that it
should only count the cell which has some value.
suppose formula in L5 returns some value, L6 also then L7 & L8 have no
value(but formula persists), cell L9, L10, L11 has values then L12 has no
value L13, L14 has value and L15 has no value (but it has formula in it)
values in these cells changes and some goes to zero and some return values.
now i want to give them Auto Numbers in a way that cells with some value
should only be considered.

View 10 Replies View Related

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.

View 10 Replies View Related

Numbering System

Oct 19, 2009

Wondering if there is a formula for Excel that could replicate a numbering format like in Word?

Example:
A1.1.
A1.1.1.
A1.1.2.
A1.2.
A1.2.1.
A1.2.2.
A1.3.

and so on...

Idealy I would like to go farther than the 3rd level.

View 6 Replies View Related

Numbering For Same Cell Value

Oct 24, 2011

I have a spreadsheet, there is large number of items entered. Now I want to numbering of this Items. There is approximate 250 items that are start with A, there numbering will be 1-250 then start with B, there will be numbering 1- something like this, then start with C....

To do this numbering is there any formula?.

View 5 Replies View Related

Using For / Next With Sequential Variable Name

Jan 10, 2013

I'm just looking to step through some variables using for/next

variable1 = 2
variable2 = 7
Variable3 = 9
Variable4 = 15

For xxx = variable1 to variable4

'all sorts of fun code here

Next xxx

-obviously this doesn't work, it steps from variable1 right to variable 4....need a way so the code includes variable 2 and 3.

View 6 Replies View Related

Random Numbering

Jan 30, 2007

I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once.

I am putting the formula in column B.

View 9 Replies View Related

Numbering In Forms

Dec 29, 2008

I have created a form to input parking ticket data to a spreadsheet, it all works exactly as i want it to, but i really need it to tell me the next available number or empty line, so i can use that for filing and audit purposes, ideally i would like it to do sequential numbering, but i've been looking for weeks and cant find a soloution, i have basic knowledge of VBA and i'm really struggling with this,

View 9 Replies View Related

Numbering By Group

Feb 10, 2009

i have items listed in groups and need to number them

1111
1111
1111
1222
1222
1222
1222
1444
1444

in the column beside this i need these items to be numbered

1 1111
2 1111
3 1111
1 1222
2 1222
3 1222
4 1222
1 1444
2 1444

View 9 Replies View Related

IF Statments And Numbering

Feb 26, 2009

Heres an example of what I'm trying to do,

if I select form a list a certain name (i.e. "Plt") then I want it to populate a list of numbers (1-102) and the same with "SO" populating numbers 1-119.

and here is what I have so far

=IF(OR($F$1="Plt",$F$1="SO",$F$1="Plt LR",$F$1="SO LR"),"1.")

Is there anyway of making excel do this?

View 9 Replies View Related

Sequential Months

Feb 8, 2010

I am creating a budget worksheet for non-technical users. They choose a starting month from a drop down menu (already created). I want the remaining 11 months to automatically fill in to the right (in a row). I have created a macro (initiating autofill) that requires them to click the button after they choose the starting month but this copies the drop down menu & Input message from the Data Validation I used to guide them initially.

View 9 Replies View Related

Sheet Numbering

Feb 6, 2007

I'm wondering if this is the way things work and there's nothing to be done about it (but I doubt that). I have a workbook that I load data into from a csv file. The csv file is "divided" into regions, and I want each region's group of data to be loaded into a separate sheet. To be on the safe side, I delete all the sheets before loading the data with the following code that I found in this forum

Sub delete_all_sheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If sh. Name <> ActiveSheet.Name Then
sh.Delete
End If
Next
Application.DisplayAlerts = True
End Sub

Then, for each new region, I create a new sheet with the following code

On Error Resume Next
sheet_nr = sheet_nr + 1
Sheets(sheet_nr).Activate
If Err.Number <> 0 Then
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
End If
On Error Goto 0...............................

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved