# Questions tagged [array]

The array tag has no usage guidance.

196
questions

**0**

votes

**1**answer

25 views

### compare values in excel and highlight the leading number in B1

how do i compare column A which has repeats data against column B which has numbers and highlight the leading number. since the data has no patterns, not quiet getting it, Please help.

**0**

votes

**1**answer

44 views

### How do I count the number of times a value appears in a 2D array in excel while also having a date condition that varies by row

I'm trying to create a function that calculates the number of times a specific text value appears in a 2D array, while also meeting a conditional that is unique to each row.
Essentially its just a ...

**1**

vote

**0**answers

22 views

### Sum of array in one cell produces different result from sum of the array elements

I want to do a simple calculation (say, addition) on a set of values on the basis of some keys, as in:
Col A Col B Col C Col D Col E
Row 1 **Key** **...

**0**

votes

**1**answer

67 views

### PowerShell: Array of Arrays

I have files with the following structure:
c:\root\dir1\001 (BRP-01 Some) Text.tif
c:\root\dir2\002 (BRP-01 Some Different) Text.tif
c:\root\dir3\001 (BRP-01 Some) Text.tif
...
Ultimately, I want to ...

**0**

votes

**1**answer

55 views

### How to count the frequency of numbers that meet certain criteria e.g. ISODD and > 50 in Excel

What is the Excel formula to count the frequency of numbers occurring in a list against some criteria. E.g. how many odd numbers are in the list, how many even numbers are there with values > 50 or ...

**1**

vote

**0**answers

20 views

### SUM IF with multiple conditions in the same collumn

How to make this work in libre office Calc?
=SUM(SUMIF(Aug.N$2:N$400, {"ED","EDS","CS"},Aug.AD$2:AD$400))

**0**

votes

**0**answers

12 views

### VBA array to copy columns to another worksheet

I have an array to copy data from 1 sheet to another. I have it working for Column A but I dont need the entire range, just Columns A, D to G.
Dim plr As Worksheet, plw As Worksheet
Set plr = ...

**1**

vote

**2**answers

38 views

### Sum Numbers Up to First “/”

I have an array of numbers like this:
114/114
142/137
79/79
60/59
69/69
132/132
1/1
I need to total the numbers before and after the "/", ending up with 597/591. Is this possible in Excel ...

**0**

votes

**1**answer

137 views

### Inserting Array Formula with VBA

I am trying to insert an array formula using VBA but I keep getting the error
"Unable to set the FormulaArray property of the Range class"
Sub Insert()
Range("J2").Select
...

**0**

votes

**0**answers

31 views

### Index with Multiple Returns

I am trying to create a formula that will take a value from A2, A3, A4 etc and search another spreadsheet to return multiple values based on what is in A2, I have managed to create an array formula as ...

**2**

votes

**1**answer

157 views

### Batch: Unable to assign an array to a variable

I am not able to assign an array to a variable:
set persons[0].FirstName=Richard
set persons[0].lastName=DuponT
set persons[1].FirstName=Romain
set persons[1].lastName=DuponD
set x=0
REM Works:
call ...

**0**

votes

**1**answer

126 views

### Keep getting “Index out of range” error

So I primarily use java and C++ and VBA is very new to me. I'm very confused as to why I keep getting error 9 - "Index out of range". Here is the code:
Dim count As Integer
Dim j As Integer
...

**0**

votes

**1**answer

20 views

### Can MS Excel be used to reiteratively check intervals in multiple lines?

I want Excel to check intervals in all the lines in another sheet, finally returning true if my value is in any of those intervals, and fetching a value in that given line if true.
Let's call the ...

**1**

vote

**1**answer

54 views

### comm command output in bash versus zsh

In the process of transitioning from bash to zsh. I am comparing the contents of two folders, and echo the files that are common:
common_files=$(comm -12 <(ls -p "$folder1") <(ls -p &...

**0**

votes

**2**answers

101 views

### results from array formula disappear when data used by the formula is altered

I am using an array formula to calculate cash flow based on length of memberships and total membership value. This is my formula:
{=IFERROR(IF(MOD(DATEDIF($N8,AB$7,"m"),$S8)=0,IFERROR(...

**1**

vote

**1**answer

76 views

### Excel array formula not behaving as expected

I have a little table in EXCEL that uses COUNTIFS to count the number of instances in a dataset that matches four criteria that the user can select from a data validated list in the first four cells ...

**0**

votes

**0**answers

20 views

### troubleshooting an error with a raid 0 array

I have some problems with reading big files from a RAID 0 array of 4 WD Green 1TB HDDs connected to a Windows 10 PC through an Adaptec 6405E RAID controller.
Long story short, I had no problems while ...

**3**

votes

**1**answer

109 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

34 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

**1**answer

17 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 ...

**2**

votes

**2**answers

804 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

19 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

36 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

62 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

51 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

108 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

88 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

21 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

325 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

127 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, ...

**0**

votes

**1**answer

787 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

14 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

**1**answer

18 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

73 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

**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

21 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

67 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

148 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

91 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

265 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

47 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

101 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

510 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

177 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 ...

**1**

vote

**2**answers

6k 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

652 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

42 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

23 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

167 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 ...