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.
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.
I am putting together a macro to import a CSV file onto excel. I will be manipulating columns of data (concatinating some, dividing by 100 etc.)
What I am having trouble with is that each file imported will have different number of rows. I tried, in recording a macro, to use autofill (entered formula and double-click fill handle to drop the fourmula into each cell) hoping this would show me how to do it but alas, it just coded the range as the starting and stopping cells (didn't show a count formula or some such.
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.
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.
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
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
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.
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
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
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.
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.
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.
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?
I have a peptide sequence that is 4500 amino acids long. I have the numbers 1 - 4500 in column A and the corresponding letter code for the amino acid in that position in column B. I have done a bunch of other calculations and isolated small series of amino acids within the entire sequence (example: positions 25-42, 153-166, 381-297, etc). I would like to fill column C with either a blank space or, if it is in one of the selected series, the letter code again. So that the first 24 rows of column C would be blank but rows 25-42 would mirror the letter shown in B. Then blank spaces again until we reach row 153, etc.
I've used a vlookup to paste the letters with the chosen parts of the sequence, but now I want to space them out along the entire 4500 so that I can see if there are overlapping areas between several sets of criteria for choosing series (these would go in columns D, E, etc).
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.
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.
I have a spreadsheet that lists numbers in a column. They increase 1 number at a time, however the same number is listed 24 times before increasing to the next. Scaled down to 3 times it would look like this: 111, 222, 333, 444, 555, each one of those individual numbers would have their own cell. When I highlight the column and drag down, it starts doing decimals..how do I get it to recognize it? sample.xlsx
I want to drag it down to continue the pattern. Right now, I type a couple of numbers and drag to finish the section.
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
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:
Following is the master data that's imported from weekly timesheet.
DeptProjNoProjNameEmployee Dept170000Project1Shawn Johnson Dept170000Project1Brenda Larry Dept260010Project2Patrick Leh Dept170010Project3Shawn Johnson
When imported, the 'ProjNo' is stored in Text format. Now when forecast data is appended to master table, the ProjNo stores as Numbers. I would like to know how I can automatically convert it into Text. The number of records varies every time when imported from the Timesheet.
The other thing is I need AutoFill 'APPROVED' in Column 5 from 2nd row to the last row.
Example: Tab where the information is captured: ___A____ B____ C____ D 1 _TP1__ TP2__CON___PT 2 __3____3_____2____4 3 __2____1_____6____5
The tab where I want the info to pulled to runs in a row, not in columns, and there are 6 different branches with 35 different categories that I need to add the formula to to pull the info I require.
So, when I type the formula (=TAB1!A2) in A1, I get the correct info I require, but then when I drag it to the right (A2), it changes the formula to (=TAB1!B2) as apposed to what I want which is (=TAB1!A3). So now I have to manually type the formulas which will take me forever!
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)
