# Questions tagged [array]

The array tag has no usage guidance.

189
questions

**3**

votes

**1**answer

39 views

### Excel VBA: Application.Tranpose on an array gives Run-time error 438

I have a VBA code that gets data from a column of a structured table and puts it in a column of another structured table like this:
Dim TopNTickers As Variant
Dim NoOfTickers As Integer
Dim ...

**1**

vote

**1**answer

30 views

### Excel Function returning array

I want a UDF that accepts a parameter and returns an array.
Here is my function:
Function UnitSales(ToAdd As Long) As Long()
UnitSales = Array(1 + ToAdd, 2 + ToAdd, 3 + ToAdd)
End Function
I ...

**0**

votes

**0**answers

26 views

### How can I retrieve an array from a separate spreadsheet using a column name as the search criteria?

I have a macros-enabled Excel template. I'm trying to figure out which formula(s) I can include in it in order to achieve the following:
▪ search for column name that exists in a separate ...

**0**

votes

**1**answer

15 views

### Array completely filled with a repeat of the first output value

I used to use that sub where inputs and outputs were on LINE ranges. Everything was working fine.
Then I switched the data to COLUMN ranges. Now the function loops correctly through all the input ...

**0**

votes

**0**answers

20 views

### Loop through multiple rows per iteration with Excel VBA

How can I convert my Excel VBA code, which currently loops by each row, to loop through 10,000 sets, with each set containing 20 rows? I understand that the Step function might work, but I can't ...

**2**

votes

**2**answers

125 views

### In Excel, can I “splice” multiple Dynamic/Spilled Arrays into a single Dynamic/Spilled Array?

IN A NUTSHELL
I have four (say) columns of data, each a Dynamic/Spilled Array. I'd like to "stack" them into another column so as to produce a single Dynamic/Spilled Array comprising the "splice" of ...

**1**

vote

**0**answers

18 views

### Plotting 2 dynamically-sized series on ONE line with Excel

My problem is: with excel, I want to plot 2 series on one line, where the first series has a changing size.
Here is an example to better understand it:
List_1: can be [0,1,2,3,4,0] or [0,1,2,...,15,...

**0**

votes

**1**answer

32 views

### Excel to return matched value in rows from a column array

Super stumped, I am trying to return the values of column based on the value of a cell found in a number of columns. I have attached an image to better describe what I mean. Table So, I'd like to ...

**0**

votes

**1**answer

60 views

### Match 5 closest in same array

I have a list of customers and lat/long coordinates (3 columns:c,u,v respectively) I want to create 5 columns to resolve as the closest 5 customers.
So far I’m using
min(sqrt(([lat1]-[lat_array])^2+...

**0**

votes

**1**answer

41 views

### Can someone help me change this array formula to normal formula?

I am having problems in terms of speed with a file using this formula.
{=IFERROR(INDEX(Query1[COGSDetailSKU.RecipeCode], SMALL(IF(($B$1=Query1[COGSDetailSKU.SKUName]), MATCH(ROW(Query1[COGSDetailSKU....

**0**

votes

**3**answers

97 views

### Excel - Best way to structure lists of lists?

I have a sheet of cooking recipes. Each recipe has a name and a list of ingredients and their quantities. For example
Apple Pie
x4 Apples
x8 Flour
x2 Butter
Scrambled Eggs
x2 Eggs
...

**0**

votes

**1**answer

59 views

### How to count unique value in a table with other condition?

I have this (sample) source of data where I want to count the number of unique Client/ID in total and from a specif location.
I am good at counting the number of unique clients in total using MS ...

**2**

votes

**0**answers

19 views

### Efficient way to concatenate row titles based on column indicator?

I have a list of ingredients in column A, and different products in columns. The products all consists of different combinations of the same ingredients. In each product's column, there are Y ...

**-1**

votes

**1**answer

20 views

### Too many arguments in use if and lookup functions

I am using an IF formula with LOOKUP in order to evaluate and return a value in a table. For example, to return a value in a salary matrix,
=IF(K5>10,G5+SMI!$G$25,IF(I5=1,LOOKUP(K5,SMI!$F$10:SMI!$...

**0**

votes

**0**answers

161 views

### Insert key/value with spaces using variable into associative array and return it to parent function

Have 2 functions, child and parent.
Child function building an associative array, and returning it to another associative array in parent function
Key and values are strings that always vary ...

**1**

vote

**2**answers

50 views

### Countif Date Array Formula

I'm trying to build a sheet for tracking compliance with training. I'm needing to build some summary fields, which show how many procedures a person is current on, how many they are out of date with, ...

**1**

vote

**1**answer

323 views

### SetLocal EnableDelayedExpansion - Array Variable with Variable Index - dynamic variable

Oh the 10K and 20K and 100K will want to call this a duplicate.. but it's not.
The reason for this is merely: Use Array Variable, "arr"
Use a counting variable, "x", or whatever
Compare array index:
...

**0**

votes

**0**answers

12 views

### MATLAB output array

I'm a beginner and I got a Script and I have to out put RES{1} and RES{2}
RES{ii,1} = measure;
RES{ii,2} = measure_filter;
measure and measure_filter will be filled before. But I don't know how to ...

**0**

votes

**0**answers

47 views

### Copy Transpose Paste Vertically Breaking on Blanks

I am trying to copy/transpose/paste values from a one-column PivotTable and a formula column and I need it to paste vertically on another sheet and break on blanks. (see image) I need to copy each ...

**0**

votes

**1**answer

17 views

### Amending values in an array

I want to pull the available disk space from multiple servers and flag the values with low disk space (<20GB for example purposes). I am having trouble with flagging the low values.
To pull the ...

**0**

votes

**2**answers

64 views

### Excel array operation: how to get this array?

I have the following two arrays:
{a,b,c}
{d,e,f}
How do I get this array?
{a*d,b*e,c*f}
For example, one array is {0,1,1}, another is {255,122,0}. I need this array:
{0,122,0}
It is really ...

**0**

votes

**0**answers

86 views

### Excel INDEX/MATCH function difficulty with CSE

I have an INDEX/MATCH function in one of my formulas to look up and match information with multiple criteria. I know that the first time the formula is entered, it is required to use ctrl+shft+entr (...

**0**

votes

**0**answers

11 views

### Excel Array Function Giving error when file is saved as a copy from network drive

I have created a template in excel that used SUMPRODUCT and other array functions. This file is saved to a network drive in which others can save a copy and use in their own work stations. I have ...

**0**

votes

**1**answer

23 views

### Excel - Using Two Data Tables to Build Output Table

I am trying to compare the locations of numbers in two different data tables and create an output summing the total revenue booked per day of the week based off the location of the day of the week in ...

**0**

votes

**0**answers

69 views

### Ask Excel to process the closest sum of numbers to a given number?

I have a table of data that I need Excel to calculate the best possibility given a number. For example in row 2 I have a number of floors labeled 3E-7W. In row 4 I have a number value associated to ...

**0**

votes

**0**answers

18 views

### Nest an Excel Array Function In a Non-Array Function and Return an Array

I am trying to create a spreadsheet that will be used to provide quotes to customers. Some part-numbers apply to a single item. Some part-numbers are bundles of up to 4 items. I am trying to create ...

**2**

votes

**1**answer

66 views

### Adding arrays to a timeline and summing them [closed]

I am trying to add multiple arrays to a simple timeline and then summing those arrays. This is a simplified view:
A = 1 2 3 4 5
timeline 0 1 2 3 4 5 6 7 8 9 ...

**1**

vote

**1**answer

92 views

### Dynamic Arrays for earlier Excel versions

Is there a way to have an array formula "spill over" in earlier Excel versions? I have heard that the new Excel will bring dynamic arrays, but need this functionality on an older version of Excel.
I ...

**0**

votes

**2**answers

72 views

### construct parameters of a script based on dynamic values of variables

Team, I have a set of parameters that are defined inside this script-A with their default values as "DEFAULT". When users set these parameters which are variables to any value other than "DEFAULT", ...

**0**

votes

**1**answer

109 views

### Names of variables are stored in bash array; how to loop over the array and test their values against common pattern?

Team,
I am trying to implement a functionality that check content of all variables in list of predefined variables in ENV by user and then run my function with all the resulted variables as ...

**0**

votes

**0**answers

22 views

### Why are array formulas not filling adjacent cells as they should?

When using formula arrays with multi-cell results, the returned array is outputted in the adjacent cells as the formular arrays (as I've experienced in the past and as documented).
Why is it that if ...

**0**

votes

**1**answer

64 views

### Excel: Array formula to sum values based on matching month and year results in value error

I am trying to sum a bunch of numeric values in
'Product Detail zPart (ALL TIME)'!$O:$O
using formula
{=SUMPRODUCT((MONTH('Product Detail zPart (ALL TIME)'!$F:$F)=MONTH(Summary!C$2))*(YEAR('...

**0**

votes

**2**answers

239 views

### Formula for finding longest string match in a cell from a list and returning the matching string

In an Excel workbook, I have a formula that searches for matches to a cell from a list. It works, but doesn't find the longest string match. How do I modify the formula so it returns the longest ...

**-1**

votes

**2**answers

121 views

### Excel Sumif Array

I'm trying to use an array formula with sumif and know the correct format is along the lines of
=sum(sumif(A:A,B:B,{"hello","hi"})),
However when I try to use > or < than operators, the ...

**0**

votes

**2**answers

2k views

### Excel INDEX MATCH looking between two dates of my choosing

I'm using Excel to log a list of items (name of items in column D) and then how long that item took to produce in column G.
I'm doing an INDEX MATCH to search for the worst performing product of the ...

**1**

vote

**1**answer

350 views

### Using powershell, how two screenshots can be compared in buffer?

I found this snippet that is working well in a loop for me to take screenshot per minute. I could hash results and compare the two latest screenshots for changes by following this post: (Get-FileHash $...

**0**

votes

**1**answer

40 views

### Excel array (2 variables)

I have the following sheet https://1drv.ms/x/s!Ai6fM6lT5gt6h00EUCmpnuwlgNtr
It consists of table 1 that is populated by a list (with an array).
Goal: I want to populate a second table based on the ...

**1**

vote

**0**answers

22 views

### Using an array to output results from a lookup criteria

I've tried searching the site, so sorry if this is a repeat. I'm not sure exactly how to phrase what I'm trying to do in excel.
Essentially what I have is a data sheet and a summary sheet. On the ...

**2**

votes

**2**answers

147 views

### bash for loop with unknown array

I need to execute a for loop over the elements in array edg_cdi, but I know it is that array just because my input parameter is chosen='cdi'. If it was chosen='cdt' (or many others) the selected array ...

**0**

votes

**3**answers

93 views

### Simple Array Formula in Excel not comparing the right values

I'm trying to calculate with an Array Formula (in Cell D2) what i'm calculating in cell D11. The issue i'm having is that it appears my Array Formula is not evaluating the inputted array one-by-one. ...

**0**

votes

**0**answers

205 views

### Excel: Ignore the last value in series 1 if COUNT(series1) > COUNT(series2)

I am currently using excel to do some trading. In one series I have a column of "buy prices" and the column next to it is "sell prices".
I want to divide all of my sell prices by all of my buy ...

**0**

votes

**1**answer

66 views

### Multiple criteria unique values, and list

I have two issues that I am looking for help with.
The first is to create a dynamic list of unique ID numbers for items matching "Horse" in column J on Sheet 2!, "Ball" in column I on Sheet 2!, and ...

**2**

votes

**1**answer

486 views

### Find position of first uppercase character in excel string

I need to find the position of the first uppercase character in an Excel string. There are lots of resources on the web that have the same (or effectively the same) solution for this:
=MIN(IF(ISERROR(...

**1**

vote

**1**answer

2k views

### Pass arguments of .BAT file to executed .EXE file [duplicate]

I need a DOS .bat script that just transfers ALL arguments to a .exe program.
For example the DOS script main.bat that calls the .exe program.exe:
program.exe ????
The question is what ??? should ...

**1**

vote

**2**answers

110 views

### Count max values compared with previous array

I try to count the number of values in a row that are equal are greater to MAX() value of a (part) of the column above.
For example: compared to the 3 cells above itself. In E comes the 'count'.
i ...

**0**

votes

**1**answer

73 views

### Excel - Find the document of a given type, with the closest due date

I am operating on 2 Tables.
"Table 1" is a list of current documents that I import and want to keep unchanged. The file is unformatted in any way.
Table 2 is a list of customers that I operate upon ...

**3**

votes

**1**answer

25k views

### How to count the number of elements in an array in Ansible?

An array is generated in Ansible from shell command output, it is similar to below:
foo: [value0, value1, value2, value3]
Now depending on output of the shell command, the number of elements of foo ...

**0**

votes

**1**answer

870 views

### Return list of all values that match any of a few criteria

I'm new to this site so hope I'm doing this right!
I've made use of @BruceWayne array formula in the answer to the follow question:
Return list of all values that match criteria
My question is, can ...

**0**

votes

**0**answers

65 views

### Moving filtered data into variant variable array

I am currently trying to automate a manual task. I have filtered my data and this works fine if I copy and paste the visible range into another sheet. I am trying to write the visible data into a ...

**0**

votes

**1**answer

565 views

### extract data from SharePoint document library to CSV using powershell

I am trying extract data of SharePoint document library to CSV file using powershell. I am getting data correct on CSv file. But one column i.e "Description" have more text data on it.So when run the ...