on the 11th row there should be a formula which sums corresponding column, according to how many items and which items were used in this particular set: corresponding prices should found from array "prices" (page a).
i'd like to use array formula (ctrl+shift+enter) if needed (like: {SUM((units=units2)*set2)}) or sumproduct function (like: SUMPRODUCT((units2=units)*set1) ), but can't use it, cause the arrays aren't equal. any ideas (except additional column on sheet B, where reside corresponding prices, then can use sumproduct function)?
I want to take select records from one sheet and sum only the matching records from another sheet, but the ranges are different sizes. As an example, in Sheet1, I want to select the codes with a "Rank" of "1", and total the "Cost" of those matching codes in Sheet2.
I place the below formulas in A1,A2,A3 …A100 A1 = --(D10:D20="A") A2 = --(E10:E20="F") ……….
These formulas return arrays {1,0,01….) and of course they can not be displayed in single cells (error message). Is there a way I can use them in a sumproduct formula? C1 = SUMPRODUCT(A1*A2*A3…*A100) The reason is that in reality they are much more complicated and I get error messages (length of the sumproduct formula) and secondly this way I can “understand” and use better my formula. I dont think that i can "break" the sumproduct in smaller pieces and would be great if i can do something like the above.
I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.
I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time.
So I need a formula or process (taking and comparing the data from both sheets) to do the following
IF 1. The dates of a widget sale and the accessory sale match (in both sheets) 2. The salesman ID in both sheets match 3. The names of the customer in both sheets match THEN 1. Add up the total dollars by salesman for sales that match the above criteria
I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code: Public Sub CSVparser(file As String) Dim Lines As Variant Dim j As Integer Lines = Split(file, vbLf) ReDim breadCrumbs(UBound(Lines)) As Variant For i = 1 to UBound(Lines) - 1 breadCrumbs(i) = Split(Lines(i), ",") Next i End Sub
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1 data data data data p2 data data data data
[Code] .....
needs to become:
p1 data data data data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1: 50025 50026 50086
Table 2: Considering that name, age and location is each in separate cell
I have been trying to solve this problem by breaking it down to it's core elements and it seems to hang up at a certain spot but I can't figure out why.
formula is: {=LARGE(IF(AND(Array1+8<20,6-Array1<0),Array2,0),1)} where: Array1 is a sequence of numbers, say, 2 5 8 11 14 17 Array2 is a corresponding sequence of numbers 1 2 3 4 5 6
the resulting array should return the numbers 0 0 3 4 0 0 and my answer should be 4 instead my answer is 0
when I break it down and select six cells and use the formula: {=if(Array1+8<20,Array2,0)} my result is: 1 2 3 4 0 0
and another six cells and use the formula: {=if(6-Array1<0,Array2,0)} my result is: 0 0 3 4 5 6
when I select a single cell and use the formula: {=large(if(Array1+8<20,Array2,0),1)} my result is: 4
when I select a single cell and use the formula: {=large(if(6-Array1<0,Array2,0),1)} my result is: 6
I've tried this method several different ways, even using "Yes" & "No" as my result and then try to match them up. I've tried doing this not using named array and just selecting the cells themselves for the formula. All with the same results.
I have a worksheet that has 20 columns and 500 rows. I created a VBA macro to loop through the data to hide rows that do not meet certain criteria. After the data is selected I copy and paste those selected rows to another sheet. The macro works well but I would like to use an array to contain the data that I copy and paste to a new sheet. I have been trying to find information on multi deminsion arrays but I have not been able to fully understand how to get the information into the array and then how to get it out again. Most of the examples that I have seen are for two maybe three columns (dimensions). I am hoping that someone could point me in the right direction to get started on this. I also have a few books on Exel VBA but none of them seem to address my question.
I am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.
Sub test()
Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer
The formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.
Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.
I have two 2 Dimensional String Arrays with data. I need to find a way to get the difference between these two Arrays. I am new to VBA, I don't know how to deal with these. I certainly feel that there is some efficient function for doing this. or Is the naive two for lop concept is the only way to go?
I have an array that will open specific workbooks. But Now I need to append a Case Number to the beginning of the file, and I don't know how to cycle through two Arrays. I have been setting it up as:
Code: Dim Report Dim Reports Dim WB AS Workbook Dim WS AS Worksheet
For Each Report in Reports Then I open the workbooks and print them. Next Report
Now I want to add in a 2nd Array, that will Append the 1st item in CRN to Reports, then the 2nd item in CRN to 2nd item in Reports. So it would look something like this.
Code: Dim Report Dim Reports Dim CRN Dim CRNS Dim WB AS Workbook Dim WS AS Worksheet
I have two arrays of the same size and shape. Question: is there a more straight forward way to add up all the corresponding elements in both arrays without looping through each pair of numbers one at a time?
Does anyone happen to know of a list of formulas in Excel that can be used as Array formulas?
I am trying to get a better grasp of what happens with array formulas in Excel - for instance MATCH seems to be a good candidate to hold an array of values when confirmed with CSE, but INDEX seems like a formula that wouldn't itself hold an array of values (although it might be able to lookup the corresponding cells of two arrays with corresponding rows and columns).
Below is my attempt to understand working with arrays of values.
Test() works fine. Yay! But it is "too easy" just plugging in a hard value for the range address...I don't always have that luxury. Normally I work with ranges that I've determined at runtime. I'd like to assign a range of values to an array but my attempt below -- test2() -- fails. I am not even sure if my effort is a good approach or not, but I know it doesn't work!
Basically, I am looking for how to assign the range of values to an array when I am determining the range in code -- as in:
I need to find the possible combinations for several arrays consisting of binary data. I shall give a simplified example with three arrays and four fields. Lets assume the three starting arrays look as follows:
1 0 0 0 1 0 0 0 1 0 0 0
Each row may contain one field = "1" and all other fields = "0". So the next possible combinations may be:
1 0 0 0 1 0 0 0 0 1 0 0
1 0 0 0 1 0 0 0 0 0 1 0
etc...until:
0 0 0 1 0 0 0 1 0 0 0 1
Assuming k = number of fields and c = number of arrays the possible combinations are k ^ c = 4 ^ 3 = 64. I am looking for vba coding that allows to cycle through all possible combinations and displays them in an excel spreadsheet. Ideally, the code should work for any k and c. Could anyone please give me any pointers as to how to tackle this problem?
p.s. in essence I'm looking for the tabular representation of a tree plan with four branches to commence with, a total of 16 decision nodes, and two possible outcomes (1 and 0)
Well I find myself going round in circles so I thought I'd just ask hopefully solve things quicker. I'm struggling with arrays in VBA, at some point I could use them but now it seems I've forgotten everything and even reading some tips on-line doesn't seem to be helping at this point.
I'd like to be able to use a dynamic 2-dimensional array to store values. I'm reading these in from a worksheet under certain conditions (depending on a value in another column) and that part works. I know I'll always have 2 columns but I don't know the amount of rows. I'm calling a Sub that does an if-test and copies the valeus into the array, then I'd like to "reDim" the array 1 row larger for the next pass.
Column1 Column2 Text1 Value1 ... Text2 Value2 ... Text3 Value3 ... To get an array like so:
Arrayindex(i,1) Arrayindex(i,2) Text1 Value1 Text2 Value2 Text3 Value3 Depending on how I make initial declarations I either end up with an "subscript out of range" or "array already dimensioned" error. So I'm apparently not getting the right syntax in the dim or redim?
I am trying to loop through the values of an array that was populated from a range. Essentially I'm trying to manipulate a pivottable depending upon what a person selected in a control form. Below is my Dim userWeekArray As Variant userWeekArray = Sheets("Valid Values").Range("A20:A27") For w = 1 To 8 If .PivotItems(w).Name = userWeekArray(w) Then .PivotItems(w).Visible = True Else .PivotItems(w).Visible = False End If Next
I get an error message stating that Subscript is Out of Range. It seems Excel is assuming that userWeekArray is a Range object when I want it to be an Array. How can I populate an array with elements from a range in a worksheet so that I can then easily loop through the array to examine the elements?
I'm trying to do a loop through four arrays named Asc, Bsc, Csc and Dsc. I'm using the following code to do this but it comes up with a Type Mismatch error on the penultimate line. Please could someone explain the problem? I'm getting a bit confused as to whether I'm mixing up different types of variables, and not sure if CVar is the appropriate function.
For ch = 65 To 68 Sc = CVar(Chr(ch) & "sc") For r = 1 To 3 If Sc(r, 1) = "" Then 'ERROR HERE etc...
Option Base 1 Dim Covariance(1 To 5, 1 To 5) As Double Dim Mean(1 To 5) As Double Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double Dim PortfolioValues(1 To 10, 1 To 2) As Double
And my main function:
Function Generate_Portfolio_Values()
Dim a As Integer, b As Integer, c As Integer Dim m As Integer
