1. D

    Loop through an Array, change values

    Hi there! I want to loop through an array looking for the below values. IF it finds them, I want to change the value, if it doesn't, just move on. <colgroup><col></colgroup><tbody> 10 Very Easy 10 Demonstrated Well 10 Very Satisfied 1 Not Demonstrated 1 Not Satisfied 1 Not Easy...
  2. I

    Copy columns based on header and array

    Hello, I have data in Sheet1 that I'd like to copy into Sheet2 based on the header. I'd like to create some kind of text array that I can update in the code to include new columns if they become relevant. I'd like the relevant headers array to be something like sWords = {"Date", "Qty"...
  3. K

    Extract list of non-zero cell values from a list AND whether Estimate or Actual AND the name of the product

    I have a list that I need to remove the zero value items from via formula to create a new list (imagine an order form). Because people will be using this second form without much excel knowledge, I need it 100% formula driven. <colgroup><col><col><col></colgroup><tbody> Qty Item Estimate...
  4. M

    Using Colour Codes in an array

    Hi, I have a table that stores the field name in column 1 then the colour code in column 2 (see below) in the VBA code I am trying to use the Colour code. If I use it as a number it works but I can't work out the correct number for the RGB codes I have. How do I pass the array variable...
  5. L

    Help with SumIfs again

    Good Day, Please help me to create SUMIFS formula. the data is on different sheets (Jan-Dec) and have multiple criteria. i tried, but i cant figure it out. Please help create formula in column D6 and D7 on formula sheet. Criteria for D6 are : Months, Name, and Kg Criteria for D7 are : Months...
  6. spencer_time

    Best practice questions, regarding variables and array / Array defined by variable

    Hello, I'm working on a large spreadsheet to help me automate crunching data at work. I've decided to put sets of data into array's (is plural of array, just array?) and am unsure if my approach will slow the end product or cause some other undesired effect. I, on a daily basis, compare...
  7. Z

    arrays count unique items?

    Hello folks, I have an array of say (s,e,se,sw,s,n,ne,ne,s,e,se) and would like the count of the unique items in that array using VBA. 7 in this example I believe. Is this possible and how would it be done?
  8. A

    Filtered List Formula

    Hi I’m attempting to create an array formula that only picks upvalues in a filtered list. Is itpossible to combine both formulae Array formula: =(SUM(IFERROR((AK5:AK215<(TODAY()))*(AK5:AK215<>""),0))) (courtesyof Jasonb75) Picks up non-blanks with the filtered list...
  9. ragav_in

    Need Autofilter for more than 2 criteria - Array is not working

    Dear All, I am trying to filter a range in excel (15 columns) and the filter criteria to be applied on a column (Column 2) which contains description. I want to apply filter on that column to get only the rows of data that meet 3 criteria such as "PMP", "PM Plan", "Project Management Plan". If...
  10. P

    Array formula with criteria

    Hello, I have an array formula that I want to update to make it more useful. {=(PRODUCT(1+$a$2:$a$28/100)-1)*100} That calculates the investment return for an entire time period, lets say 2006-07. I want to change the formula so that it will calculate the return only for the values within a...
  11. D

    vba arrays

    <colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody> A B C D E F G H aaa2 aaa3 aaa2 aaa3 aaa4 aaa5 aaa4 bbb1 bbb5 bbb6 aaa5...
  12. S

    If, then formula for matching the count

    Hello! I'm new to this forum and am hoping someone can help me with a formula. I'd like to return a text if the count of a text of a range matches the count of a text in another range. Ex: Range 1: Return Text: Data Input (these will be dropdowns)...
  13. E

    Array + Transpose + Table

    Excel Champs, Looking for creative solves for the below problem. I have a project sheet that looks something likes below in a table. I need a button function that can transfer in a separate sheet all the values in that row for that project transposed in a column. Master file (Sheet 1)...
  14. R

    Is there a #rows limit to pasting an Array to a Range?

    Hi, All. I am processing some data (1 year, 5 min. average, so 105120 rows) of several data-items. Col A: Time Col B: data Col C: data . . . First the values are stored in 1-D arrays, then some data cleaning is done, then each array is pasted to an Excel-sheet. ArrPI: Variant/Variant(1 to...
  15. A

    SUM of Array Where Two Conditions Are Met On Worksheet Other Than Where Array Is Found

    I don't know if this is possible, but I have tried and failed to do the following using sumproduct and sumif(s). Sum all values in array 'Tabbing Data'!$A$2:$CA$5000 Where column header = a value in a reference matrix, e.g. 'Tabbing Data'!$A$1:$CV$1='Data Gap Check'!$P47 And where 'Data Gap...
  16. B

    Changing an array formula to a regular formula

    Hi all and thanks in advance... I have the following array formula which is working as intended =SUMPRODUCT(((CONTACTS!$A$2:$A$5000=A2))/COUNTIFS(CONTACTS!$A$2:$A$5000,CONTACTS!$A$2:$A$5000&"",CONTACTS!$R$2:$R$5000,CONTACTS!$R$2:$R$5000&"")) Problem is it takes way to long to calculate. Is...
  17. B

    Array: how to populate it starting from a cell's value up to zero

    Hello, I am new to arrays and am really struggling with the concept. Here is what I am trying to achieve: in range("B3:B9") I have some numeric values. Let's say that the value in B3 is 37: I would like to create an array that contains all values from 0 up to 37 based to an incremental...
  18. BlakeSkate

    Adding entire rows to array

    I'm trying to write a row to the end of an array the logic is essentially: i = loop through UBound of array 1 x = loop through UBound of array 2 if statement is true then add the entire row we are looping through from array 1 to the bottom of the array make changes to the new line and add 4...
  19. Z

    2d array pasting into single column

    I have 6 columns (B:G) with 16 rows (2:17) of data in each column. I am trying to get all of the info of the array into one single column starting at cell K4. I have been able to write a loop to go down the rows but I can't seem to get the loops right. Any suggestions?
  20. L

    User forms and keeping array variables

    Hello, I am making a user form and I'd like it to contain: a drop down list, and 4 labels + 4 text boxes. The idea is after a choice is made in drop down list, 4 label values are changed depending on array position, and 4 text boxes can update the values of the array. So i fill up my array...

Some videos you may like

This Week's Hot Topics