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.
View 9 Replies
ADVERTISEMENT
Sep 17, 2009
I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.
I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.
Here is my problem:
I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)
Example of data (Where "/" delienates column seperation):
Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain
6-1-06 12:15 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:30 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-1-06 12:45 am / 45 / 35 / .0001/ 95 / 5 / 7 / 3 / 0
6-2-06 6:00 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
6-2-06 6:15 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0
So what I need to do is this:
1) Find which data times are missing
2) Add the appropriate amount of rows in between where the missing data would be
3) Add the correct dates to the new rows
4) Add "N/A" to the columns that have no data
You can see that my date and time are formatted in mm/dd/yy hh:mm
I found this on the website and was trying to modify it to my needs:
Sub InsRow()
Dim c
View 9 Replies
View Related
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.
View 8 Replies
View Related
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.
View 9 Replies
View Related
Oct 12, 2010
I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.
example:
1
2
3
4
8
9
becomes
1
2
3
4
8
9
View 7 Replies
View Related
Aug 3, 2006
I am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:
Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.
ColumnA ColumnB
1 -------Part A
2 -------Part B
3 -------Part C
5 -------Part E
6 -------Part F
As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.
ColumnA ColumnB
1 -------Part A
2 -------Part B
3 -------Part C
4
5 -------Part E
6 -------Part F
I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.
View 4 Replies
View Related
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.
View 2 Replies
View Related
Jun 18, 2008
can do the following thing:
Here's a simplified form of my data under the two columns B and C
B C
1 a
2 b
5 c
7 d
I want dummy rows to be inserted for the missing rows like:
B C
1 a
2 b
3 dummy
4 dummy
5 c
6 dummy
7 d
View 2 Replies
View Related
Nov 27, 2007
I have a sequence(or a series is more proper) of dates(without the hours) for. exapmple: 10/22/2007,1025/2007,10/25/2007. What I need is to insert rows with the missing dates. I found a VBE code for a macro which does the work but now the problem is that in some rows the dates are the same and if I have two times 10/25/2007 it adds the row with the next date after the first one and all messes up.
Range("A2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(1, 0).Select
End If
Loop
View 9 Replies
View Related
Apr 25, 2014
I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.
I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
Still I have to check and manually insert extra missing for every half hour missing but that's bearable.
This formula inserts a "missing" every time it finds a gap.
My question is: How can I insert a row above every cell with "missing"?
How do I do that? Here is also my excel sheet.
CR1000_Meteo_20131113_2_CLEANED.xls
View 3 Replies
View Related
Feb 12, 2014
how to leaved blank cells for missing dates in a series of sequential dates. For example, If my series is (in (m/d/y) format):
1/10/1999
1/12/1999
1/13/1999
1/14/1999
1/17/1999
1/18/1999
But I want it to be:
1/10/1999
(blank cell)
1/12/1999
1/13/1999
1/14/1999
(blank cell)
(blank cell)
1/17/1999
1/18/1999
How do I do this?
View 1 Replies
View Related
Apr 28, 2014
I have data in several rows whereby my column D looks like this:
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000
Throughout the week, additional data is added to the rows, thus creating blank cells within this D Column:
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000
Is there a macro i can run which will re increment only the blank cells to look something like this?"
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_03.1000
FebtReport14_04.0000
FebtReport14_04.1000
FebtReport14_04.2000
FebtReport14_04.3000
FebtReport14_05.0000
Also , I will be adding new data quite often, in which newer rows may need to be added in between: ie:
from this
FebtReport14_04.0000
FebtReport14_04.1000
to something like this:
FebtReport14_04.0000
FebtReport14_04.0100
FebtReport14_04.0200
FebtReport14_04.1000
View 5 Replies
View Related
Jul 8, 2008
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
View 9 Replies
View Related
Jan 7, 2009
I have a string of numbers ex. 1234, 5678, 91011 in 400 rows. Before each string I would like to add 88.
View 4 Replies
View Related
Apr 4, 2008
I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................
With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!
View 5 Replies
View Related
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
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
View 9 Replies
View Related
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.
View 14 Replies
View Related
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
View 3 Replies
View Related
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
View 7 Replies
View Related
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
View 8 Replies
View Related
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.
View 6 Replies
View Related
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
View 9 Replies
View Related
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.
View 7 Replies
View Related
Aug 4, 2009
I am looking for help. Sheet1 B1:1 has dates in each column in ascending order for one month. There are missing dates in between the days and they might look like this.
Example 1
7/1/20097/2/20097/6/20097/7/2009
Is there a code that will insert columns with the missing dates between the dates that already exist so that example 1 would become example 2?
Example 2
7/1/20097/2/20097/3/20097/4/20097/5/20097/6/20097/7/2009
View 6 Replies
View Related
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.
View 8 Replies
View Related
Jul 14, 2013
I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.
******** language="JavaScript" ************************************************************************>
Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
(A)boutA1=ABCD1This WeekLast WeekMissingAdded
21002103110311002310271032103210274103310331034104051035103410361055610371036
165571038 81040 91055 101655 Sheet1 [HtmlMaker 2.42]
View 2 Replies
View Related
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
Dec 19, 2011
We recently bought some new software at work, and I'm having trouble formatting the exported data in a way that will work with existing reports. The attached file is what the data looks like after being exported. Basically, what I'm trying to do is insert rows for the missing numbers -- i.e., each name should be followed by rows numbered 1 through 10.
John Smith John Smith
1 1
4 2
8 3
4
5
6
7
8
9
10
My ultimate end goal is to have columns of data for each name, instead of rows:
John Smith 1 2 3 4 5 6 7 8 9 10
I've created a formula that can do that part, but it operates on the assumption that each name will be followed by a set number of rows.
What little I know about vba I've learned on my own, so I may just be missing something obvious.
View 4 Replies
View Related
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.
View 3 Replies
View Related