What would be the best approach/funcvtion to use in excel if I had an array of variables and wanted to match them?
E.g.
1,2,3,4,5,6,7
I would like to lookup up a column and if either of these numbers are in that row to say "yes otherwise "No"
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
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.
Is there any way to add together a portion of an array without using a loop or for statement. i.e.) I have a array variable: arrayvar = array ( 1, 2, 3, 4, 5)
is there a way to sum the last two numbers in the array without using
arrayvar (3) + arrayvar (4) or a loop or for statement.
I am thinking something along the lines of worksheetfunction.sum( arrayvar( 3 to 4)) I know that this does not work but I hope you can see what I am trying to do
In sheet 1, I have
Col A Col B
John Sedgwy - R
In sheet 2, I have the following names
Col A Col B
Peter Walker
John Sedgwicky
When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}
gave me an error but when I did an individual match to both John and Sedgw, it works.
I am trying to loop through different SlicerCaches but it doesn't seem to work.
Here is the code, I am trying,
VB:
Test_Name = Array("[Test - Test Allocation]", "[Test 2]")
For i = LBound(Test_Name) To UBound(Test_Name)
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")
The code returns a mismatch 13 error.
When I try it without an array, it works fine.
VB:
Test_Name = "[Test - Test Allocation]"
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")
I want to know if I can populate an array with variables..
For example, imagine I've declared 3 variables a, b, c.. and each variable has been assigned a value (which can change)
now I want to say this:
myArray = (a, b, c)
then I want to be able to loop through myArray and retrieve the value attached to each variable..
I have 2 array variables in a block of code which I need to compare to check if the items in the arrays are exactly the same. The arrays are exactly the same size and I can see the contents of the arrays in the watch window.
When I try to run and IF statement as in: IF VARIABLE1 = VARIABLE2 THEN - I get a compile error saying "type mismatch" on the equal sign.
Is 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 need to be able to do a look up in 9 different table arrays using 9 different ranges.
I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.
I am not sure how to make this smaller or work.
Okay, I made an epic fail on a previous post that i turned to Solved, a real error on my part of not fully testing before i posted Solved, basically i was so excited that i'd solved it myself, well partially.
I have the following code in the first sub routine that collects which option button has been selected.
[Code] .....
The second sub routine is per following.
[Code] .......
Unfortunately when i tested this with only one variable myRev(1) it worked fine but when i added more myRev variables 2 thru 4 if failed, so is there a trick to passing an array variable between sub routines?
What I am trying to do is to automatically build a "tree" diagram representing the links in a huge model which is dynamically configured. What I have a problem with is the following:
The tree consists of layers, I start off with the top layer and for each entry in the top layer I can add all its subsidiary layers and draw links between them, this uses a function which takes as its arguments the node name and its layer number (how far down the tree it is) and the number of items in that layer so far.
So I start at the first item in Layer 1 and there are as yet no layers below it. I start at the first one and add the first item in layer 2 then I kick the function off again and that adds the first item connected to item 1 in layer 2 in the layer below (3) starting at the first one, and so on. When I reach the bottom I go up one layer and add the second item in the bottom layer and so on. When I have added all the connctions to the first item in the next to bottom layer I go up one layer and add the second item connected to the first item in that layer and then add all the items connected to it and so on and so on.
In this way I build up the network exhaustively (to make things more complex more than one item in a layer may connect to the same item in the layer below).
I can do almost all that, the issue I am struggling with is I need to keep track of how many items there are in each layer (as some layer 1 items connect to 1, 2, 3 ...8 layer 2 items and so on). my idea is to keep a running total of these in an array LevelCount(1), LevelCount(2) etc. so when I add a new item to a layer I know where to put it. However I cant workout how to do this final step.
Currently I have a function that draws the nodes below a specified node this and takes the correct value from the array LevelCount(n) by passing Levelcount(n) (where n represents how far down the tree you are) into the function and the function then updates the value of LevelCount(n) (ByRef) so that next time I use it it is correct. That is fine but what I want to do is to is to call the same function from within itself when it adds each node which would make it work automatically - it would keep calling instances of the function until it reached bottom and then go back one step at a time to the top but I cant work out how to reference the right value in the array to pass into the second (and subsequent) instances of the function.
I don't think I can simply pass (n) into the function and in the body of the function set LevelCount(n) = LevelCount(n)+1
I also dont know ahead of time how many layers the model will have, nor can I tell which layer a node sits in as it depends on the links that are dynamically configured.
Beyond this a node can also be subsidiary to nodes in more than one level so it needs to sit at the lowest level - but I suspect if I can work out how to do the first bit i can do this too.
I am trying to construct an indirect array reference as follows;
Dim A10 As Variant
Dim A11 As Variant
Dim A12 As Variant
For i = 0 To 2
String1 = "A" & (10 + i)
String1 = Cells(6, D1_CNum + i).Resize(2995, 1)
Next
But the above doesn't work, what would be the correct way of creating a reference to an Array using variables to contruct the name of the active Array?
I was hoping String1 would equal "A10", so that on the Cells command Array A10, A11 and A12 would be filled using the value of String1??
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 trying to pass a public variable to another module in the same workbook. On Module1 I declare at the top
Public stores
Sub Main()
Dim stores(1 To 100, 1 To 10, 1 To 10)
Reader
...which then calls the procedure Reader in Module2
Sub Reader()
let x=1
let y=1
let z=1
let stores (x,y,z)=activecell.value
I've left out the portions of code that seem irrelevant. When the macro runs, I get a type mismatch error on the "let stores" line. If I move the code from Reader into the procedure Main, it works, so it seems to be an issue with passing the variable. I haven't used multiple modules very often so this is probably a very basic issue.
Excel 2007.
I'm basically copying and pasting a bunch of columns. Currently, my code is very long because I'm not using a loop to plug in the column header. how I'd store all the column headers in an array (I think) and loop through 1-by-1.
Here's a look at what I'm doing now:
Code:
' ''Project Number
x = "Project number"
i = Sheets("RawData").Rows(FirstRow).Find(x).Column
[Code]....
how to store these column headers in an array and then pull them?
I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:
Private Sub Weeklytestt_Click()
Dim varCurDate As String
If DateYesterday.Value = True Then Answer = "1"
If DateToday.Value = True Then Answer = "2"
If DateTyped.Value = True Then Answer = "3"
Select Case Answer
Case Is = "1"
varCurDate = Format(Date, "yyyymmdd")
varCurDate = varCurDate - 1
Case Is = "2"
varCurDate = Format(Date, "yyyymmdd")..........
In my VBA project I've declared several public variables as normal (ahead of
all procedures) ... Public simolo() As double etc. They are declared in normal modules, and arent declared twice. I set the values in one procedure and then execute a second procedure but, when the variable is encountered in the second procedure, it appears to be empty and I get a "Subscript out of range" error. Clearly, the public variable isn't public since no data is stored in it.
What is going on?
I'm having problems with the MATCH function. If I use =MATCH(A2,'c:myfile.xlsx'!MyRange,0), it works. A2 is the value that I want to look up. 'c:myfile.xlsx'!MyRange is the full path of a range in an another XLSX.
However, if I assign the file name to a cell and try: =MATCH(A2,A3,0). where A3 has the exact same path as the line that work, Excel returns #N/A. I also tried a match on a range in the same tab of the same spreadsheet, and it didn't work. Is it possible to do what I'm trying to do? The file location of the external XLSX is going to change, and I wish I could just update one cell rather than every single cell that contains a MATCH.
I have a query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.
e.g.
Main Data Table
Color Operator
005--325 005
004--326 004
003--327
001--328
002--322
Lookup Color
001 - Red
002 - Blue
003 - Green
004 - Violet
005 - White
Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6
I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.
Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
ExampleFile.xlsm
I have a worksheet that has 6 columns of numerical data that all column
datasets are referenced together for each row. So for example the data
in row 5 in all colums is related.
Column C through F have 4 numbers that refer to the data in the first 2
columns. These are also ordered by row.
I need to look in column A for 0.00, and column B for 3.14 Both of
these must be in the same row or I need the closest value. When these
values are found in columns A & B, return the values from the same row
in Columns C through F.
I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.
Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.
The short version:
(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))) evaluates out to series of 340 True and Falses. I need to be able to pull the nth True, and then somehow index that against AH2:341.
I'm working with a record of 53,000 entries with latitudes and longitudes and I need to cross compare them with 341 locations in the United States.
I've already done some of the heavy lifting, which supported narrow an initial list of 85,000 records to at least recognize which records where within any of the 341 points. (Less arrays makes my computer happy.)
This formula gives me the count of locations each entry is within one of the 341:
=SUM(IF(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25,1,0)) {CSE} & copied down 53k times
The actual return range is AH2:AH341 for the location number.
Now, I need to be able to list each of the 341 they are close to. The max count is 17, so I'll only need to list out 17 columns.
Maybe something like =LOOKUP(1,1/(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))),$AH$2:$AH$341)
but this only returns the first match. How do I return the 2nd, 3rd, 4th, etc?
Because of the sensitive nature of my data, we can use a much simpler array which I'll adapt; I'm mostly trying to understand the logic or formula that should be used.
Return Nth Match.xlsx
column A
MARY
JOHN
MARK
SUE
cell B1
zzzJOHNxx
I am looking for a formula (Vlookup, Match, etc) that will look for an entry in column A that matches part of the string in cell B1 (not the other way around i.e. using wildcard)
If I have to match MAX from Row 1 and MIN from Row 2 and get the EXACT MATCH as well as more than 1 Match?
I have 3 Rows and 4 Column Matrix
from A1 till D1 contains values as 10,20,50,40
from A2 till D2 contains values as 30,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES
THen my answer would be HARRY, however if there are more such combinations present then I would require a list of all of them.
Ex2 For Multiple Lookup
from A1 till D1 contains values as 50,20,50,10
from A2 till D2 contains values as 10,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES
I matching up some ip numbers against some ip ranges and I'm finding I can only use the wildcards in the first argument and not the second array argument. Is that the case? Here's my files:
IP_List
141.188.146.000 =match(A1,IP_Range!A:A,0) Doesnt work
156.140.216.202
IP_Range
141.188.???.??? =match(A1,IP_List!A:A,0) works
I really need the first case to work (looking up an ip in the ip range). Is there a way to do this?
I have the following array formula that identifies the first number in the column I:
=INDEX(I1:I1200,MATCH(TRUE,ISNUMBER(I1:I1200),0))
This formula works fine however I was hoping to expand the range to the entire column. I thought (obviously incorrectly) that the following formula would work:
=INDEX(I:I,MATCH(TRUE,ISNUMBER(I:I),0))
Instead of getting the same result as the first formula, I am getting "#N/A".
=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))
I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.
Can an offset be added to an index?
I created an Index/Match array forumula in the worksheet. It works.
View 2 Replies View Related
