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.
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.
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
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1 List2
Final List
Audi Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example
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.
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?
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:
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'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
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?
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
I'm currently trying to compare all of my companies data sources for staff, ie helpdesk, telephony, ldap etc. and I have made quite a bit of progress, mostly thanks to this forum, however one issue I am running into, is the fact that when I use the text to columns funtion (in a macro) it's splitting up names which are double barrelled into 3 columns. The names are in the format of...
I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.
I'm trying to pull thesum of huge number of columns in this case the range of pulling the data is B2:E7 based on two criteria (B8 and B9) The result is on B11
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.
The final column is the total for each row. Instead of using =sum(a1:a5) then dragging the formula down thus creating a new formula for each line, I want to use a single array formula to achieve the same result. I have tried =sum($a$1:$a$5:$e$1:$e$5), even though all my instincts said it did not look right. And I was right!
I'm sure it is easy, but I just cannot work out the syntax.
