# Questions tagged [array]

The array tag has no usage guidance.

**0**

votes

**3**answers

41 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

28 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

43 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

96 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(...

**0**

votes

**1**answer

72 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

96 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

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

**0**

votes

**1**answer

333 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

98 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

43 views

### Write Dates into array / Stop loop when reaching empty cell / Excel VBA

I am fairly new/coming back to VBA after ~10 years of Hiatus.
Now I am faced with the below Problem:
I would like to write Dates from a column in worksheet1 (say Colum B) into an Array until I reach ...

**0**

votes

**0**answers

57 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

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

**0**

votes

**1**answer

100 views

### Hashtags are coming when export data to CSV file by powershell script

I am running a powershell script for getting data from SharePoint document library. While run the script, data came to csv file, but columns names are coming with ";#" hashtags. For example, names, ...

**1**

vote

**2**answers

22 views

### LookUP Value returning multiple concatenated values

I have two columns, one is a 'Category' with a simple code and the other column named 'Tree/group qualities' with one, or many of the following categories that are, if multiple, concatenated using ...

**1**

vote

**0**answers

40 views

### MAX Result of multpiple results from INDEX and MATCH

I am working with 3 columns, fname, lname, & date. Where there will be multiple duplicates of fname and lname. I am trying to write an INDEX & MATCH that will display the MAX result or the ...

**0**

votes

**2**answers

62 views

### How do I use a conditional array with more than one condition to calculate percentiles?

I have a table in excel with the following data:
+-------------------+----------------------+----------+
| Contribution Type | % Contribution Match | % Salary |
+-------------------+-----------------...

**0**

votes

**0**answers

22 views

### How to display rows based on column criteria in Google Sheets on a separate sheet?

I have a Google Sheet I'm working on with around 98 rows that will constantly be changing. One column is an ID number that assigns a positional rank and populates other sections of the sheet. What I'd ...

**-1**

votes

**1**answer

30 views

### Excel check formula, return specific text on error

I have an excel sheet that uses an array to return the most common text found, and then the result is copied to a separate sheet so we can view the most common text per month of the year. This sheet ...

**2**

votes

**2**answers

41 views

### Using GCD Function With Arrays in Excel

I'm having trouble determining GCD from a dataset. I'm trying to determine the quantity of items in a box of any given item in a warehouse without physically looking at each item on the shelves.
...

**0**

votes

**0**answers

27 views

### How do I enter values instead of cell range into the Matrix Functions?

I have a simple system of equations that needs solved repeatedly, based on slightly different input each time. The user is comfortable with Excel, and solving a system of equations is easy within ...

**1**

vote

**2**answers

112 views

### Returning a value that is CLOSEST to some value found within a large range of data

I have one column A1:A20 whose values I need to find within a large table of data.
For the purposes of this example, I'll say that the data table ranges from D1:Z25.
Ideally, I would have an Excel ...

**1**

vote

**1**answer

75 views

### Index/Match for an entire table rather than a single column

I am looking for a formula that will locate an exact match of a value somewhere within a table of given values.
Table d,e,f contains the differences between values in columns a and b, but that's not ...

**1**

vote

**3**answers

219 views

### Creating “temporary” array formulas in Excel

I have a chart that should have a recommended line going through it. I would normally do this by having a "recommended" value for every value I chart. Is it possible to have a formula that repeats a ...

**2**

votes

**1**answer

49 views

### Excel Formula To Show Top X Percentage Of An Array

Using the simple data set below, starting in A1, with headers:
Order Time
a 1
b 1
c 2
d 4
e 4
f 5
g 6
h 7
i 500
j 600
I want to know in ...

**1**

vote

**2**answers

231 views

### How to find the average time from an array in PowerShell

I am referencing a list which has a lot of data in the form of the elapsed time of a process and I'm trying to convert it to a format where I can find the average amount of elapsed time.
The data ...

**0**

votes

**1**answer

84 views

### Get column header of first cell of longest consecutive set of cells below a given threshold

I have a formula that calculates the length of the longest group of consecutive cells in a row of data that fall below a certain threshold.
I would like to show the column header of the first cell of ...

**1**

vote

**0**answers

94 views

### Excel : How to write everything I want on a single array formula

On Excel, I have a table with many numeric results on B column obtained by a array formula.
For example:
+---+------------+-------------+
| | A | B |
+---+------------+----------...

**0**

votes

**1**answer

763 views

### Array Function Only Return First Value

I'm trying to use an array function in excel, the formula is to return the value of filtered table.
this is my formula.
{=INDEX(A:A,MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A31)-ROW(A2),0)),ROW(A2:A31))))...

**0**

votes

**0**answers

62 views

### Comparing two Arrays of dates when using INDEX, SMALL and ROW to get nth matches

I have a range of dates and then a large list of emails, which all have send dates. I'd like to have a formula that finds the first match between the two arrays of dates, and then copy this down and ...

**1**

vote

**1**answer

30 views

### Excel - N/a In cell causes formula searching range to break

I have a formula in excel checking a range and returning only cells which contain Monday.
However, some cells in the range have an #N/A and this is causing my formula to break.
If I remove them ...

**0**

votes

**1**answer

1k views

### Bash- Storing key value in variables declared in Associative Array

I'm trying to create an associative array in bash containing the name of a file (a video) and its duration. The code is :
#!/bin/sh
shopt -s nullglob
movie=(*.mp4)
declare -A MYMAP[$filename]=${...

**0**

votes

**2**answers

276 views

### Checking if sum of arbitrary amount of cells in a column matches a cell in another

The result I would like to achieve is Column 'C':
To make this spreadsheet easier to copy/paste into your own, here is copy-pastable data:
ID
1
2
3
4
5
6
7
8
9
Number
1000
300
800
300
200
ID
1
1
...

**0**

votes

**1**answer

45 views

### Batch script to fetch each line from a .txt file as a string (more than one word in a line) and google search each string in a separate tab in chrome

I've a controlfile.txt file in which I have some softwares names in each line, e.g. "Microsoft Office" in first line, then "Autodesk AutoCAD" in second line, "Adobe Acrobat Reader" in third line and ...

**0**

votes

**1**answer

373 views

### Need an array index/match formula for multiple duplicate results

Here is the effect that I am attempting to achieve, in Column C:
I am trying to make Column C "detect" which entries from D/E are present in A/B. I think one of the problems is that I am not allowed ...

**0**

votes

**0**answers

414 views

### AHK: Loop thru Associative Array

So after some extensive searching both in the AHK documentation, stackexchange and the ahk_forum, I have decided to seek help from the experts
I will explain the code I am working on and then post ...

**0**

votes

**2**answers

43 views

### How to convert a string of comma-separated values into an array

I have a several cells with strings such as this one:
"John,Carla,Peter,John,Bob,Chris,Carla,Andrea"
I would like to count how many different people are named (i.e. how many unique values). The ...

**0**

votes

**1**answer

91 views

### Bash - check for multiple elements in array

I'm just looking for a proper and more elegant way of writing the following code in Bash:
in_array() {
local somearray=${1}[@]
local somevalue=${2}
for i in ${!somearray}; do
if [[ ${i} == ${...

**0**

votes

**1**answer

1k views

### Find a Wildcard Match in an Array with PowerShell

I have a list of users I want to do an ActiveDirectory lookup on but I don't want to do a lookup on System accounts that pop into my list. My list I am checking against will have the fullname of user ...

**1**

vote

**2**answers

526 views

### Excel - array function to remove spaces

I have a column of text items that can contain spaces (empty cells) in between items. On another sheet, I'm trying to use an array formula over the existing range to filter out those empty spaces.
...

**1**

vote

**2**answers

48 views

### Use Excel formulae results in array for nested function

In Excel, is it possible to have multiple formulae slot together into an array for a nested function?
Consider
=SMALL({10\15\20\25\30},1)
The formula returns the smallest value in the array. Now I'...

**0**

votes

**1**answer

95 views

### Excel Multi Conditional Formatting Questions

I need a formula that will look at the text in one cell and compare it to three other columns and highlight it a specified color.
Here's the example:
Data
Cell A2 contains "This is the sample text ...

**1**

vote

**2**answers

221 views

### Excel: using a formula to only sum from above row until next blank cell?

I'm a bit of a noob so bear with me. I'm making a spreadsheet of various products bought and sold at various prices. The table is sorted by product, with a blank row separating each complete buy-sell ...

**0**

votes

**2**answers

204 views

### Return count of days in month using array formula

I have a range of dates in a column as such:
01/03/2017
31/04/2017
06/05/2017
...
It is required to count the number of days which fall in a certain month, say April. I believe this is possible by ...

**2**

votes

**1**answer

84 views

### Average by month within multiple bespoke date ranges excel

I have data by month from January 2013 -> October 2017 as such
MONTH MY DATA
Jan-13 45.0
Feb-13 23.0
.
.
.
Oct-17 98.4
I wish to average data by calendar month, for specific, non-...

**0**

votes

**4**answers

88 views

### Multi-cell array furmulae, nested

My data is on the first 2 tables (A1:B6 and D1:F6):
My intended final result is on I1:I4 for every corresponding value in H1:H4.
For instance, you take the value of "A", you find every ...

**2**

votes

**2**answers

827 views

### CTRL+SHFT+ENTER Array Formula not working

So my issue is a more complex one, but I have tried with a much simpler formula (with a similar case) and I get the same problem. The array formula I'm using only returns the results for the first ...

**-1**

votes

**1**answer

56 views

### Unusual hybrid nested raid array configuration (raid 1 AND 5)?

I was wondering about your thoughts on an off-the-wall raid configuration with 4 disks. Ignoring performance hits (which I expect to be bad), could one configure a raid 1 array with one leg begin a ...

**0**

votes

**2**answers

376 views

### bash: how to print multiple arrays values

I'm new to shell scripting and trying to figure out how to print 3 (or more) values of an array at once. Here's the word list I'm working with:
$ cat words.txt
one
two
three
four
five
six
seven
...

**0**

votes

**2**answers

875 views

### For loop with an array in bash script

I want to use for loop with an array. I using the following command for this:
#! /bin/bash
rm -f /orch/list
arrayVM=($(cat /orch/servers | grep $1 | awk '{print $1}'))
for i in $arrayVM
do
...

**0**

votes

**2**answers

130 views

### Understanding complicated array formulas

I wanted to remove all letters from a cell, leaving only numbers.After some googling, I found this array formula: =SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$...