I have account numbers in column A. I have 2 columns which contain account range.For example column/cell f1 contains 45555555 and column cell g1 contains 45666666. I have to check if the account in column A is in this range, and if yes ,I have to have "klo" in column B.
The problem is , that I have to check a lot of rows (and I do not mentioned but I have to check other attributes with the same structure as well) and the do- loop-if structure is rather slow. Could you tell me how can I solve this with say arrays-or with other method-which can boost my macro?
I need to know if there�s a function (or method) in excel that�ll allow me to select an array of numbers and once those numbers are selected, to use only the numbers (for an averaging function) that yield the best sigma?
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.
If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.
I have 1-d array that gets populated from a number of sheets. This array may contain duplicate entries... for example, let's say this array contains animals.. cows, chickens, horses, pigs... etc. What I would like to do is take this info from this array and create another where it would contain all the different types of animals but without all the repeats.
I need that array interpolated to say 20 data points but the first and 20th data point must be the actual first and last data point of the array (1 & 9 in the example). I have downloaded the xlfun software but the interpolat function only does 1 point at a time...than as you drag the function down it changes the initial array. Of course I realy have anywhere from 40-60 data points to be interpolated to 100.
Lets say I need an array of 100 numbers from zero to 100. I want a mean of 75, and SD of 12. Or the same data based on a skewness and kurtosis value. Can I do that in Excel?
I'm just generating fake datasets for my stats class to analyze.
I have an array with an ubound of 10,000 because I will be loading a different amount of numbers each time I run the macro. Is there a way to tell the amount of digits filled inside the array if it is less than 10,000?
But when in column "A" is "Yes", I want to store in array each value within cell in B (in this example B2) to apply later a For/For Each over each number (in this example are 3 values only within cell B2), something like:
I'm using the vlookup function to pull numbers off an array. Is there a way that I can get the vlookup function to pick the smallest number greater than or equal to my Look_up value. If not is there a function that can do that?
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
Application.ScreenUpdating = False Dim c As String Dim d As String
[Code]....
this works perfectly but I have found one problem, I want excel to find on the existing arrays "0,00" and "0", only full match and not only partial like for example:
-154.09, which triggers "0" for the second array and 120,00, that triggers "0,00" how to I change to only find full match with the existing arrays?
In a formula on another page I want to add together columns A and B, and then take the minimum of those additions.
So in the case above the minimum value I am after would be 3. This is because on R5 3+0 = 3 which is the lowest number of the additions.
I do not want to add a sum column in the source table and then take the minimum of that as I dont have great access to the source table.
Inside my formula I need to add them then using the min function grab the minimum value of them all.
I have tried many combinations of the MIN, SUM both in Array functions and not in array functions but cannot nail it. Getting the minimum of 1 column is easy - I am finding getting the minimum of the addition of two very difficult.
I should add that there can sometimes be 8 records, sometimes 10 records. So the formula needs to be written to ignore blank cells, but include cells containing 0.
In the first row of my table are the targets for each period of the year and in the second row is the actual performance. The targets for the year will be populated throughout but only periods so far will have a figure for the actual performance.
following is code for search in the sheet and give result in other sheet
Private Sub Searchbton_Click() 'Serach button click 'for search Dim a(), r As Range, res, i As Long, ff As String, rng As Range Dim title title = Array("Sr.No", "File#", "Client File#", "Client", "State", "Borrower's Name", " Date Received", "Date & Time File Started", "Title Ordered on", "Type of Search Ordered", "Title Follow up Date", "Title Received on", "File Completed on", "Date & Time E-Mail Sent to Client", "Status", "File Canceled Date", "Remarks", "Done by") res = Application.WorksheetFunction.Substitute(Me.TextBox1, "-", "") If Len(res) = 0 Then..........
Problem is when we search for any data and if the recorded data exceeds 307 or more rows, the result will not show the data beyond 307 & more. it take to the Error : Run-time error '13' :Type mismatch error and cursor goto:
In the attached file on the Final Lists tab I have an Array formula in each column. My problem is that I need to extend this to cover approx 500 columns. Is there any way that I can cut'n'paste or drag the formula across all the 500 columns, have my references increase with each new column, but also retain my $ references (ie my formula format)?
As it is, I am facing having to either type or correct each and every one of the 500 columns. As you can see, I am a little bit of a newbie when it comes to the intricacies of excel.
Having trouble getting Excel to compute a value based on two columns on another sheet. What I need it to do is see if column A has the value I specify, then look in column B and see if that date figure is greater than 180 days, and then count it. So I might have 45 that meet criteria A, but then need to find items in that subset that meet criteria B...
I want to use following array formula: {=SUM(SQRT((0.004)^2+(B$2:B10-B$1:B9)^2))}
on columns B till U. In the case of column B, the end of the column is at row 10. However, the lenght of the columns varies, so row 10 won't be the end of the column for column C. So for column C the above formula doesn't work anymore. The beginning of all columns is the same, hence the '$'. Is there a way to change 'B10' and 'B9' in this formula into 'end of column' and 'end of column minus one'? Cause I would like to use the same formula for all columns.
I'm trying to sort an array which is of variable height/rows when put on a worksheet. I need to sort by column C ascending (smallest to largest) and then by column D ascending (oldest to newest). I tried recording a macro but the code is just junk.
Code:
Dim BookSPSortingWorksheet As Worksheet Dim TR As Range Set BookSPSortingWorksheet = ThisWorkbook.Worksheets.Add Set TR = BookSPSortingWorksheet.Range("A1").Resize(UBound(array_book_SPNames), UBound(array_book_SPNames, 2)) TR = array_book_SPNames
Is there a way I can use the sort function like the below by indexing my TR range for columns 3 and 4?
code for an assignment, I have a table of values, the 5 colums are arrays, one with 20 student names and the other 4 are grades for 4 subjects, each colum being one subject.
I need code to find the average of each student and get it to display in a separate colum and averages for each subject also getting it to display in another row.
I have a download from an accounting general ledger which has the following:
Column A: Category Description Column B: Country Column C: Department Row 1: Months Note: Each row contains the last 12 months worth of costs
As the categories/criteria can appear multiple times, and there are thousands of lines, I have been using SUMIFS to calculate totals my required combinations (eg. Travel expense, Germany, Sales department)
Every month, the information refreshes to show the most recent 12 months worth of data. And this means I need to manually update my formulas to correct the month column headings, as everything moves by 1 month.
Would something like a SUMIF with a SUMPRODUCT work? Eg. if current month = July, then it would sum everything from the July column automatically?
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx