I have attached a spreadsheet of data that I would like to reduce.
In the workbook there is a set of data (rows 1 - 24) which are defined with random dates (row 2) and number criteria column A.
In the table below that (rows 28 to 37) I have set out how I would like to present the data. how to create an array formula that sums the above data months into the criteria of column A.
I have a sheet of data that I can filter (e.g. only show male pupils).
On the next sheet I want to display the list of filtered results with no gaps.
As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:
INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))
(However this formula no longer seems to work as I think I changed something).
I've tried to incorporate
SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))
to only show filtered results but with no success.
Progress Tracker.xlsm
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 am trying to create an Array, and have searched through many threads on this website and realise I don't have a basic understanding of Arrays. Specifically the ReDim Array function.
View 2 Replies View RelatedIs there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I have a doubt, in VBScript for excel is possible create an associative array?, for example:
PHP Code:
Dim columnValue(26) As Variant columnValue("A") = 1 columnValue("B") = 2
So if I want to know the value that corresponds to the letter B the result must be: 2
MsgBox columnaValor("B")
Array set for workbooks? I have more than 10 workbooks to update
refer to attachment. the VBA code is in Consolidation SOP.
I have an array which I would like to extract the information and put it in a table in a spreadhseet. Does anybody knows a faster or better way to do it different than using a for next?
View 2 Replies View RelatedAs part of a larger formula, I need a way to evaluate a range row by row and generate an array with the results. More specifically, I need to evaluate whether at least one of the cells in a row has a specific value.
So given the following table:
1
1
1
2
2
1
2
2
3
3
When querying the table for the value "1", it should result in the array {TRUE, TRUE, TRUE, FALSE, FALSE}.
How can I achieve this?
Sub Macro2()
Dim stgSheets As String, Cnt As Long
Dim arrSheets()
Cnt = Sheets.Count
ReDim arrSheets(Cnt - 1)
For i = 1 To Cnt
stgSheets = stgSheets & Sheets(i).Name
arrSheets(i - 1) = stgSheets
'sSheets = sSheets & "", ""
Next i
[Code]...
I have the following problem:
In cells A1 to A4 I have 4 numbers (8,7,12 and 15).
In cells B1 and B2 are the numbers 1 and 4
Now I want to use the max function to find the highest value in the cells A1 to A4. But instead of typing =max(a1:a4) I want to use other cells that tell excel where to look. What I exactly want is to say =max("a"&B1:"a"&B2) because in B1 and B2 the rows are indicated from which to which excel shall find the maximum.
I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how.
Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below)
I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum)
PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", "
I am fairly new to VBA, i require some help creating a Standard Deviation UDF using arrays, i have made a userform that allows a user to select anywhere between 1 to 30 companies (A1: AD1) and its corresponding stockprices (A1:A60) in a sheet called companydata, using the following sub, my problem is that i am unsure how to create a function using the info the user selects. I have also created a returns function (below) but this is vba is saying that there is a out of range error, Will anyone be able to help please.
Below is the code i am using to allow for my sub: ....
I've created a range B3:B12 in which each cell returns TRUE of FALSE. There corresponding values are listed in C3:C12. I have the same thing again in D3:D12 and E3:E12. Checkboxes link to the TRUE/FALSE cells. Attached is a stripped down file to show this.
I want to create a formula that lists each item in the one cell (no spaces required inbetween) for use with a search. I know a long winded way to do this involving many IFs but any array will be much more tidier, thing is I hardly use arrays and have struggled with this one.
I'm thinking it will be along the lines of =IF(B3:B12="TRUE",C3:C12,"")&IF(D3:D12="TRUE",E3:E12,"") but exactly what it should be is beyond me.
EDIT: The original file attached was corrupt for some reason, the new attachedment is the same except instead of TRUE/FALSE cells I've set them to 'x' for quickness (lost my work...!).
Create array set for checking multiple workbook?
[Code] .....
I have this formula, ( which i found the basis of on a You tube video and Richard Scholar was accredited with improving the soloution)
=SUMPRODUCT(--ISNUMBER(F4:AH4)*10^{-29,-28,-27,-26,-25,-24,-23,-22,-21,-20,-19,-18,-17,-16,-15,-14,-13,-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1})
This forumla generates a number for each player, the higher the number the more inline they are to get a game
Problems are this works for the 29 weeks of this year but more weeks need added to the end of the year as we get there. Can i generate the array numbers from a formula and shorten.
This is a sample data ignore row 2( just a count of players) and data actually goes back to 6th Jan
Excel 2007BCDEFGHIJ1Wk21Wk22Wk23Wk24Wk25Wk26Wk27Wk28210101010101010103Player 1111104Player 210001105Player 300101016Player 4010101107Player 50110118Player 600009Player 7111000110Player 8011100011Player 9000012Player 101011113Player 11101114Player 121010115Player 13016Player 141010017Player 15118Player 160101Sheet1
I'm trying to create a single array from multiple ranges... I'm not sure what syntax to use:
Code:
Dim dat4() As Variant
Set r = Sheet13.Range("rsqlassetid")
Set r2 = Sheet13.Range("rsqlparentcat")
dat4() = (r , r2)
I can create an array with multiple columns from a range if the columns are next to each other but in this instance they're not.
These 2 ranges both have the same number of rows and I'm trying to combine them into a 2 column array, but not sure how to make it work without looping, rediming the array and using a secondary array to preserve the data...
I got the following code:
Code:
Function DateiAuswaehlen() As Variant
Dim FileOpen As Variant, i As Integer
Dir Left(KonvPath, InStr(KonvPath, ""))
[Code]...
Basically, I want the function to return an array, but also work when the user does not choose any file.
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
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
Excel 2007, Windows XP
I am concatenating some cells into an array. The amount fields should always have just 2 rounded digits following the decimal. What should change in the following VBA code to achieve that result?
Currently Cells(r, 6) & Cells(r, 7) could have these values:
1.5
24.78945678
45.2341
What I want is rounded values to 2 decimals:
1.50
24.79
45.23
The array is used as an input parameter in a remotely called function module, after logging into the remote system, SAP.
' delim is a | character
' Populate Myarray with data from all rows
' - Only from rows which are not hidden ' 05/23/2008
For r = 1 To row_count 'r is row number
if worksheets("JEMASTER").rows(r).hidden = false then
i = i + 1 'increment myarray index by 1
myarray.AppendRow
[Code] .........
I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.
Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.
How do I make it so that it will select sheets if there are less than 4 sheets present?
I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?
------ A ------------------- B
John123@gmail.com--------Blue
Bill323@gmail.com ---------Red
Sue223@gmail.com -------Green
Sue223@gmail.com -------Yellow
Bill323@gmail.com ---------Red
Bill323@gmail.com --------Yellow
John123@gmail.com ------Yellow
Sue223@gmail.com --------Blue
- C --------------- D ---
John ------------Blue, Yellow
Bill --------------Red, Yellow
Sue------------Green, Yellow, Blue
I am using Excel 2013 on Windows 7. In the above example columns A & B is the given list to process, and Columns C & D contain the result I am trying to achieve. The major part of this that I am having trouble on combining, separating them with commas in another cell, and ignoring a duplicate value. You can see bill has two red values, but I only need it displayed once in column D.
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
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?
View 2 Replies View RelatedTwo part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
I want to use an array formula to sum about 5 rows each of which has 5 columns, e.g.
1 2 3 4 5 15
2 3 4 5 9 23
5 4 3 2 5 19
9 8 7 6 5 35
7 6 4 2 1 20
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.
I have an formula for calculating the factor of a number this a fairly long array formula I decided to try to write this in vba to use as a user defined function. I first decided to record a macro of me writing the formula in excel to see what the code looked like, this I then adapted in another module copying the basic formula in a function procedure. my recorded macro works fine when I run it but my adapted function shows an error namely that the FormulaArray is not defined. I've tried all kinds of variations but with no success. how do I write an array formula in V.B.A.
View 13 Replies View RelatedI have a list in excel that contains the same sku more than once on a
workbook in no paticular order, however i want to bring back the last
time that this sku appeared on the workbook for example:
SKU 271562 date counted 11 september
SKU 275555 date counted 12 september
SKU 271562 date counted 12 september
sku 255212 date counted 13 september
& so on
SKU 271562 date counted 11 october
The date range is only applicable to the date counted therefore i
cannot search via a date
2. vezerid Oct 13, 7:09 pm show options
Newsgroups: microsoft.public.excel.worksheet.functions
From: "vezerid" <veze...@act.edu> - Find messages by this author
Date: 13 Oct 2005 11:09:27 -0700
Local: Thurs, Oct 13 2005 7:09 pm
Subject: Re: Excel help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
The following formula works, but takes far too long for an efficient user to run (note the array):
{=(MAX(IF(($B$4:$B$50000=$B4)*($I$4:$I$50000=$X$1),1*$J$4:$J$50000)))}
B = Number Values (e.g. 3431003)
I = Simple Text Values (e.g. I, P)
J = Dates (e.g. 4/15/2009)
Is there a formula that would provide the same result in a quicker fashion?
Note: Unfortunatly, the data will get up to 50,000 rows.