1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. F

    SUMPRODUCT IF value equals

    Hello, could you please kindly help me with the below, to make it so that it'd only consider values for the calculation if the corresponding value in column 'A' = "XX", and instead of specifying the range, sums up all the matched values in a given column? Please also note that values start in...
  8. H

    Adding a wildcard function to an excel array formula to return multiple values vertically of both Number and Text

    Hi all, First I want to apologize if my question lacks some formula knowledge, I'm new to this level of Excel and trying to figure this out. Second, I want to thank all for tolerating my ignorance. Here is my challange: I'm designing a stopgap solution for querying a large Excel spreadsheet...
  9. S

    Monthly Meal Planner

    I am looking to create a two column list from the table titled "Meals" the Range is $C$6:$F$117. Have played with formulas and am coming up dry on a result. Trying to make planning for feeding this infant easier... instead I have created a puzzle. Would like to get something like this from the...
  10. W

    Algorithm to pair positive and negative numbers and deal with residuals

    I have the following problem: I have a number of data entries with following fields ID, Name, Category, Amount - currently in Excel columns. The aggregate of Amount will equal zero, ie there are positives and negatives - but it will not necessarily equal zero for each Category. I need an...
  11. B

    Count Matches based of Criteria in other cells

    I have a table set that I can't sort or adjust. I want to count the number of matches in group 2 based off of group 1. In this example it would be 3. Group 1 has 3 that match group 2, APPLE, ORANGE, PEAR.... MIKE doesn't match. What formula would do this? 1 APPLE 2 corn 1 ORANGE 2...
  12. L

    How to return the starting balance of a bank account in a master list of bank accounts

    Hi everyone, I've built a workbook that evaluates a client's banking information and processes multiple bank accounts at a given time. I'd like to find a formula that will return the starting balance of each unique bank account from my dataset to reduce human error with data input. The...
  13. J

    multiply each row in array by %

    I have a sheet with table of data and I need to apply a different percentage to each row of data going into the array. For example AC AD AE AF ...all the way to CR Prorate scholarship1 scholarship2 scholarship3 scholarship4 .67 5000 1000 2000 3000 .45 4000 3000 5500 2500 1 3500...
  14. A

    VBA FormulaARrray (Index w/ Multiple criteria)

    Hi, I have a part here of my VBA code. I don't know what the problem is but the code returns a single result to all the covered ranges. It only returns the result for the first item. For example: In column F2 up to F10 All the results returned by the code from F2 to F10 is the result for F2...
  15. C

    Nesting within ARRAY and IF formula Google sheets

    Building out a holiday request system that is specific to my team and I am struggling to factor in half day holiday requests. I have the following formula: =ARRAY_CONSTRAIN(ARRAYFORMULA(IF(SUM(($D16>=IF('Vacation request management'!B:B=Workings!$B$2,'Vacation request...
  16. B

    Using arrays in LINEST for multi variable regressions

    Is there a way to combine two columns into a single array such that it can be used inside the LINEST function? for example, LINEST(known_ys, known_xs, const, stats) where the known_xs are two different columns of the same length from two different tabs, as opposed to be adjoining columns
  17. M

    Match two and more condition and creat unique value

    Hello, I have excel with two columns: one is a city, the seconde one is a name of river. I need to find a intersection and get an unique value for each group. Each group is created from the same river and particular cities. If city has two rivers, this should be in the same group. But also the...
  18. N

    Array Help!!

    Need to filter a list of ID's (with multiple entries per ID) which includes end dates for events and a "day count." Ultimately, I need to get the number of ID's (not which IDs, just a count of them) that have exceeded a "day count" (say 220 in a year or 400 in a two year window). Each row is an...
  19. F

    Filter Dates Stored In An Array

    Hi, I have a simple worksheet which has many dates in an column and I want to filter only Sundays from them. This is a very easy task if you can add a column in data but you cant. So as far as I know it can be done if you store all these dates in an Array in VBA and then filter that column with...
  20. littlefish

    Training Results and Ranking

    I have a table which ranks employee training for: Excel, Word, PowerPoint, and Outlook. Column A - has the employee completed training for all four Office programs? Y/N Col B - employee name Col C-F - ranks 1 through 4 each Office program according to employee proficiency (E, W, P, O) I'm...

Some videos you may like

This Week's Hot Topics