array

Previously I had similar problem with this one, where I needed to assign a value to array's element then use ARRAYTOTEXT function. The reason I use arraytotext function is because I need to do iteration involving dynamic array variables, so I store the arrays in some cells in a row for each...

Suppose I have A = {1;2;3;4;5} I want to replace i-th element to value x. If i=3, x=9, then B = {1;2;9;4;5} Now I make custom function to do this assignment: =LAMBDA(array,i,val,VSTACK(INDEX(array,SEQUENCE(i-1)),val,INDEX(array,SEQUENCE(ROWS(array)-i,,i+1)))) But when I use it with ARRAYTOTEXT...

Suppose I have an array X = {"A";"B";"C"} I want to rotate the index by an integer i, suppose i = 1, so that Y = Rotate(X,i) = {"C";"A";"B"} Is there excel function to do this? What I do now is Y = LET(X,H18#,i,H16,n,COUNTA(X),ix,SEQUENCE( n)-H16,INDEX(X,IF(ix<1,ix+n,ix))) which is quite...

I have the following formula and data (please see images below). How can I improve or modify the formula to avoid slowing down the excel workbook? Note that the sheet contains around 25 tabs an the formula is used multipe times within each tab with various criteria. I would like to avoid...

Hello, I have a json file which I am parsing to excel, but one of the fields I need to pull doesn't work. The only difference I can see between this field any all other fields, is that this field is within square brackets, whereas all my other fields are not. I get an error Application Defined...

Hi, I'm trying to calculate the headcount & salary by allocation for a 5yr period where the salaries change every year, but the headcount allocation stays the same. I think im doing something very silly as the sumproduct formula that im using doesnt seem to work. Frmula used in F20 calculates...

Hi all, I hope you're doing well. First of all, sorry for bad explanation as my main language is not English. I'm currently using Excel 2021 and trying to experiment with Dynamic Array formula. It's quite helping with my daily Excel use. 2 of my latest question was asking about Dynamic Array...

Hi, first of all, sorry for my bad English. I have been looking for a solution here, trial and error by myself, and the Microsoft forum as well. But haven't found any solution that satisfies the needs. I think it's possible but I don't know how to put it into Excel formula. The logic is like...

I have a Table, with 144 columns, consisting of strings that I convert to Formulas in another Table. Work around for Volatile function Indirect when I add and remove Sheets, but need the Formula NOT to go #REF.... SO - I have an 1 dim array and would need each data to be written/saved in a new...

Hello! I'm still a newbie to the VBA and Userform usage. I have developed a Userform that will input the information from the Userform into the cells I have designated. I'm still trying to understand Arrays. But I have it setup in array for Vendor and Probe Type: Private Sub...

I am trying to use MATCH and INDEX to improve performance on a google sheet that has way too many XLOOKUP functions. I have a helper column (DH) that lists the matching ROW I need from the Visibility sheet. This formula returns the expected result from column 9 on the Visibility sheet, where...

I am trying to add classifications to my leagues based on year. Some leagues change classifications on a year-to-year basis. I am trying to use XLOOKUP to look up the year and the league, and select the proper classification from a range. The problem arises when a league changes...

I am trying to do an array multiplication, multiply each of the months with the corresponding numbers from the 'Array' below, for example for "Person 1" do 0.1*150, then 0.2*250 and so on. As you can see it is returning #VALUE. Even if I try multiplying as an array, or multiply the numbers one...

Hi Helpful folks of MrExcel, hope you can help me with this problem I've been stuck on. I have an array of values in one column and in two other columns I have the year and an amount. What I'm trying to so is create a third column (H) and say if year is contained in array *2, otherwise *1...

Can someone please fix this Google Sheet for me? I need it desperately for work. I cannot figure out what I'm doing wrong, and every formula I've looked up doesn't work for me. It's driving me CRAZY! o_O I've highlighted the columns that are giving me issues in yellow (sheet linked at bottom of...

I cannot figure out why I am getting a "Subscript out of range" error with this line: WS.Range(Cells(1, 1), Cells(5, 3)).RemoveDuplicates Columns:=Array(2, 3), Header:=xlNo

Hi! I have been working on implementing MRP Excel solution in a company I work for. I have already done one, and now I was working on a new more detailed system. My company prohibits me to install XLB2BB add-in so I will do my best to explain everything. First sheet 'ProdBOMs' contains Bill of...

I would like to pull all unique values from collumn D:D based on one criteria. Criteria Portfolio Name in (A2) Collumn of portfolio names: AA I want to avoid using an array formula is possible, whatis the simplest way to achieve this i have tried the below: =UNIQUE(FILTER(D:D, AA:AA=A2))

Hello Excel lovers, For a better understanding, please find the picture. Here, my input Column is I, J, K My output column is the P column (I want to get the Xlookup result for nth number.) To count the number of occurrences, I add a helper column L I don't want to add this helper column. I...

Strap in, this is gonna be long and possibly confusing. I have to test and record the first and last data point in groups of 25. Each quarter, the iterations change, so in Q1, I'm testing 1, 25, 26, 50, 51, 75, 76, 100. . . In Q2, I'm testing 2, 24, 27, 49, 52, 74, 77, 99. . . and so on...