Fill In Missing Sequential Increment?
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
ADVERTISEMENT
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
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
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 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
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
View Related
Jul 20, 2014
I'm making a Purchase Order generator for work. Essentially, the main screen has buttons and the user selects the company, job number, their name etc. They click 'Generate' and it will great a brand new excel file for them with all the correct codes, ready to populate and send to a client. In the main sheet, we also have a master list showing every purchase order made to date. This is where I am currently stuck. I will have many more questions on the way. This is my first program so very new!
Lets say we have the following in cells A1 and A2. The rest is blank:
REQ0001
REQ0002
[Code] .......
View 3 Replies
View Related
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?
View 2 Replies
View Related
Dec 17, 2012
I have items in a column like below:
50
50
50
50
What I would like to be able to do is highlight the four 50's and fill down, but I would like for it to increment and paste the next four filled like this:
51
51
51
51
Then below that the next four filled like this:
52
52
52
52
If its a macro that'll be required I'd like to to be able to change the number of rows to be repeated (4 in this case, but could change).
View 4 Replies
View Related
Jun 14, 2013
I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).
Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).
Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1.
View 2 Replies
View Related
Aug 30, 2008
I have been frustrated trying figure out what I would assume is actually a pretty simple solution. All I am trying to do is grab a set of cells and repeat them down the column a set number of times. The 2 forms of this are discribed below.
A: I have a set of numbers from A1 to A51 I want to repeate those numbers down the columns 84 times.
B: In addition to this I need to have the cells from B1 to B51 Repeat 84 times but with an increment of 200 each time. So I would have say 1 in B1 - B51 and in B52 - B102 it would turn into 201 and so on and so forth.
View 5 Replies
View Related
May 22, 2008
When I have a text field, such as 123 Main Street, Suite 456 and I copy it down, I get
123 Main Street, Suite 456
123 Main Street, Suite 457
123 Main Street, Suite 458
What is this feature called, and how do I turn it off?
View 2 Replies
View Related
Mar 29, 2014
I have items in a column like below:
Line (A1) asdfasdf
Line (A2) adsfadsf
Line (A3) asdfasdf
What I would like to be able to do is highlight the three columns and fill down, but I would like for it to increment and paste the next three lines filled like this:
Line (A2) asdfasdf
Line (A3) adsfadsf
Line (A4) asdfasdf
Then below that the next three lines filled like this:
Line (A3) asdfasdf
Line (A4) adsfadsf
Line (A5) asdfasdf
The (A1, A2, etc.) are cell ranges and already contain the concatenate function that pulls data from a different location. The problem I am having is when I
highlight and fill down, it doesn't increment the cell by +1 (Ex. A1 beomes A2) but instead by +3 (A1 becomes A4) the number of lines highlighted.
View 8 Replies
View Related
Jul 30, 2013
I have got the following problem. I have got a table which looks like this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
When I mark the selected cells and scroll down using the autofill option I get this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
=Time!A6
=Open!A6
=max!A6
=min!A6
=Close!A6
Why does it give A6? I want A2 instead of A6 because I want to create a table from A1 to A500 but without these jumps from A1 to A6.
View 5 Replies
View Related
Feb 10, 2010
I have 2 sheets in the same workbook (sheet 1 & 2) with one matching column (A) of info and need them to combine and fill the missing data. Each sheet has identical column heading and the amount of data is not the same; sheet 1 has ~2000 rows and the other ~5000. Sheet 1 is consolidated so I would like to fill in the missing cells from sheet 2.
Ex. Sheet 1.......
Ex. Sheet 2.....
As you can see, the missing cells in sheet 1 could be filled from the data in sheet 2; also note that the above info is on different rows (and will be random for each case). I only want the columns to fill in that have a matching email, the other 3000 rows in sheet 2 do not match with sheet 1 and I don't want the rest of it to transfer over.
View 4 Replies
View Related
Mar 29, 2012
I get given a csv file on a monthly basis which contains consumption data per day for the specified period. This sounds simple but on occasion (more often than not) the data has missing days. This can cause me problem later on in my analysis.
I can happily total the monthly consumption using the date and month text. What i want to do however is to sort the csv file into daily consumption and highlight the missing days i.e. have a range of the days in the month and allocate the daily data to the correct date. I currently do this manually but know that there must be a better, automated approach... searching for matching dates for example?
In my head i'm thinking the following approach but lack the coding skills to do it.
1. Define the start and end dates. Perhaps count the number of days between the two dates and autofill the start date down the appropriate number of days in column A?
2. Paste the csv file into a different sheet and, starting from the top, cut and paste the csv data to the correct date created in step 1. Do this for each row based on the csv data.
View 1 Replies
View Related
Jul 17, 2008
I'm trying to setup a macro to run on a database extract that is sent to me. Some of the data is not filled in for each row and I want to automate the auto fill down for each entry based on the Reference # above. I searched the forum for terms that I thought might be related,
Here is the starting data: ...
View 10 Replies
View Related
Aug 9, 2008
i have a time series of data at one hour interval ( from 6:00 hrs to 18:00 hrs ) each day and again from 6:00 to 18:00 hrs next day and so on for several years. in this series many data are missing. i ve to fill in these missing data in such a manner so that the new values becomes the linearly interpolated values in between the data given at any two hours. no rows are allowed to be inserted in between 18:00 hrs of the previous day to 6:00 hrs on the next day. pl see the attatchment.
View 2 Replies
View Related
Jul 20, 2012
I am having trouble getting a VBA code to do the following:
2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:15:00
2012/05/01 00:18:00
From this above to this below
2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:09:00
2012/05/01 00:12:00
2012/05/01 00:15:00
2012/05/01 00:18:00
There are data entries next to these time stamps. I am able to create just the time stamps starting at the beginning and ending at the end of the month but I need it to fill in missing entries in a data set. The code below works but only some of the time. I have tried a few different things but nothing works.
Code:
Sub Insert_missing_3min()
'Inserts a row with the date and time where the missing date and time stamp is and a zero next to the date added.
Dim min3 As Date
Dim CurTime As Date
Dim CurCell As Date
Dim NextCell As Date
min3 = 3 / 24 / 60
[Code] ........
View 1 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
Sep 1, 2009
I am looking for a formula that I can use in the lower table to fill in the missing County cells, based on the values in the Town/Zip columns on the top table (I apoligize for the crudeness):
CountyTownZipAbleTownA00000AbleTownB00001AbleTownC00002BravoTownD00003BravoTownE00004CountyTownZipMemberTownD00003BlackTownC00002FrankTownA00000GreeneTownB00001JonesTownA00000SmithTownC00006ThomasTownE00004White
This would consist of hundreds of Zips and Towns and this is just an abbreviated mock up.
View 9 Replies
View Related
Sep 18, 2008
new data goes into the report, the pivot table looks at a dynamic range and confirmed that the range doesn't select any empty cells.
Just wrote a macro to fill missing cells with a data on workbook open.
all the data is date form, yet still when i refresh the pivot table i look my montly grouping, when i try to group again it says "unable to group"
View 9 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 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
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
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
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
Apr 10, 2007
Is it possible in Excel to automatically rename all the tabs of a workbook in one move in a sequential format - eg renamimg 52 weekly tabs Week 1, Week 2, Week 3 etc.
View 2 Replies
View Related
Jun 19, 2014
I'm trying to create a single column of stock bin codes for eventual conversion to barcodes. The bin codes run like this:
A1A
A1B
A1C - etc. to A1H, then the sequence starts again with:
A2A
A2B
A2C etc. up to A6H, when the 1st character changes to B and the sequence starts again
This needs to continue until the sequence reaches Z6H
How to do this without having to enter each code manually?
View 11 Replies
View Related
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
