I have created an analysis worksheet but need to a formula that looks up against a list, hence why I think (and hope) that this might just need an array formula.
So the worksheet has a few thousand rows of data (one for each order) that is put against an email address. I need the formula to sum the revenue generated by 'club members' who placed on a specific date whos email appears in a list on a seperate tab. If it wasnt for the email list I would use a simple sumifs formula but I am stumped by the need to look up against a list of emails.
the data tab is the one with all the data and the discount tab contains the list of email addresses to look against (B:B). AC is the revenue data, Z is whether they are club members, L is the data (B11 is the date to look up against).
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes: =COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and =SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1 List2
Final List
Audi Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example
I am trying to create a list from an array. Said array contains formulas that return numbers from input contained in an other table. I would like the list to list the numbers in ascending numerical order.
I have a question regarding arrays. If I have too many elements in a 1D array(let's say 1000), how can I list all of them in a msg box (separated by comma)?
I have a list of combo boxes embedded on a worksheet and I would like to have them accessible through an array. I am an old VB 5 coder and this was a method I used very often and found it to be quite the time saver, but I cannot find a way to do this in excel.
I am trying to create a purchase order that has blank item cells, but when you click on one a drop down list appears. By selecting the appropriate item, I would like the price to come up in the next cell for the item selected.
What would be even better is if once that was done there is a cell next to the item and price and this cell will be for the type of the item selected. In this case the flavour, flavours are specific to different item though?
I think the first bit can be done through a drop down list of some kind, not to sure about the flavours drop down menu though.
I am looking to create an array where values are based on a data validation list.
i.e. in cells C5:C65 which have a data validation list of "Active" and "Not Active". Once a cell has been selected as "Active" then all other cells are in that range are automatically "Not Active".
The answer I am sure is an If and Lookup or something but what I have been creating today feels like a sledgehammer approach with helper columns and all sorts - I really want to avoid an array or anything that will chew up processing power...
I know you can read a range of data into a ListBox with a single command. can you read the contents of a ListBox into an array with a single line, and if so what is the syntax?
I've created a range B3:B12 in which each cell returns TRUE of FALSE. There corresponding values are listed in C3:C12. I have the same thing again in D3:D12 and E3:E12. Checkboxes link to the TRUE/FALSE cells. Attached is a stripped down file to show this.
I want to create a formula that lists each item in the one cell (no spaces required inbetween) for use with a search. I know a long winded way to do this involving many IFs but any array will be much more tidier, thing is I hardly use arrays and have struggled with this one.
I'm thinking it will be along the lines of =IF(B3:B12="TRUE",C3:C12,"")&IF(D3:D12="TRUE",E3:E12,"") but exactly what it should be is beyond me.
EDIT: The original file attached was corrupt for some reason, the new attachedment is the same except instead of TRUE/FALSE cells I've set them to 'x' for quickness (lost my work...!).
I'm not sure if this is possible but I am trying to find a function that will organize data that will be in a set 2-dimensional array (ex. 6 x 8) into a single list column. The trick is to omit any blanks that may be in the array so that the final list contains no blanks.
This is for a template; the data will constantly be changing so it's not a one time project where I can just sort the data to omit the blanks.
In the example I am trying to automatically have the 'Initial Group' organized to look like the 'Final List.'
But when in column "A" is "Yes", I want to store in array each value within cell in B (in this example B2) to apply later a For/For Each over each number (in this example are 3 values only within cell B2), something like:
I am working on data that needs to be cleansed of the symbols i.e. *&/- etc so I am hoping that I can automate this as their are over 30,000 rows of data and takes time to go through each find and replace.
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:
Car Region Own Use Color
Honda North Yes I use it to go for work Green
[Code] .....
I want to be able to do following (2 separate tasks):
Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):
Use
I use it to go for work
Family trips Weekend fun 2nd car Work
Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):
Car Region Color
Honda North Green
White
I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.
