array

  1. S

    Find nearest and exclude member of array

    Hello! I need help with {=INDEX(MATCH.....} or something like that formula. I would like to make a formula that finds the nearest value, of a given cell, in an array of data, and exclude that array member in folowing cell. Here is an example: Thank you in advance!
  2. J

    VBA filtering array

    Hi All I'm looking to filter with the criteria being an array - can't quite get it to work. Simple example below - where am I going wrong? Thanks in advance! Jon Sub test() Dim Narrative As Variant Narrative = Array("*OPENING*", "*CLOSING*", "*VALUE*") Range("A2:H100").AutoFilter FIELD:=4...
  3. B

    Display TRUE or FALSE if value is the largest that occurred on that day. Bonus: ARRAY FUNCTION!

    I have two columns, Column 1 contains dates and Column 2 contains values, like so: Col1 Col2 1/1/20 5 1/1/20 7 1/2/20 3 1/2/20 9 1/2/20 4 1/3/20 6 1/3/20 6 What formula could I put in Column 3 that would return "TRUE" or "FALSE" if the value in Column 2 is...
  4. 9

    Arrays To Compare & Delete Data

    I have two tables that are on different tabs. One tab is called RawData and the Other is called CSheet. On CSheet, I have a column of data starting in C2. I am needing to compare the CSheet data list to the table that I have on the Raw Data tab. The table on the RawData tab starts in Cell A1...
  5. V

    Passing Texbox string array to countif formula in Excel

    Trying to get an Array to work with Formula Countif, but cant manage to get the countif right, can anyone give me a pointer please? I have a userform with 5 textboxes on it add a search word in 1 or maybe 2 i wanted them to use the countif formula. Sorry if this seems simple to you guys im...
  6. D

    Extract multiple specified values from cell and return in 1 cell

    Hello everyone, I need some help on this, in column A i have a list of various fruit, within column C i have sentences which may contain one or more of the fruit listed in Column A. In column E i would like to list the fruit from column A which is contained in column C. Also i would like to...
  7. U

    Treeview Parents/Child - How to return the first non blank value located one column to the left of the source cell and n rows upward.

    Hi all, I'll try to be as clear as possible so please do not hesitate to ask for clarification. I'm working with a text file data extracted from CATIA V5 that replicates the tree structure of an assembly with its components and subcomponents. From that text file I arranged the source data in...
  8. D

    Passing an array to a recursive sub, best practice for optimization

    I have a table with Bill of Material information in four columns; Parent PN, Child PN, Quantity, Units of Measure. Given a parent part number as an input I am returning the "exploded" or "indented" bill of material. Essentially that means I am returning all the children of the parent, all the...
  9. X

    INDEX/MATCH within an Array

    Hi I have a large report that I am pulling into a multi-dimensional array. I'm then using a function to remove dupes and then running various loops summarizing the data from the main array, but I am stuck on one point. I can't find a way to use INDEX/MATCH functions within arrays. ie if I was...
  10. asokaw

    Find the REF No where the Amt is = to the same negative amount

    I need a formula to show the REF No to be inserted in Result Column when the Amt is = to the Negative Amt as shown
  11. W

    VBA Code to extract number and paste

    Hi, I am trying to paste data from A,B columns from sheet1 to Aand B sheet2. For the A column , while pasting data I want to paste only numbers from each cells of sheet1. And for B column while pasting i want to remove first 10 letters from left and the paste it. Can someone help me with a code...
  12. V

    subtracts time array formula

    Hi All, Thanks in advance, we have the following sheet, we want to subtract B3-B2 in A3, 06-07-2020 13:160.00298611106-07-2020 13:2106-07-2020 13:2406-07-2020 13:2806-07-2020 13:36 pls provide any array formulla
  13. M

    Google Sheets | Where do I put a TOTALS array in the following formula?

    Good afternoon, Please could you advise where i would put the following array: {QUERY;{"TOTAL",SUM($E$5:$E)}} ...in this query: =IFERROR(QUERY(IMPORTRANGE("sheetname","INVOICE_ENTRY!B2:J"), "SELECT *" & IF ((LEFT(G4,1))="*", "WHERE Col7 STARTS WITH '"& RIGHT(G4,LEN(G4)-1) &"' AND Col1...
  14. M

    Rolling Average Ignoring Blanks

    Hello, I'd like to find the 3 day rolling average of two columns while ignoring blanks. Looking at M7 it would be something similar to that formula but ignoring blanks in J and K while automatically finding the previous 3 non blank values. I've tried using offset, large, and various if...
  15. G

    Alternate method to store data for use in any sub - Arrays don't work for this

    My original idea was to load an array (4D) with lots of data then call up the array as a variable and have it spit out the values that I want when I need them in other subs and/or in other modules. I haven't had any luck by searching the web, posting here or etc. The best advice I got was...
  16. M

    Handling Outliers

    Hey Everyone, Im still super new at this VBA stuff. But I am trying to devise a way to automatically pull data from my worksheet and exclude any outliers that is inputted by the user as "Yes" in another section of the worksheet then be able to use a basic STDEV function for the rest of the...
  17. J

    Dependent data validation question

    Hi all, i feel like i've watched a million youtube videos and read a million articles and they all seem sooo similar to what i'm trying to accomplish, but i just can't quite get there... Here's the situation; Excel 2016, will be a shared workbook so no tables allowed, workbooks will likely have...
  18. J

    Loop through an array and get a value

    Hello everyone, I am trying to run the below code in order to loop through a few arrays and get the first value in each array, but, unfortunately it is not working. The message box should display the values: "1", "4" and "7". Thanks. Dim allArrays As Variant, myArray1 As Variant, myArray2...
  19. B

    Sheets Array

    Hi guys, have had a look online but couldn't find anything particular to my situation. I want to copy an array of sheets where the sheet name ends in the word Log - The Sheet name could have anything at the start of the sheet name but it ends in Log - usually there will only be 2 sheets that...
  20. C

    Sumif(s) with OR criteria in multiple columns

    Hi everyone. I am trying to write a SUMIF() formula that pulls in a total from one column if either one of two other columns is greater than zero. Value1 Value2 Value3 500 0 1000 0 200 2000 100 100 5000 0 0 4000 So my psuedo code would be: If value1>0 OR value2>0 SUM...

Some videos you may like

This Week's Hot Topics

Top