# array

1. ### How to lookup a value from several rows in a table using INDEX and MATCH

Hi everyone, First post, so be gentle! I have spent hours trying to work out a solution to this issue, and know someone here will know the answer. We have data in a large table with a column for each month and then new rows for each year (see screenshot). I need to lookup the value...
2. ### UBound() of an array stops the code without errors

I have this function to check if an array is empty. This code has worked before. The input arr for this specific case is empty. Putting it in watch windows shows: 1. Expression: arr 2. Value: <blank> 3. Type: Variant/Variant() 4. Context: C_UnionTags.ArrayIsEmtpy The code stops at UB =...
3. ### Array in If statement

I have above sample data. In cells F9 and F10 I want to sum 'Sales' (C5:C10) for the respective months (Jan, Feb) based on the user input in cell F3. With following formula I can get the sum of sales for an individual product per month: =SUMIFS(C5:C10,B5:B10,E9,A5:A10,F3). To get the sum of...
4. ### Array Formula Extract Records tutorial not working for me

Having watched a MrExcel YouTube video about pulling records based on a condition, I attempted to reorganise a large question bank we have for learners. Unfortunately, the formula doesn't seem to work as it's getting caught pulling correct row numbers and always returns 0...
5. ### Sheet Name But Referencing Column Letter

Hi, Overall I'm trying to use a filter function with data from a separate sheet. Problem is the data is not very consistent and the column headers move around. I can use the address and match to find my column letter in the other sheet but I want to be able to put it back in to an overall...
6. ### VbA - copying different colum ranges depending on which condition is met & pasting into different sheet

Ok, so I would really appreciate help as this has been driving me mad for over a week. Basically I have a raw data table where a customer can choose up to 3 different courses, data is in 3 columns. But I need to be able to have a complete table where if a customer selects 3 courses, her details...
7. ### Function Not Working - Sort & Remove Duplicates by Creating New Array

I am not well versed in VBA and have no idea where to start finding the issue here. I need to write a function that will take a 1 dimensional array as an input and spit out a new array with all the values sorted alphabetically and duplicates removed. This input "SourceList" column has text...
8. ### Way to create rate array from total and counts (opposite of sumproduct)?

Hi, Is there a way to calculate a rate array from a total, if one has the counts? In the example below, I've used =SUMPRODUCT using rate 1 array and enrollment count array to get the monthly total. I'm wondering if there's any way to use a given monthly total and the count array to determine...
9. ### Concatenated range to array

Hello all, The following allows me to populate an array with values from a range Dim ARR1As Range Set ARR1 = range("D2:D10") I was wondering if there is a way to concate a range and then populate the array Not correct but something like Dim ARR2As Range Set ARR2= range("A2:D10")
10. ### VBA MsgBox based on rows - continue if true cancel if false

I have searched everywhere for this answer. I am sure it is out there but I feel like I am going in circles. So I came here to you awesome people for help and direction! I do apologize that I am not very savvy in VBA but occasionally I do have to create some crude macros and then come to you...
11. ### Using a Variable array in place of a Vlookup to return values to VBA code

I have a set of sheets which contain data which I pull into my VBA code with vlookups. The input data is collected into a table of data 10 columns wide, the key field is always an integer between 1 and 3000 but depending on the data entered by the users I might have 5 entries or 3000. The...
12. ### Strange Error

Does anyone have any idea why this code only searches the first row with a matching header instead of every row with a matching header? =COUNTIF(INDEX(\$A\$4:\$F\$360,MATCH(LEFT(L\$4,3),LEFT(\$A\$4:\$A\$360,3),0),0),\$J16&", "&LEFT(\$K16,1))+N16 Array \$A\$4:\$F\$360 with header \$A\$4:\$A\$360,3 trying to...
13. ### VBA Recipe calculator sum the ingredients

Hello, I need a piece of code that will calculate the quantities of needed ingredients for some products but i am stuck with some 2d arrays and the code messed up. Can you please help me? In the attached images, -the result for ingredient1 is 40 because we need 20 cakes and we use 2 pcs in...
14. ### Average all values in two different columns that are between two values?

Hello all, I am trying to get an average of values in two non-consecutive columns, and only include values between 80 and 120. In the screenshot below, I would like to average the values in columns I and K together. I also need to exclude any "" cells and cells that may contain #N/A. My full...
15. ### Filtering a 2D array in a listbox from a textbox

Hi everyone. I have a userform with a textbox and a listbox. The listbox is populated with data from a 2-column ListObject table whenever the form initializes and, as I type in the textbox, it gets filtered using the full string. That works, but since I'm filtering construction materials, I...

Hi all, I have a pretty complex look-up issue and wondered if anyone can help? The link to the G-sheet and criteria are below (also within the G-sheet): Link: https://docs.google.com/spreadsheets/d/12xETyc1bCqATpTdb--nZWRjVD9XCGHDb75f4YKMzhxA/edit?usp=sharing Criteria 1) Look down column B in...
17. ### Help on array formula conversion

hi good day/evening, I am new to this forums and I want to thank everyone in advance, can someone help me with my array problem, I wan to be able to compare a cell on a list. with this formula COLUMN C is the list APPLE BANANA STRAWBERRY PINEAPPLE ORANGE COLUMN D is the what i...
18. ### VBA code to Conactenate the data in a row by removing duplicates and sepearating it by comma.

Hi, Can someone help me with VBA code to do the below task. I have data in A2-Z2 where in each there will be some data. this data in each cell migh be sepearted by comma. what i am looking for is to publish a out put in AB where it concatenates data from A2-Z2 by removing duplicates and...
19. ### Most Improved

Alright, so 3 problems I need yall's help with please... I posted pictures to give a better idea. I'm hitting a wall... and i'm not an expert with vlookups or array formulas :( 1. I'm trying to get the top 5 Most improved sales associates. I want names & %Increase 2. And the 5 with the...
20. ### VBA code for multiple if statements within an Array

Hi there, Can you guys help me with below code. what I am trying to do is to split the array in column B which is seperated by commas and then look for each items and if the condition statisfies then it should publish something in column C. I have lot of scenarios with combination of both...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back