Most excel/vba books say not to use the sumif function.
I have a very large workbook (9meg) and cant use VBA code as it slows down the interation calculations required.
I have used the sumif function to find and collate unqiue data on different worksheets.
Is there another function that i should be using?
I have a few hundred multi sheet workbooks that have address data in one of the sheets that I wish to collate into a single worksheet to use as a mail merge with word.
View 5 Replies View RelatedI wrote a custom find function to search for a list of numbers across a bunch of worksheets (6 in total). A cell reference is sent to the function and it basically returns the worksheet name and address for the first occurence, or nothing if not found.
Now I realise that the number of cells that need to be checked is fairly severe across 6 worksheets (finding 65 numbers * 65536 * 256), but this still takes about 1 minute to execute... is that about right? Code was hacked together quickly and is nothing special...
Public Function My_Find(Optional my_range As Range) As String
Dim ws As Worksheet
Dim my_cell As Variant
Dim result As Variant
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies View RelatedI am using the following formuale to pick up a numeric value (from column G in Feb09 sheet: =INDEX(Feb09!G:G,MATCH($A10,Feb09!$B:$B,0))
Trouble is, the match it's doing in column b in Feb09 is listed twice in the sheet, but i'm only picking up the data for the value in the first line ... i think i need a sumif function.
I am looking to collate Data of Peoples Activities from their status and duration. I would like to have a table where I would be able to see each agent name, their status and how long they took in each one. e.g.
Name | Status | Total Time
Humpty Dumpty | NotReady | 0:15
| Wait | 0:12
I have attached a sample worksheet of data, if anyone can guide me as to how i can do this also if this can be done through a macro, that would be alot easier as i have to collate atleast 200 peoples activities.
I would like to put all my data from many workbooks into one workbook. all the columns will be the same but the rows will be different.
If possible I would like the file name in "f"
The file location on the file is C:Userssteve.kirkDocumentsbompartsdn. this is where all the files are
CQ821-004 Earth brush bracket - Fabrication.xlsx‎
I always copy data from all tabs to a single tab. I am looking for a macro which does this and saves time. Criteria is Data starts from Col B to Q not further in all tabs, but the rows will vary in all tabs.
View 13 Replies View RelatedI developed a database for tracking our Search and Rescue activities on an annual basis; this usually involves ~140 calls per year. Amongst other things, we log the time the call came in. The time is entered in numbers format, not text, as I couldn't figure out how to get a time range (09:30, etc.) to work. It's like military time...0015, 0945, 1730, etc., so in numbers format, 0930 comes out as "930", 0030 as "30", etc. I developed a tracking column in one hour increments...0000-0059, 0100-0159, etc. Each time a call is entered and the time logged, I'd like to keep a sum total down in the time range column...so a call received at 0930 would end up in the 0900-0959 box. Among other things, I tried formatting with, "=COUNTIF (range:range, ">0900, <0959") but that doesn't work.
View 3 Replies View RelatedI have multiple excel files with a form popping up whenever these files are opened (form has been called in Workbook open function). I need to collate data from these multiple excel files without opening these files. Can somebody help with a sample code?
View 9 Replies View RelatedI have two columns of data one which #N/A appears due to formulas often and the other a set range of numbers 1-60. Column Q contains numbers between 1-60 and #N/A when the lookup formula doesn't find what it is looking for. Column R contains the numbers 1-60. What I need is to place into another column are the numbers in column R that don't appear in Column Q.
View 2 Replies View RelatedI have a data on sheet1 which goes from A2:T unlimited, I already have a macro that enables me to pull data from A2:T2 that then collates to sheet2, but I need it to then go to the next populated row which will be A3 and collate the data then progress onwards until final row.
View 2 Replies View RelatedEvery month we collect data from a number of employees and average it out in a new excel workbook ready for analysis. Each employee sends in their monthly data, structured identically, with only the numbers in the data tables different.At the moment, we just copy and paste every single data return into the separate worksheets of a single workbook, and on the front page of the workbook we create a simple formula which adds the equivalent cell in each sheet together to get a total. Its not hard to do, but its very lengthy as a process.What I've been researching, is the possibility of a macro that can automatically send the outputs of separate workbooks into one central workbook, so that we don't need to go through the copy and pasting of each return every month.
E.g, Say I have 10 workbooks, named Book1, Book2, Book3 etc, which each have a number ranging from 1 - 10 in cell A2. Could A macro be designed to bring all those values together into cell A2 of an 11th workbook, e.g one entitled book11? I'm aware that said Macro may need to exist in each separate workbook in order for the process to be carried out.
I'm trying to collate data from two sheets (sheet1 & 3) , paste it on sheet5 and do some calc. But the macro keeps copying the same row after 2 iterations. Also the find function stops working after 1st iteration. Not sure what to do
Sub Macro17()
'
' Macro17 Macro
'
'
Sheets("Sheet1").Select
Range("B3:L3").Select
Do Until Selection.Row = 10000
Selection.Copy
[Code] .........
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".
How do I create code that will copy and paste 30 rows of data
Copy from filename "Record1", worksheet name "CA3M", in column A and B, and Paste data in filename "Record2", worksheet name "CA3M"
- Column A 30 rows of data paste into A20:A49 of the new file and worksheet
- Column B 30 rows of data paste into E20:E49 of the new file and worksheet, prior to pasting these results remove all dashes "-" eg.0-0-1 = 001
The location of the 30 rows of data to be copied changes, so the code should always look for the following information in the row before and after the 30 rows to copy in column A and B:
- Find the 30 rows of data in Column A and B where the first row immediately follows the row with the cell result "Account Date" and the last row is immediately before the row with the cell result "Previous Page Page".
Also, I will probably have to update the code to repeat this function for several other worksheets within these 2 files after my database is completely built.
I have a workbook with 32 worksheets. Data on 31 of these sheets all filter through to the one "Summary" sheet. The data on all the other worksheets is input manually and the lay-out is identical on each sheet. What I want to do is a Vlookup on the summary sheet for every sheet in the workbook, but without typing VLOOKUP(A1,Sheet1A:C,3,0)+VLOOKUP(A1,Sheet2A:C,3,0) etc etc (my sheets aren't actually called Sheet 1/2/3 etc, they have specific names).
View 4 Replies View Related- I copy some records from a Database into an Excel sheet in workbook (say W1) in the Worksheet WS1.
- The i look up for values in a column (say column B) of W1 in another worksheet in another workbook(say W2) .
- If i find a match in Workbook 2(W2) ,Worksheet 1 , Row 2, Column A ; i look at the values in the Column B, F,G on the Row 2 itself.
- Each of these values can be found in the corresponding Worksheets 2;3;4 in the Workbook2(W2) .
- Then i need to pick up all these values from worksheet 1;2;3;4 in W2 and contatenate them and put them in the Workbook W1 against the Row .
- Similarly i process all the non zero rows in the workbook W1.
i've seen variations on a theme of this.. but have tried adapting some codes with no success.
But i'm convinced this is a common need / request.
i want a summary sheet with used reference numbers in column A.
I'd like cumulative costs in column B.
The costs to build up the cumulative value will come from any number of worksheets in the workbook... they will have the same reference protocol in column A with the costs per period in column B.
I'd like the summary sheet to run a cumulative sumif over the "other" worksheets and provide the cumulative costs..
and also.. in a nirvana type world.. to add any new references on any of the "other" worksheets to the bottom of references in the summary list and to run the cumulative sub against those too.
I'm trying to create a database which can analyze data for my thesis.
The idea is the following:
I have all data saved on multiple work sheets in one workbook.
I would like to use the find function and when I search for specific data this is either summarized in a new sheet or else can be printed.
I have made up 7 worksheets that are completely identical with exception of course to the data they contain. I have a master sheet that contains some of the data from each of the other sheets that will be shared between them. Things were working very well until I discovered that some data was duplicated on a couple of the sheets; and I am in need of adding more worksheets.
The formula I am using for this will not allow me to have more than the 7 worksheets. It is an IF statement array. So now I am wondering how will I be able to add these extra sheets. I would like to use my worksheet names in a range but have evidently done something wrong and could not get that to work before, ending up with this very long 'IF'. !Can worksheet names be used to create a named range?
Also, I would like to know if there is a way to prevent duplicate data on these sheets. I am using conditional formatting on each sheet now, but that of course only works on the current sheet.
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
I am trying to used the sumIF function in refer to a particular value, e.g. Name 1.
Question 1 - What formula I can used to make sure the SUMIF function refers to that specific value, e.g Name 1?
Question 2 - Is my SUMIF function correct to calculate the SUM of hours to a Task? This is due to the data table setting? find attached:
I am using Excel 2003. Worksheet named: Table_Data has the following code in cell: B2
View 3 Replies View RelatedSum amts based on first left character of code
code amts
102 100
200 500
101 100
results
1 200
2 500
I'm having a little problem with the function Sumif. I have data like this:
Date
0
0
Total
0
78[code]...
I want to some all the Totals that appear before the "End Notification" text. I have more totals after that text and the number of rows are variable.
In my data, I have always 4 totals before that text, so I thought perhaps that the way to do this would be to impose to the sumif function to stop summing after counting 4 totals. I didn't find a way to do that.
I thought a little more on the problem and I find out that the easy way would be to record a macro with the sum if function, where, in the range part, I would do a Find for "End Notification"; and select all the cells to the top of the sheet. The problem is that the macro dont record that part, only the row number.I think I need to turn the range a variable, where the variable is equal to the find selection.
I'm trying to create a sumif formula that finds the column the information is in that it needs to sum. I have attached a copy of the file I'm trying to create a formula to get results that I want.
Generally you would normally use the "=SUMIF(range,criteria,sumrange)". I was wondering if there was a formula I can substitute "sumrange" to also search a range of data (Eg: "G16:DA28"). I need the range to be dynamic as the column to be sum can change based on the criteria/option button being selected from the product category. I assume there is an indirect function involved, but I am not familiar with it. If you have a better and less complicated formula please suggest.
I’m using a SUMIF function to evaulate some date. The second argument in the function, the criteria, is that the numbers to be summed are greater than 25 but less than 50.
View 2 Replies View RelatedI want to add a twist to the sumIf-function, and need some help. My request is similar to lots of other sumIf-issues posted here, which were resolved with the sumproduct-function, but is a little bit different.
There is a function sumif() that is used for do sum() under condition
Nevertheless, if I want to do max() under condition, how can I do?
There isn't a function maxif().