array formula

Good afternoon, I have an array formula setup to bring back the earliest sell date of a product from a different worksheet in the same workbook. The formula works, but it drags Excel to a massive crawl and sometimes the program freezes up because it's currently 137,051 rows of data. Note: The...

<tbody> Item no. Catagory Date Amt A T01 1/1/2017 10 A T01 1/5/2017 15 A T01 5/5/2018 20 B T01 1/1/2017 15 B T01 1/1/2017 15 B T02 1/1/2017 15 ..... </tbody> I'm trying to determine the item no. under catagory "T01", within a period from 1/1/2017 to 1/31/2017...

I request help to get a very complex output, so it seems a very complex array formula could be needed. My inputs are 2 numbers that share a common prefix. The first number is the common prefix and the second has a few numbers more and I need to get a list expanded up to the second number. This...

I have a list of numbers and I want to get the common prefix between them. I've been able to make this array formula that gives me correct output but comparing only 2 numbers (stored as text). A1=23702000000 B1=23702999999...

(hyperlink to googlesheets version of my Microsoft Excel sheet- as I couldnt figure out how to attach the file! https://docs.google.com/spreadsheets/d/10S4lg6PyUQC7wZjVp4Oepdnql5v4ii4GR091nm5Jzuw/edit?usp=sharing Hi all, I hope somebody can help! I will start with what I am looking for, and...

Hi, Is it possible to to use 2 x array formulas in the same function. I.e. {=sum(countifs(range,{"text","text"},range,{"text","text"}))} I'm trying but it's only picking up the first text from the second array. Any ideas ?

Hi Everyone, Would you be able to help me create a formula that will allow me to return multiple results, but then display the cell reference of each result I tried to use INDEX MATCH but it will only display the first match that it finds then return the cell reference of the match, this is the...

Hello, May somebody help me how to tabulate with a formula the following information please. <tbody>#CACACA [/URL] "] A B #CACACA [/URL] , align: center"]1 STAGE 1 #CACACA [/URL] , align: center"]2 ID 0 #CACACA [/URL] , align: center"]3 NAME JFMSC #CACACA [/URL] , align: center"]4...

I'm trying to build the MASK column such that a 1 is displayed for the first distinct COMPOUND that has VOL * FLOW > 0, but I can't get it to work in the table using SUM(IF(FREQUENCY constructs; I get a "multi-cell array formulas are not allowed in tables" error. What other options would I have...

Hi Guys, I have a formula that seems to be pulling the information I need well, but it functions very slow and it is an array formula. I am looking for an equivalent formula that is not array. {=IFERROR(INDEX('2018 Import'!$H:$H,SMALL(INDEX(($A$9='2018 Import'!$A:$A)*(MATCH(ROW('2018...

Hey There, I am currently using a lot of sumproduct formulas in my sheet and was wondering if there is an easier way. Every time I add someone to the page (which takes forever between all the calculations, I have to go through everything and copy-paste and change the name so that the formula...

I am trying to find a formula that will return the data for corrective actions that are repeat issues that are within a date range. I have a summarty table that will contain the repeat issue data once it is pulled from my main table. So I need the formula to look at my main table (contains...

I have two excel workbooks. Of which one excel (X) contains macro and array formula. Normally I copy and paste some data from one excel (X) to another excel workbook (Y) manually. During the manual copy and paste, the array formula works fine. Later I copied and pasted the same using macros...

Hi, I have a table with 1000 records. Only approx. 10% of them however have some TRUE condition. <tbody> Name Condition A TRUE B FALSE C FALSE D FALSE E FALSE F TRUE ... ... </tbody> I would like to create a table on another sheet that shows list of names, but only for those...

Hi, I know this can be done using array formulas, but I am not expert in them so maybe you can help me out. I have two tables: <tbody> location name value ... EUR Lucy 0 ... USA Fiona 0 ... EUR Jane 1 ... EUR Jane 1 ... EUR Lucy 1 ... ... ... ... ... </tbody> <tbody> location...

Hi all, I have a sheet of data with two columns I want to run a formula against. The first column contains device names, the second, version numbers of a piece of software. Both are text (the device name is alphanumeric and the software version is numerical but with major, minor and subminor...

I need an array function that works much like the column grouping function of a pivot table. For example: Referencing the list of items below... <tbody> Mary Mary Bob John John John </tbody> ...is there an array formula that would return the three distinct items ("Bob", "Mary"...

Hi, I've not seen that Microsoft have chosen to include this kind of function (am happy to be wrong) in any release??? What I'm trying to do is very simple but believe that either it is only achievable through an array function or using VB. VB works no problem but I don't want to go that route...

Hi all I am struggling to get an Array formula to sort based on Multiple search values. I have 3 List. when the excel was first written I only had List B and C and i used the following formula to create a result list of just the references in list C...

I'm trying to use an Array formula to return all values from a field for a given customer's name and then use something (I thought probably either MAX or MIN) to sort through them to get the correct one. The formula is currently {=MAX(IF(A3='Sheet 2'!AH:AH,'Sheet 2'!G:G,""))} where A is my...