I have a set of number in worksheet IN:
A B C D E Columns
41.0141.0241.0341.0441.0
42.142.242.342.442.
43.0143.0243.0343.0443.0
44.144.244.344.444.
45.0145.0245.0345.0445.0
46.146.246.346.446.
Datarows increment by 1. I need to map these to another workbook worksheet named OUT
to rows that increment by various amounts 1,2,3 ....10) i.e..............................
There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.
In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are
5700 entries that show up 2x
504 that show up 3x
24 that show up 12x
I am looking for a formula that I can drag down a column that is pulling data from left to right at the top of my spreadsheet. See the attachment.
View 5 Replies View RelatedI am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:
List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER
Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”
So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.
=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))
I want to call a macro with a varying name that is within a module with the same name.
I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc
On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.
The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test1.Test1
End Sub
The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):
Sub Test3()
Test2.Test2
End Sub
etc
However, if I try it so that the calling of the macro varies as below , it does not work:
Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub
I need to create a line of VBA code to have the macro select several rows to be cut and moved. The problem is, at this stage of the macro,the number of rows vary. Could be 1, could be twenty. I have established DD as the Dim for the number of rows, but I am not sure how to write the command so that 1 through DD are selected, cut, and moved. I may be having a senior moment, but I can't think of how to couch the term.
View 9 Replies View RelatedI paste in information in Columns A-E with varying lengths for each report that I need to generate (They generally range anywhere from 10-500 rows). I have formulas already in place in the first row of Columns F-L that need to be copied down as far as columns A-E go. I recorded a macro for this when I try to run the macro for another report with more or less row entries it copies down as far as the original recording was set. How do I make it so the rows will copy down just as many as I need and not some arbitrary number of rows? This is my first post so if any clarification is needed that's no problem.
View 3 Replies View RelatedUsing Excel 2003, Column E has a varying and unpredictable number of rows, which only the final 252 rows are of significance. I need to compute the following formulas, here written in English as I can't determine the proper terminology to accomplish this task in Excel:
Final Row with a number in Column E/average (final 252 rows with numbers in column E)+
Final Row with a number in Column E/average (final 126 rows with numbers in column E)+
Final Row with a number in Column E/average (final 63 rows with numbers in column E)
Once again, what makes this not straightforward for me is the column may have wildly varying numbers of 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.
I've trawled a few forums for a while now trying to find a macro that matches my specifications, or one I can adapt but so far have been unsuccessful, so here goes:
I have one column of data (col. A) of customer names and addresses each with an empty row in between them. The lines of data relating to each customer vary from 6 rows to 14 rows, then an empty row, then the next customer and so on - for several thousand rows.
I am wanting to create a macro that will transpose each customer to either their own row in sheet2, or to column B and then delete column A at the end.
All I can find is macros for a specific number of rows rather than varying. One way would be to have a code that 'pads' out those customers with fewer than 14 lines by inserting rows to make them 14, and then a code to transpose 15 rows at a time.
how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?
I have tried relational positioning e.g
each time a row is created, integer n5 = n5 + 1
Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n2 = ActiveCell.Offset(rowoffset:=1, columnOffset:=-19).Address
n5 = 0
Where n5 is augmented each time a row is created then set to 0 to count the next list
For example, I have an excel worksheet which has a main "master" list that has all of the cars that are sold along with the name of the salesperson who sold it all the way to the right (Column 6)
What i am trying to accomplish:
I would like excel to automatically take all of the rows which have the same salesperson name in column six and sort them into separate sheets, but i would also like these rows to automatically update if information is changed in them or if they are deleted on either the salespersons sheet or the master sheet.
I have found this sales forecasting template from the Microsoft excel template section on the web, however, when i insert the new rows, it does not automatically update the "linked" sheets. It is the "detailed sales pipleline management sheet".
View 4 Replies View RelatedI have a large database, and I wrote a macro to update it. However, the sheer volume of data makes it difficult to update at once (without crashing my machine), and I would like to know if there is a simple way to run the macro in increments ---say, 2,000 rows of data at once. Just wondering if these is an efficient way to code this, rather than hardcode partitions into visual basic.
View 2 Replies View RelatedI attached document, in that document I made formula in Pressure cell which is INDEX MATCH MATCH to get the result.
But what I wanted is, when I type 10.4 in Temperature cell, the Pressure cell will show the result which is 1261.24 directly without I have to fill in two temperature cell to make it 10.4.
Below are the equations in 3 cells. They are averaging intervals of 7 cells without overlap.
=AVERAGE(A2:A8)
=AVERAGE(A9:A15)
=AVERAGE(A16:A22)
If I autofill, Excel does not seem to be able to recognize that pattern. Instead, Excel adds 1 to each row so that the fourth cell is as follows:
=AVERAGE(A17:A23)
But rather, the fourth cell should be the following:
=AVERAGE(A23:A29)
Is there a way to autofill with the 7 cell intervals?
Suppose I have an optionbutton named for each day in February, say Feb1, Feb2, Feb3, etc. Would the following code do what I want it to do?
View 4 Replies View RelatedI am trying to make a bill of materials using excel. The material is 10' sticks of pipe. What I want to accomplish is have a column (column A) with the lengths used in inches and have it show me the total amount of pipe used keeping in mind that once you hit 120" you have to start a new stick of pipe.
For example: If I used 40 inches I have 80 inches left over... But if column A read (40", 90", 60") I need it to know that the 40" will be cut from one stick, the 90" will need to be cut from a new stick, and the 60" can be cut from the left over of the 40" cut.
For i = 10 To 260 Step 10
iStrg = i
cntrl = "A" & iStrg
Range(cntrl).Value = i
Next i
I have been trying to use this code which displays 10 in A10, 20 in A20, 30 in A30....etc. What i am trying to do is display 0 to 260 with a step of 10 in the cells A2 to A28, could anyone be of assistance?
I have a column named "Time" and when I enter the data in the first row (for instance 6:00), I would like time to be inserted in the rows below in 5 minute increments.
But as an addition, I would like an input box that requires the end time. For instance, if I enter 6:00 in the first row, I should have a input box requesting me to enter end time. If I enter let's say 8:00, then time (in 5 minute increments) should be entered until time is 8:00.
Is it possible to do this in Excel? If it is, how would I go about it?
How do I write a formula to round the decimal places in a number in set increments. For example;
a) 14.28 to the nearest 0.25 would result 14.25
b) 1.99 to the nearest 0.1 would result 2.0
c)2.97 to the nearest 0.25 would result 3.00
I need an equation that will take time and turn it into quaters increments. Meaning, If we work on a computer for 1 hour and 15 minutes (1:15) then i need it to say 1.25.
1:00-1:15 =1.25
1:15-1:30 = 1.50
1:30-1:45 = 1.75
1:45 - 2 = 2.00
and so on... up to 3 hours.
If E2 = 1:00-1:15 then F2 =1.25
If E2 1:15 then F2 = 1.50
I have a chart which has horizontal gradients in increments of 500. For example, $500, $1000, $1500, etc. Is it possible to make one of the horizontal gradient lines which run across the chart, darker than the other horizontal gradient lines? For example, if a "goal" is to reach $2000, can I make that horizontal line in the chart darker than the lines for $500, $1000, $1500, etc.? I am not talking about the bars that run vertically for each data, but the lines that run horizontally across the chart.
I dont know if this can even be done in a chart or not.
My employer gave me a life insurance "Rate per Age" chart to convert to an Excel spreadsheet.
The chart shows rate increases in 5 year increments starting at age <29 and increasing in steps as follows: 30-34, 35-39 through 95-99.
If I input an employee's birthdate I am hoping to calculate the date when he/she will hit each 5 year step increase.
I'm trying to write some VBA code that can vary an undefined number of variables from a certain value to a certain value and by a certain increment. I know I can do this using For, from to step function but I could potentially have as many as 60 variables that need to be permuted and as little as 1.
For example:
Permute variable 1 from 2 to 6 by 2
Permute variable 2 from 6 to 8 by 1
should output
2 6
2 7
2 8
4 6
4 7
4 8
6 6
6 7
6 8
how to make this code efficient instead of writing 60 for loops? that could either be used or not?
I don't know if title is understandable, English not being my tongue, anyway here's my problem, the current value must be increased or decreased by 0.01 if it's between 1 and 2, by 0.02 between 2 and 3 and so on. I'm really new to VBA and after a few hours I came up with the following code which works fine till the value of 6 and then gets stuck: ....
View 14 Replies View RelatedI'v been trying to do this for a while now. I need a function that takes this date (which is not a set format in Excel, so has to be 'general'):
10.10.2007 00:00
10.10.2007 06:00
10.10.2007 12:00
10.10.2007 18:00
11.11.2007 00:00
...
...
...etc
I need a function that increments this type of date format in a column.
i'm looking for a loop macro to perform the following task:
Sheet 2 is my look up table, and sheet 1 as a number of references such as:
='Sheet 2'!B1
='Sheet 2'!E1
='Sheet 2'!G1 etc
I need a macro that increases the row references "1" to "2", saves the sheet then increases "2" to "3" and so on until the bottom of the reference table is reached
I'm trying to make a line graph with specific increments on both the x and y axis. Now, I can make specific increments on one, but have to sacrifice the other in order for me to label the lines. Im not sure how to get around this. I'll just type out what i'm trying to do and maybe you smart people can help me here.
Okay, both the x and y axis must be in increments of 8,10,12.5,16,20,25,30, and 35.
The first line should be labeled "Indicated magnification, and should have the values 8,10,12.5,16,20,25,30, and 35... the same as the x and y values, making the line a straight linear line.
The second line should say "actual m value" and have values of 10,12.5,15.5,20.5,25,31,37.5, and 44.
The third line should say "actual n value" and have values of 10,13,16,21,26,32,38.5, and 45.
Excel file attached! I need a macro to do the following:
Start with cell F4 and read the increment value from cell C4. Then add this with F3 and display the result. Continue with the same increment until it reaches the value equal to cell B4. Then read the increment from cell C5 and do the same until value equals cell B5. Repeat the same step until it reach the value equals B7. I did manually in the column F4 to F28. Moreover, the cell increment will change according to the variable in A2. In this case it is 25 and got 25 values to fill the column R.