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
Using 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 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 have a column of data where the seperate entities are divided by blank rows. for example: i need to calculate sum(G3:G15) then skip the blank row (G16) and calculate the next group, say sum(G17:G50) and export that import to a new worksheet.
The issue is that the column lengths vary from day to day so the blank rows will not be in the same position.
I have a column of contract numbers of varying length. I want to run a sumif of all of the values on my reference sheet that have the contract number beginning with the contract number in my listing. All of the the contract values in my reference sheet are very long. Since the numbers I am working with vary in length, i don't know how to match this string in the sumif function
I want something like this
=SUMIF('Reference Sheet!$A$5:$A$13410,LEFT("", LEN('Select Contracts'!$C6))='Select Contracts'!$C6,'FINALIZED DATA'!$H$5:$H$13410)
Is there a way to refer to the range in the sumif function,
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
I have a workbook with several sheets of varying length (amount of columns). The length of each sheet changes every week. I want particular data from the sheets to come together on one sheet in as organized a fashion as is possible. I'm currently losing my mind copying and pasting every week when I need to update the thing.
View 7 Replies View Relateda formula to extract the numbers into two separate fields. The text may vary in length and the numbers vary in length also from 1 digit to 2,000,000.
Data in A1 is as follows:-
Meter reading Old:1345 New:67890
I have drop drown menus that allow you to select 1 of 3 choices and based on those a second set which lists the options for the first three (subset). Now depending on the second value I need to pull a set of x y coordinates that can be of varying length. '
Example
Drop down menu 1 1560, 1669, 1554. Drop down menu 2 1560-1,2,3,4 1669-a,b,c,d. This has been done now lets say I select 1560-1. I need the following to be pulled from a list, the data is a block.
1.00 1.543
1.23 -1.25
0.54 -2.75
This can be of any length.
I need to find text within middle of a string.
Character before required text is say AAA
Character after required text is say BBB
Text required can vary in length.
Extract text and place in another column.
All text in a single column, required text not in every line. but
does repeat.
I am writing sum of few cells from the row above, to a single cell and then merging the cells.
I need to achieve this in vba but the problem is that the number of columns I need to use in the SUM formula can change month by month, though I know how many columns are there in each month and I am using A1 style to write formula in my code. I couldn't find any way to write formula in vba in such a way that you can change column name to variable (so that first sum covers columns 2 - 6 (or 2 + 4), second covers 7 (OR 2 + 5) - 10 (OR 2 + 8) etc.).
----------A----------B---------C
1-------------------100
2--------11/29----150-------250
3--------11/29----300-------300
4-------------------200
5-------------------300
6-------------------150
7--------11/30----100-------750
Have column A with date, column B with subtotal, column C with Total. The Total (C) is made up of one to five subtotals (B). Subtotals to be included in the Total are those that come after the previous Total up to the row with a date. Only a row with a date has a Total. Is there a formula that can do this?
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..............................
I 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 RelatedI 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.
I'm almost done with the budget at work and now I need to spread the annual figure by month. Initially I was thinking of spreading it evenly by 12 months, i.e. $12k annual budget = $1k per month.
However the boss doesn't want it that way. So I received inputs from certain managers as to when the bulk of the budget will be spent. Now I need to develop some type of formula so that once I input that amount let's say in June, then the rest of the month's will adjust accordingly.
For example, using the example above, if I manually input $6k in June, then the remaining $6k from the budget will be spread out evenly through the other 11 months. Is there some clever way to do this or is just a matter of multiple IF statements?
Have some code I have found from this site shown below. It works great however I need some help in editing it to change what it does. Basically the code currently takes all the data from set cells from all sheets after a set point and adds a new sheet and copies data from each of the sheets between this point and the new sheet and then pastes it to this final sheet.
What I wish to change is I already have a sheet which I will be using to analyse data from so how would the code look to be able to point to such a sheet within the same workbook? The sheet will be within the first 2 sheets of the workbook so from the code below you can see I have already set it to not include this sheet. I wont be needing a new sheet to be created at the end, I just want the data to paste to my analysis sheet which we can call "analysis".
I want to sum a range of cell in a macro. The range can change depending on what is imported. For instance, my range is from (Ax:Ay) and sums this amount on the cell below Ay.
View 9 Replies View RelatedI need to be able to round a given number (stock required) up to the next multiple of another number (pack size), as in the attached example.
Also, is there a way I can round to the closest multiple, ie either up or down, depending on which multiple is closer? I haven't decided which way I want to go yet!
I am trying to auto sum a range of cells, in 3 columns, that varies from sheet to sheet.
I have attached a sample sheet. The columns I am trying to total are columns "E", "F" and "G".
I need some programming help to control the length of the range in the formula below:
View 4 Replies View RelatedMy data is as follows, all in one cell:
10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21
There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))
My result is 9,423.52. Its not pulling the whole number.
What do I need to change in the formula?
I have a spreadsheet that has different product codes that I want to link to their corresponding web pages.
At the moment I individually input the hyperlink into each cell using this common address http://www.ace-sales.co.uk/Catalog.aspx?search=25617 it is just the 5 digit code at the end that changes each time
Can I put a common hyperlink in all the product code cells, then just put the product code in the cell and then the hyperlink automatically links to products corresponding page?
What I'm trying to do is sum the daily interest of a loan for any range of dates. Imagine this example:
1) In column A you have dates from 01/01/2009 through 12/31/2059, with each individual day showing, so literally thousands of cells.
2) In column B you have just random values corresponding to each date, such as $100,000 on 01/01/2009, $139,000 on 01/02/2009, $52 on 01/03/2009, and so forth. These values occupy as many cells as the dates, i.e. there's a cash flow (value) for every date.
3) I want to run a formula in column C that will sum a date range I specify. For example, if I want to sum all the daily values for each month (i.e. the 1st through the 30th), or if I want to sum all the daily values for the 15th of a month through the 14th of the following month. In other words, the loan can start on any date, and I need to sum the interest for each "monthly anniversary."
4) I'm thinking that the formula will have to be different to sum an actual entire month. For example, formulas to sum from, say, the 15th of one month through the 14th of the next month, or the 10th of one month through the 9th of the next month, might be similar.
However, as there is a varying number of days in most months, that formula might not work (though I might be misunderstanding myself) when trying to sum the values for an entire month, i.e. February 1st through 28th. I think it might be solved by some sort of "if," such as if day = 1, then sum whole month, otherwise some from date through same date of next month less the last date's cash flows?
I am try to Set a range to a variable. The problem is the range differs in size. The good thing is there is a blank cell before each group that I am attempting to set the range to. I know it should be possible to set the range to stop at the blank row. I am locating the correct row by the date and want to set the range to include the next three cells to the right and all the cells below them up to the blank row.
View 5 Replies View RelatedI am working on an excelsheet containing a data dump from a manufacturing database. Inside this sheet i want to make subtotals of different production runs. The data that needs to be added up is in one big list between every production run there is an empty cel. So I want to make a formula that does: =SUM(one-cel-above-current:next-empty-cell-above)
View 6 Replies View RelatedI have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.
In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.