Questions tagged [array]

The tag has no usage guidance.

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