1. T

    Loop cells to array

    I need to add values to array but I don't know how big range is. It depends of conditions in column J as you can see. Code below doesn't work. It doesn't add values to array it just makes new array everytime with single value which comes from column D. How to fix this to ADD new values to array...
  2. J

    List of values based on criteria in two places...

    Hi there, I am currently using a tracking spreadsheet to list out titles of people who started in my organization and the days it took to hire them. The problem I am running into is based on the fact they are occasionally stored in an interim place if they have accepted an offer, but not...
  3. D

    Dictionary, Collection or Array?

    i have the following excel sheet and need to reformat the data from a vertical view to a horizontal one while lining up the data in the appropriate columns. the vertical column is labeled Key and Value ("A:B") the reformatted output is labeled to the right called Key, followed up by the keys...
  4. X

    Sumifs within an Array?

    Is it possible to do a sumifs within an array? I have a large data set (circa 20,000 rows) that I am working on and one of the elements is a sumifs. Currently I'm creating most of the array, pasting it to a sheet, and then updating the remainder of the array by running the sumifs on the sheet...
  5. StephenArg

    Copy contiguous columns, paste to non-contiguous columns

    Hi there, I have a workbook with integers stored in 6 contiguous columns and 3 rows. ("source WB") For example: A1:F3. However, this range changes from week to week, so the selection of the source range needs to be dynamic. I have another workbook ("destination WB") where I want to paste...
  6. H

    convert Array Formula to Non-Array

    Hello All just trying to get the following array formula to a non-array {=IFERROR(INDEX(TRADES!$D$2:$D$2000,MATCH(1,(TRADES!$A$2:$A$2000=B3)*(TRADES!$C$2:$C$2000=DATE($F$1,$N$1,$E$1)),0)),"")} Thanks
  7. G

    Filter Everything, Except for a Single Criteria

    ActiveSheet.Range("$A$8:$AH$1894").AutoFilter Field:=1, Criteria1:=Array( _ "NOT STARTED", "ON-HOLD", "STARTED"), Operator:=xlFilterValues ActiveSheet.Range("$A$8:$AH$1894").AutoFilter Field:=2, Criteria1:="NCR" Hello All, The Code above is working to filter only "NCR" (after Field...
  8. O

    MS Scripting for directory tree parsing

    Go easy on me. I'm more of a decent hacker using other's code not a programmer. 1st post here. Tried using recursive code I found based on MS scripting (which I barely understand) to parse directory tree to fill an array with all paths. It seems to be parsing fine as I can tell HOW MANY dirs it...
  9. leopardhawk

    Need to add multiplication to an array formula

    Hello forum friends, I am trying to add a multiplier to an existing array formula but it is giving me, not an error, but the wrong result. The formula as it exists is returning a result of 24. I want to multiply this result by 0.621371 but when I add *0.621371 to the end of the formula and do...
  10. M

    Split string into numbers in Excel VBA

    The string can be like "abc123(456 k789)". It is to be splitted in array like array[0]=123, array[1]=456, array[2]= 789. So the problem is to split array based on numbers.
  11. N

    VBA - Executing array formulas

    Hello, The code below was written to update formulas in F5:N and array formulas in H5:AA, and replace the formulas in F6:A6 and below with the output as values. I am using VBA code to avoid a slow workbook, forcing the formulas to run only after I have updated other data in the workbook. The...
  12. A

    Fill down an array formula across multiple worksheets

    In short, what I need to be able to do is fill an array formula down in multiple worksheets. Within each sheet the formula needs to fill down a different number of times. I will give some background info so that this can be understood in context and then paste the code I currently have below...
  13. C

    sum column of numbers based on id # and other possible criteria

    I am trying to do the following but cannot seem to wrap my head around it. I have a workbook with multiple sheets. For what I am doing I have to pull information from one and compare it to another. It looks something like this: Sheet 1 <tbody> A B C D E F 1 Game # Cards Home ID Home...
  14. A

    Array not erasing

    Hi, I cobbled together some code from multiple sources that will process text files. Since we have multiple files in a folder that need to be read, I built the process to read all of the filenames in a folder into an array (DirectoryListArray()) that I use as a master list of files, then...
  15. S

    Userform Picture and text selection

    Good Morning, I was wondering two things, both interrelated: 1. I have a drop-down box that selects a bunch of info from an array using vlookup formulas and vba coding. Is it possible to have a collection of pictures saved on a sheet in the workbook and then the background of a userform as...
  16. J

    VLOOKUP vs. INDEX/MATCH on variant array

    I have a multidimensional array with 'x' rows and 3 columns of data, and I'd like to perform a VLOOKUP on the array information stored in memory rather than calling the worksheet each time. I'm just not sure how to write the code to perform the VLOOKUP using INDEX/MATCH, and repeat that process...
  17. G

    Array Formula Question - Condition An Array by Itself

    Hello! I'm trying to solve the following issue using an array formula but I'm struggling. I'm fairly versed with arrays but can't figure this out. Thank you in advance for the help! Using the example below, I'd like to pull the max number associated with each individual text in the first...
  18. Y

    Alternative to Array Formula

    I am using this formula : =IFERROR(MEDIAN(IF((rngProgramme=rngEruCourse)*(rngOwnerType="LA")*(rngStartYearList=rngStartYear)*(rngCreatedDate1>=rngLeadsFrm)*(rngCreatedDate1<=rngLeadsTo)*(rngRating="Null")*(rngDisposition=""),rngAging)),"-") THen Ctrl + Shift + Enter and I have a lot of cells...
  19. M

    Formula to Sort a Subset of Conesecutive Positioned Values of an Array with Constant Values?

    I am looking to figure out in Excel 2016 how to write a formula that will sort a subset of values in consecutive position within a larger array with constant values (zeros), but keep those zeros in their same positions thus keeping the number of values in the array the same? Moreover, an...
  20. W

    Store row numbers in an array

    I've got 2 types of data and thought it might be a good idea to use an array to store the row number in for subsequent processing; is this a better solution than ifs & loops ? Both sets of data are in blocks of approx 4 to 7 rows but each block in its raw form is separated by a blank row so...

Some videos you may like

This Week's Hot Topics