I have a spreadsheet of information (product codes and values) and want to use VBA to find the top ten list of products by value. The list of products is variable in length so I can pass the data to a two-dimensioned array.
How do I ask VBA to return the top ten products by value - there can be duplicate values so I just want ten (if there are 4 #9s, I just want two of them, any two)?
I am remodeling my home and have put together a budget template. I have uploaded the file to make things easy to understand. Basically I want to return the values from column F and G on sheet "ledger" to the corresponding account name to column H in sheet "budget". For example, rows 14, 21, and 22 in the "budget" sheet currently correspond to the "fuel" account. I would like those values in column G (or F) to be added and calculated in cell H58 in the "budget" sheet.
View 5 Replies View RelatedI seem to be incapable of creating an array to return all of the values in each of the cells in the first row so that I can populate a combo box. I managed to do it without a problem for copying the sheet names within a workbook but not for this and I want to bang my head against the wall!
Basically, I have the first row of my spreadsheet for which I don't know how many columns contain data so my range is variable.
I then need to take the value of each cell and add it to my combo box (Later I would also like to use this routine to create and append sheets in other workbooks).
In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
View 3 Replies View RelatedI am trying to return an array of values into 1 cell.
Basically, I have a gigantic list of item names, item numbers, and reuse codes.
I want to populate all the reuse codes by item number in a second sheet:
something like this:
Source sheet
Item item num Reuse code
Widget1 w001 298767
Widget2 w002 4894957
widget3 w003 28276
Widget1 w001 12345
Widget1 w001 678910
What I want this code/formula to do is consolidate the reuse codes based on the item number.
Aggregate Sheet
Item item num Reuse code
Widget1 w001 298767;12345;678910
Widget2 w002 4894957
widget3 w003 28276
I have this formula but it only gets the 1st Reuse code, I want it to get ALL reuse codes for that item.
{=INDEX('Reuse list '!$P$2:$P$24489,SMALL(IF('Reuse list '!$E$2:$E$24489='Status Sheet'!A11,ROW('Reuse list '!$E$2:$E$24489)-ROW('Reuse list '!$E$2)+1),1))}
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I'm having a difficult time returning COUNTIF values for a positive match between 2 columns THAT meet a certain condition. Basically I want to iterate through column A sheet 1 (ONLY for values where column B is paid) and return a count for every instance there is a match of value A sheet 1 in column A sheet 2. In other words, I'm looking to find the number of ids marked as paid from Column A sheet 1 that exist in Column A sheet 2. I don't wish to return the actual ids, just the total count.
I've tried the following but I know there's an error in iterating through Column A the way I have it:
=COUNTIFS(Sheet1!$ColA:$ColA,Sheet2!$ColA:$ColA,Sheet1!$ColB:$ColB,"paid")
Sheet1:
ColA
ColB
123
paid
[Code] .....
Sheet2:
ColA
ColB
23
NY
[Code] .....
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,
MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP
(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
View 9 Replies View RelatedI can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:
{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.
My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?
Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.
Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.
Link to an example document to clarify this. [URL] .......
I need to figure out how to match three values on the "source info.xls" file attached to the "PFG FILE.xls" that is also attached. In column A in the "Source info.xls" file the formula will need to match the yellow column, then the green column, then the red column. keep in mind that the attached files are just a small subset of data. The "PFG File.XLS" is actually 150K rows long.
View 5 Replies View RelatedI'm trying to use an array to carry out string function on a range of excel cells.
Here is the code I am using
Dim arrXl As Variant
arrXl = ws.Range("F1:F" & ws.Range("D1").End(xlDown).Row)
For i = LBound(arrXl) To UBound(arrXl)
MsgBox (LBound(arrXl))
'If 1st char is different from 3rd char then remove all of string after 1st char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
arrXl(i) = Left(arrXl(i), 1)
End If
'If 1st char is different from 7th char then delete string after 5th char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 7, 1) Then
arrXl(i) = Left(arrXl(i), 5)
End If
The lbound function returns the value of 1 as the lower bound, I do not have "Option base 1" set so I was expecting the lbound value to be 0. The first 2 cells in the F column are blank so this may have something to do with it, I am unsure if cells in excel can be null if they can be null one cell may be null and the other may be a zero length string but I am unsure about this.
The ubound function returns a value of 487.
The code breaks when I try to access an element in the array so it breaks on the line:
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
and returns the "Subscript out of range" error message.
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies View RelatedI need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies View RelatedQuestion contained within code:
Code:
Dim MyArray(6, 1) As String
Dim i As Integer
'' Array List 1
[Code]....
'If lbxLI = any list index from Array List 1 then use the value stored in Array List 2 to give Listbox 2 its rowsource.
'For example; ListIndex "0" would produce a row source "_0" for listbox 2. Else if ListIndex "1" is selected, produce row source "_1" for listbox 2.
' I would like to achieve this from one "If" statement. I'm guessing it must be possible using a loop, I just can't think how despite a lot of messing about with code.
how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula
Example:
Name Replied?
Bob N
Brian Y
Robyn N
Rachel Y
From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.
I want to find a number in my array and return the value in column A. Trouble is if the array goes past column R I get the wrong Answer. the formula I am using is
=LOOKUP($C$22,$B$2:$AC$18,$A$2:$A$18)
Some cells are blank I am trying to draw a raffle.
Here is my data and formulas below:
The lookup of value 209 produces correct results when I look up as far as column R after that I get the wrong answer.
A
B
C
D
E
F
G
[Code] ....
The short version:
(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))) evaluates out to series of 340 True and Falses. I need to be able to pull the nth True, and then somehow index that against AH2:341.
I'm working with a record of 53,000 entries with latitudes and longitudes and I need to cross compare them with 341 locations in the United States.
I've already done some of the heavy lifting, which supported narrow an initial list of 85,000 records to at least recognize which records where within any of the 341 points. (Less arrays makes my computer happy.)
This formula gives me the count of locations each entry is within one of the 341:
=SUM(IF(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25,1,0)) {CSE} & copied down 53k times
The actual return range is AH2:AH341 for the location number.
Now, I need to be able to list each of the 341 they are close to. The max count is 17, so I'll only need to list out 17 columns.
Maybe something like =LOOKUP(1,1/(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))),$AH$2:$AH$341)
but this only returns the first match. How do I return the 2nd, 3rd, 4th, etc?
Because of the sensitive nature of my data, we can use a much simpler array which I'll adapt; I'm mostly trying to understand the logic or formula that should be used.
Return Nth Match.xlsx
I have the attached example workbook and I'm trying to lookup a date from a list based on a project start month:
- in cell D8 I want to be able to return the date that corresponds to the project month in row 5
- the dates are in column A
Example WB 1.xlsx
My first attempt is to return a simple Array of ColorIndexes.
View 10 Replies View RelatedI have an array with a lot of rows and columns filled with different codes.
I need to look into this array for one specific number and return the value in the same row in an specific column.
I can't use Vlookup in this case because it needs to have the values you are looking for in the first column and my values are spread in different columns.
I have two columns of data, one containing Groups, another containing Items. Like this:
Group | Item
A | Apple
A | Orange
B | Pear
B | Banana
C | Tire
D | Coffee
etc.
On another sheet, I have each of the groups listed as column headings. I want to resort the data and display it in columns instead of in a single list.
I can write a VLOOKUP to search for "B" for example, and return "Pear", but I want to return all matches for "B" not just the first one.
I have a formula which I took from another post to return a particular value in a specified cell as follows;
=INDEX(A1:E9,M3+MIN(IF(A1:E9=K3,ROW(A1:E9)-3)),N3+MIN(IF(A1:E9=K3,COLUMN(A1:E9)-1)))
However I don't understand what the -3 and -1 does and I believe it is these entries that are causing the error.
See attached file : TEST.xlsx
Does anyone know how to return multiple rows in an array with the same value?
i know that if i use ....
I have an Array formula that returns a value from several rows below:
=MAX(IF($P$8:$AQ$8="Today",$P13:$AQ13))
Row 8 contains only text and only one occurrence of the word "Today".
Row 13 has only numbers. The corresponding value directly below the column containing the word "Today" is returned successfully.
I now need a similar formula to return a text entry located in the same column but on the previous row (row 12) directly above the value just returned and this row contains only text entries.
Using the above formula and referencing $P12:$AQ12 returns 0 as expected.
I have an array that is 60x100 (column x row).
I'm trying to search for a value in that array and return the value on the same row, but shifted to the right one column.
Example: If my value is found in D63, I want to return the value in E63.
I've been messing with Index, Match, and Offset, but I can't get anything working.
I have a grid like follows (how to format):
2013-08-16-5.00 0 0
2013-08-16-2.0-19,902 -20,734 -21,429
2013-08-16-1.011,431 11,907 12,297
2013-08-160.0-0 0 -11
2013-08-161.0111 153 140
2013-08-162.031 61 60
[Code]...
Basically I want to return an array (to pass to another function) containing all data that matches my search. E.g. I want to retrieve an array of the data that matches 2013-09-20. Is the only way to do this a VBA func?
I need a look-up function that can return a value from an array that can be 3 to 7 rows in height. I want the function to always look at the last row in the array.
This is how it looks:
Column A Column B
blank row
1000
A
B
C
SUM 15
blank row
In this case I want the function to return 15 if I search for 1000. The problem is that there are not always a fixed number of rows beneath the searched value.
Range A1:A10 contains no formulas, only constants.
Only one constant value at most would ever be in that range, sometimes nothing in that range.
The value might be text like "Tom", or it might be a number like "1234".
The value could be in any of the 10 cells.
So far, and this is where the "missing something obvious" part comes in, I have only seen array solutions such as
=INDEX(A1:A10,MATCH(TRUE,A1:A10"",0))
My question is, can this result be achieved with a non array / non sumproduct formula.
I need to do this for 6000 cells in a project, that's the design and that's the way it is. I don't want that many arrays in the workbook. I already did this with VBA so I am not asking for a VBA or UDF solution, just hoping to know from the formula wizards what a non-array formula could be that returns the first found (and in this case only-existing) constant value in a range.