Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
0answers
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
1answer
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
0answers
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
2answers
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
0answers
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
2answers
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 ...