Sequential Numbering With Gaps
I have a column in which I enter a date, and an adjacent column which automatically enters a sequential number, using ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sequential Numbering
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 Replies!
View Related
Sequential Numbering Macro
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 Replies!
View Related
Gaps In A List
I'm having a problem with a list that I've created. The list is in cell A1, the base data for the list is in the range B1:B50. The problem is that data in this range is dynamic, i.e. it has formulas and depending on the result of these formulae the cells in the range either have a value or the cell is left blank. The problem this causes is that the list ends up having gaps in it because it uses blank cells as well. And this is despite me specifcally ticking " Ignore Blank " in the Data Validation menu where I'm creating the list.
View Replies!
View Related
Find Gaps In Sequence
I have a file that contains addresses in column C. I need to find any gaps in addresses. Ex: 211 Corbin Dr 213 Corbin Drive 214 Corbin Drive 123 Apple Drive 124 Apple Dr 124 Apple Dr 127 Apple Drive I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive. Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates.
View Replies!
View Related
Stop Gaps In Timeline
how I can format this timeline better (it was a to,e;ome template created by someone smart on this forum) - so that there isn't a huge gap between 1892 and then 1977... and then so the rest of the data isn't scrunched together.
View Replies!
View Related
Fill In Of Text Gaps
I have a sheet of over 40000 rows, I attach a sample. Column a is called dam and column b is called damsire. Each dam has only one 1 damsire. Both column a and column b are sorted ascending. Unfortunately there are big gaps in column b. some of these can be filled in as we have the information e.g. b38 and b39 should be Manila..as the row 37 tells you the correct damsire. Similiarly b49 could be filled in as shernazar. I want to create a new column which contains a formula to fill in these blanks. of course some of the blanks cant be filled in as the information is not there e.g. b23 to b28.
View Replies!
View Related
Align Shapes No Overlap Or Gaps
I am trying to get two shapes to butt up to each other. Unfortunately the shapes either leave a small gap or a slight overlay. I have tried using Ctrl + arrow key to move in small increments, but that didn't work. I have also tried adjusting the width of the rows, but the rows jump backwards or forwards to a number instead of staying with the number I entered. I want to create a seamless shape out of many different shapes.
View Replies!
View Related
Gaps In Cells When Calculating Formulas
I have what is probably a simple problem for most, but can't figure out what to do. In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
View Replies!
View Related
Removing Gaps From Data Extraction
I'm doing some simple data extraction, e.g. A B 1 bob 3 2 mandy 4 3 charlie 6 4 dave 1 5 steve 5 So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with, gap mandy charlie gap steve how would I get, mandy charlie steve also is it possible to have an if statement in 1 cell change the value of another cell? e.g. in a1 if(b1>5, c1="yes",c1="no"), can't seem to get it to work
View Replies!
View Related
Delete Gaps In Cells (postcodes)
I have a load of postcodes over 8 different tabs, the problem is the format of the postcode is wrong. I basically need to delete the first gap of each cell to make the postcode valid - DL 7 9 DL 8 1 DL 8 2 DL 8 4 DL 8 5 DL 9 3 DL 9 4 DL 10 4 DL 10 5 DL 10 6 DL 10 7 DL 11 7 You see I need to have it like DL7 9, or DL10 7, but i'm not sure how, I've attached the file so you can have a look.
View Replies!
View Related
Handling Gaps In Time Series Data
I have been browsing here off and on, and have found many excellent answers. I use Excel to process data on time series, as an adjunct to consultancy work on statistical analysis of industrial data . Usually the data has irregular gaps, e.g., daily data might have 2-10 day gaps. If I want to take, say, 7-day averages, SKIPPING OVER gaps longer than 2 days(say), is there an easy way to do this (I don't really know VBA,and it is not worth my time to try and write long code for this, which will eventually be done by some professional programmers)!
View Replies!
View Related
Sequential Months
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 Replies!
View Related
Using If To Sum() Non-sequential Numbers
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 Replies!
View Related
SUMIF With Sequential Criteria
In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805). HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column). Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?
View Replies!
View Related
Adding A Sequential Number
You will probably find this very easy but I am having all sorts of trouble making it work as I want to! Basically I need to do the following procedure... 1) Open an Inputbox to collect an eight digit number 2) Insert a column in A:A 3) In A1, enter a col header (URN) 4) In A2, enter the number that was collected in the inputbox 5) Enter sequential number from A2+1 to the last row Ideally, this would be randomised, so after stage 2 do RAND(), sort, and clear contents, but if I can get the main part right I'm sure I can work that out!
View Replies!
View Related
Loop Sequential Variables
I have variables 1-6 like the following: dDDDD1 = Format(d1, "dddd") dDDDD2 = Format(d1, "dddd") They correspond to a date from a cell and I just format one for "Monday" through "Saturday". In another module I'm opening a file that has worksheets named "Monday" through "Saturday" as well. I need to do the same thing to each of these sheets so I'm trying to setup a For/Next loop. I'm assuming you can't put a variable to a variable as I keep getting 'variable not defined' when I try to insert 'i'? Or am I just going about this all wrong? For i = 1 To 6 Workbooks.Open ("J:AcctMgtITrepADIinf-v2-WE0922.xls") Sheets(dDDDD & i).Unprotect Password:="hownowbrowncow" Sheets(dDDDD1).Columns("C:E").Insert Shift:=xlToRight Sheets(dDDDD1).Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True With Sheets(dDDDD1) Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)) End With ConcName = "=B1&"", ""&C1" LastRow = RngCol.Rows.Count Sheets(dDDDD1).Range("$E$1:$E$" & LastRow).Formula = ConcName Next i
View Replies!
View Related
Inputting Sequential Numbers From One Cell
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.
View Replies!
View Related
Sequential Check Digit Validation
I have a spreadsheet that pulls in a log file after running a macro with 100000eight digit numbers which may or may not have a record in the system. These numbers are produced by using any seven digits and adding a check digit at the end for an eight digit number using the mod function specifically Mod7...=MOD(A1,7) , for example, if cell A1 has 1234567 the mod7 function will produce the check digit 5 then concatenating for the eight digit number 12345675. the next number will be produced by adding 1 to the end of the 7 digit number 1234567+1=1234568 then concatenating again the number with the check digit 6, 12345686. this would be sequential order. my spreadsheet gets all the eight digit numbers with no record and sorts them in MOD7 sequential order in one column. since the spreadsheet ignores the eight digit numbers with records. my compiled list of numbers have non sequential numbers in the list. i need a way in VB to loop through 100000 lines (column A) and find the first and last number of a MOD7 sequential range and place the first number in (column B) and the last in (column C). I have figured a way to do this in a spreadsheet but it takes too many functions and calculations in 100000 lines so the spreadsheet takes too long to open and sometimes crashes!
View Replies!
View Related
VBA: Printing Sequential Invoices
I have written some code for a friend of mine, but I cannot test it because I currently don't have a printer attached to this computer. I am confident that it will work, but I would like a second set of eyes to confirm that I havent missed anything. I have a userform with two textboxes. One of them asks how many copies of the invoice to print out, and the second one asks what invoice number to start with. THe invoice numbers are recorded in Cell I1.
View Replies!
View Related
Add Sequential Numbers To Range
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
View Replies!
View Related
Inserting Sequential Numbers Into A Column
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
View Replies!
View Related
Rows On Worksheet Are Not Visible Or Sequential
This is so simple that I cannot even do a search for the answer. My worksheet has a horizontal and vertical cross. After row 16 for example, what is seen on the screen is row 24 for example. I did look at the instructions for "hidden" but I don't think this is a "hidden" issue. I don't want the rows to "jump" numbers, but want them listed sequentially down the page.
View Replies!
View Related
Find Time Gaps Greater Than "X" Minutes.
I get sheets, sometimes 10 records, sometimes 1,000 records. And there is a time column. It's formatted as: hh:mm:ssam (09:12:36am). Now what I need to do is find a way to highlight any record that has a large gap between itself and the record before it. This gap amount can be variable, but for the explanation lets say, I need to highlight any record that has a gap of 5 minutes greater than the time before it. I've tried a formula I found elsewhere: =F3-F2> 1/24/60*5 But it just returns "#VALUE!". I'm completely stumped, and since about last week, 1 of my daily responsibilites will be analysing anywhere from 10 - 50 sheets a day. The guy before me would open the sheet and manually find it by literally looking from record to record. This was this 1 guy's full time job. From what I'm reading around there are ways to have excel do this for me with a simple formula and conditional formatting.
View Replies!
View Related
Recording Data In Sequential Rows Without Overwriting
I am working on multiple sheets that collate to an “averages” sheet. The Averages sheet would need to collate the information historically. I need a code that can be assigned to a button so when it’s clicked, the figures for this week are added to the figures that were from last week. So for example. Worksheet 10, Rows 12 – 20 have the data from the last 8 weeks. Worksheet 09 has this weeks data and a button to push. When pushed it adds the data from this week to row 21 in Worksheet 10. The following week the button would add the data to row 22 and so on. Essentially each time the button is pressed it drops the information in a row one lower than the last time the button was pressed.
View Replies!
View Related
Same Page Printed But With Changing Sequential Number On It
I have a selected area which I print as labels. I want to have a number on this label preferably counting back from the quantity of print. So if I select 300 copies to print, the first label shows somewhere 300, the 2nd label 299, the third 298 and so on. I use a button and macro for printing selected labels, so perhaps the reverse printing could be added there if only counting up is available. Also to make it easier, a cell on the sheet can contain the number to count down from (this is preferred on thinking about it) So I could then print 20 labels from 300 to 280 say.
View Replies!
View Related
Using A Text File To Hold Sequential Numbers
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
View Replies!
View Related
Insert Rows For Missing Sequential Numbers
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.
View Replies!
View Related
How To Simplify Sequential Logarithmic Decay Formula
I am trying to create a s/sheet whereby one can calculate how the concentration of an isotope changes (via an exponential decay) with respect to 2 variables: 1. its half-life (arbitrarily shown as 3 'units' in cell E2), and 2. its initial concentration or dose (arbitrarily shown as 100 units) AND subsequent additional exposures (arbitrarily shown as exposure to 50 units every 3 days) (shown in column E). Thus for example, with an initial dose of 100 units (cell E6), after 3 days, the substance would have decayed by one half-life to 50 units. However, because there was an ADDITIONAL exposure of 50 concentration units on day 3, the EFFECTIVE dose on day 3 = 100 units.
View Replies!
View Related
Update Sequential Numbers After Row Deletion
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
View Replies!
View Related
Enter Sequential & Random Numbers
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.
View Replies!
View Related
Random Numbering
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 Replies!
View Related
Auto-Numbering
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 Replies!
View Related
Numbering In Forms
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 Replies!
View Related
IF Statments And Numbering
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 Replies!
View Related
Numbering System
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 Replies!
View Related
Numbering For Coordinates
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 Replies!
View Related
Numbering Macro
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 Replies!
View Related
Numbering By Group
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 Replies!
View Related
Numbering A List
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 Replies!
View Related
Sheet Numbering
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 Replies!
View Related
Adding IF(ISERROR) To Multiple And Non-sequential Cell References
Is there an easy way to change ='Week 1'!$D$21 ='Week 1'!$D$30 ='Week 1'!$D$39 ='Week 2'!$D$21 ='Week 2'!$D$30 ='Week 2'!$D$39 to =IF(ISERROR('Week 1'!$D$21), "", ('Week 1'!$D$21)) =IF(ISERROR('Week 1'!$D$30), "", ('Week 1'!$D$30)) =IF(ISERROR('Week 1'!$D$39), "", ('Week 1'!$D$39)) =IF(ISERROR('Week 2'!$D$21), "", ('Week 2'!$D$21)) =IF(ISERROR('Week 2'!$D$30), "", ('Week 2'!$D$30)) =IF(ISERROR('Week 2'!$D$39), "", ('Week 2'!$D$39)) Without having to change each one by hand? There is a pattern to the numbers. There are 5 weeks, rows 12, 21, 30, 39, 48 in columns D,I,N,S,X
View Replies!
View Related
Check & Add Sequential Numbers In Column
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
View Replies!
View Related
Roll Sequential Numbers Back & Forward
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.
View Replies!
View Related
|
TRACKER
