Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
2 answers
43 views

Excel nested dynamic array formula, filtering distinct differences only

I'm working with a big dataset with many types of products to be uploaded to Shopify In order for the correct dropdown(s) to be displayed, I have to let Shopify know what the "children" (...
user avatar
  • 11
0 votes
0 answers
33 views

Comparing a range to a value in excel is returning the wrong values

I am attempting to compare a Range of values in a column to a single value and return a True/False array. The values are all dates and are formatted as dates. example: =A1:A20 > 5/3/22 This is ...
user avatar
  • 1
0 votes
1 answer
18 views

Compound COUNTIFS with LEFT used in an array

I'm trying to count instances of differing occurrences that must meet a string criteria, then also filter by timespan against a date time string in one block. I need to leave the worksheet intact and ...
user avatar
  • 1
2 votes
2 answers
70 views

How to return multiple match results and output them to a cell as concatenated text?

Suppose I have the following data: A B C D 1 val1 Str1 2 ValM Str2 3 val2 Str3 4 ValM Str4 5 ValM Str5 6 val3 Str6 7 ValM Str7 8 val4 Str8 9 val5 Str9 10 ValM Str10 I would like to: find ...
user avatar
  • 167
2 votes
2 answers
115 views

Excel MEDIAN across ARRAY with multiple conditionals using IF and AND functions

I want to determine a MEDIAN of values in one column based on meeting conditions in two other columns. I'm using MS Excel. I know I have to process the MEDIAN function as an ARRAY (ctrl + shft + enter)...
user avatar
0 votes
2 answers
92 views

Excel VBA - Dynamic Array not Working

I am trying to make an array that stores all of the values in Column A from "Workbook B" so I can then reference and see if a cell's value is in that array in Column A from "Workbook A&...
user avatar
1 vote
1 answer
354 views

How to set and use a variable from an array value in a DOS/Windows batch file

In a DOS/Windows batch file, how can I set/use a variable from an array and a loop index, such that I can produce output like what's shown below? SETLOCAL ENABLEDELAYEDEXPANSION set x[0]=one set x[1]=...
user avatar
0 votes
1 answer
36 views

Finding unique codes based on lowest values

I've been trying to find a solution to this for a long time in many places and couldn't find it. Here's a description: I have a table of changeover times between products. I'd like to create a list ...
user avatar
0 votes
2 answers
40 views

Find last 2 values & calculate % change

I need to calculate the percent change for the last 2 cells (months). The last 2 cells change monthly, with new data added. I need the formula to find the last 2 cells--with value--in the row, and ...
user avatar
0 votes
0 answers
54 views

Storing Numbers with period in associative array using while loop throws invalid arithmetic error token

i have a while loop that creates variable X and Y. echo "$X" "$Y" 1.2.3 2.3.4 2.4 4.6.7 5.6.7 1.2 declare -A arr while read in; do X="$(...)" && Y="$(...)&...
user avatar
0 votes
2 answers
189 views

Indent of array in method in phpStorm

phpStorm is formatting arrays in method calls like this (auto code formatting): $address = new Address([ 'full_name' => $fullname, ]); I expect it ...
user avatar
  • 1,792
0 votes
0 answers
74 views

Bash check array membership with regex

I have just started bash scripting as of this morning, so forgive me for my less-than-adequate skills. I have a GitHub action where I scp files which have changed on a push event to the production ...
user avatar
1 vote
1 answer
202 views

ZSH iterate through string array containing spaces

I am trying to write a ZSH script which will iterate through a series of strings that contain spaces. First, I'll set -A the array: set -A a "this is" "a test" "of the" &...
user avatar
0 votes
1 answer
95 views

How to append a value to an array in Excel

I have a formula, which returns an array. I would like to append another value to this array. Is there any way to do this with a formula without VBA in Excel? e.g. {1, 2, 3} -> {1, 2, 3, 4} or ...
user avatar
0 votes
1 answer
59 views

Return column headers as array (no blank rows) if column sum is not zero

I have a table with columns of components needed to create various products. This table is populated automatically as output data from another table. For this example, we'll use this one: S Widgets M ...
user avatar
0 votes
1 answer
404 views

Splitting Multi-valued JSON elements into an Array with PowerShell

I'm working with some JSON data in PowerShell. Some elements are multi-valued, delimited with a semi-colon, but its not always the same elements that are multi-valued. I would like to convert multi-...
user avatar
  • 241
1 vote
1 answer
430 views

Script to disable/enable PnP Device using InstanceID's - but the ID's change upon reboot

I have a working script to disable 4 usb joysticks using their respective InstanceID's $pnpIds = 'HID\VID_0079&PID_0006\7&1699A0E&198&0000', 'HID\VID_0079&PID_0006\7&5438EB5&...
user avatar
  • 45
0 votes
1 answer
92 views

Excel: AGGREGATE function with an array

I'm referring to the data below, and I would like to calculate a conditional mean: 2 0 #N/A 0 0 0 0 -2 20 0 25 0 0 0 0 0 0 0 0 0 154 25 0 120 0 0 0 0 0 -2 2 0 0 0 0 0 0 0 0 0 57 0 -57 1299 0 0 0 0 0 #...
user avatar
  • 103
0 votes
0 answers
38 views

Array formula to return true across multiple dates combined with if "sepecific text"= true

I am trying a formula that is sets out dates for a week and lists jobs on each day based on the days they are booked in. the array formula is designed to look up data on my current sheet (cell A1) on ...
user avatar
0 votes
0 answers
31 views

Power Query to extract JSON parameter values from the data

Hope you are doing well!...I am trying to split the different parameter values for just the correct ,added and removed codes at a vbillid level..In the attached excel file the column codemap has the ...
user avatar
0 votes
1 answer
33 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.
user avatar
0 votes
1 answer
333 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 ...
user avatar
  • 1
1 vote
0 answers
28 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** **...
user avatar
0 votes
1 answer
170 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 ...
user avatar
  • 481
0 votes
1 answer
62 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 ...
user avatar
  • 61
1 vote
0 answers
22 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))
user avatar
1 vote
2 answers
42 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 ...
user avatar
0 votes
1 answer
197 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 ...
user avatar
  • 1
0 votes
0 answers
36 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 ...
user avatar
  • 1
2 votes
1 answer
566 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 ...
user avatar
0 votes
1 answer
497 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 ...
user avatar
0 votes
1 answer
26 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 ...
user avatar
  • 1
1 vote
1 answer
111 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 &...
user avatar
0 votes
2 answers
246 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(...
user avatar
  • 1
1 vote
1 answer
98 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 ...
user avatar
  • 11
3 votes
1 answer
165 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 ...
user avatar
  • 55
1 vote
1 answer
38 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 ...
user avatar
  • 13
0 votes
1 answer
20 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 ...
user avatar
  • 1
2 votes
2 answers
2k 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 ...
user avatar
  • 342
1 vote
0 answers
23 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,...
user avatar
0 votes
1 answer
40 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 ...
user avatar
0 votes
1 answer
65 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+...
user avatar
0 votes
1 answer
59 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....
user avatar
  • 1
0 votes
3 answers
114 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 ...
user avatar
  • 113
0 votes
1 answer
108 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 ...
user avatar
2 votes
0 answers
22 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 ...
user avatar
-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!$...
user avatar
  • 11
0 votes
0 answers
443 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 ...
user avatar
  • 423
1 vote
2 answers
301 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, ...
user avatar
  • 113
0 votes
1 answer
1k 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: ...
user avatar
  • 1,915

1
2 3 4 5