I am trying to multiply one array of prices for multiple input products across
the volume that that product my go into multiple end products.
I know you can use SUMPRODUCT with two arrays that are equal BUT I want to Multiply the price matrix across 15 or 16 other matrices and sum all the products.
The price matrix and the other arrays are all 1 x "whatever".
I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP.
I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them.
Example
C F G H
CUSTOMER JULY AUGUST SEPTEMBER
customer1 2 3 4
customer1 2 3 4
customer2 2 3 4
customer2 2 3 4
The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H).
The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would:
sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName
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.
EX.
Array 1. - 20 potential choices (Service codes)
Array 2. - 2 choices (pkg types)
Array 3. - 2 choices (volume type)
Array 4. - 3 choices (company names)
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 columns of data in an excel sheet that are equal in size and both only contain numbers.
I'm trying to write a macros which will put both of these sets into arrays, then perform a calculation on them.
Specifically ArrayA + 3*ArrayB then put the result back into the worksheet in a new column. Below is the code I have so far.
Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC As Variant
ArrayA = Worksheets("Sheet1").Range("A1:A5")
ArrayB = Worksheets("Sheet1").Range("B1:B5")
This is where things go bad
ArrayC = ArrayA + 3 * ArrayB
Sum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.
Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].
I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.
Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400
In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
I've been trying to figure out a way to sum up a column of times like this (please see attached portion).
Right now, all the cells on the worksheet are formatted as text, and the "total" is actually a text value, not a formula.
I've tried converting all the cells from text to numeric, even tried custom formats like [h]:mm.ss to no avail.
What is happening for me is that when I try to sum up a column to get a total, the value gets converted or rounded off to zero (usually like 0:00.00).
I have to maintain freight cost according to the shipment weight. Our shipper has different charges for different scale/quantity of shipment. For example, according to the attached picture; Rate for up to 10 Kilo is $ 20 per kilo. For weight more than 10 kilo and up to 20 kilo, Rate will be $ 15 per kilo and so on.
Now, suppose my shipment weight is 28 kilo. My cost will be $ 200 for 10 kg, $ 150 for next 10 kg and $ 80 for the last 8 kg. In total $ 430.
I have to calculate the total cost separately. I wonder if I could devise a formula by which I could put the weight in a single cell and get the freight cost instantly.
I have read in 2 blocks of data as 2D arrays, and wish to create a third 2D array from some of the elements of the first two. When I do this I get an output range that is the correct size, however each row of data in the range is identical. This is what I am using:
[Code] .....
So my main concern is how to get each row of data being printed to Sheet2 to be the correct rows, and not just one row repeated thousands of times.
an array formula to combine the array formulas in columns B, C, and D? I would like the results displayed in one cell....
View 9 Replies View RelatedI am trying to subtract two values, which are calculated from arrays. Here is my actual code:
[Code] ......
If I remove the "-", I get the sum of both arrays as expected. If I include the "-", I get the "#VALUE!" error message.
I am trying to develop some code to serve as an 'undo' for several macros that I have. They each take a selected range and perform some changes to that range. I have managed to make some code that will undo the last macro run but would like to make something that can go back several steps. To that end, I have started with the following code but am running into an issue when I need to use a variant array to hold multiple arrays of a custom defined data type:
VB:
Type SaveRange
Val As Variant
Addr As String
[Code] .....
This declares some public variables I'll need, each as an array so that I can iterate through several steps of do/undo using the undoIndex. I then use them to save each range I am about to change by calling the following macro within my actual data-manipulating macros:
VB:
Sub Save_RangeForUndo(rng As Range)
Dim i As Integer
Dim cell As Range
undoIndex = undoIndex + 1
[Code] .....
If you look at the last line here, this is my problem; I can't figure out how to properly use an array to hold each instance of OldSelection() for later referral. Just for completeness, here is my actual undo macro:
VB:
Sub Undo_Operation()
Dim i As Integer
OldWorkbooks(undoIndex).Activate
OldWorksheets(undoIndex).Activate
[Code] .....
I need to get this OldSlctVariant()() array to hold each instance of OldSelection() so that I can restore them for each consecutive undo. I'm not very familiar with the variant type and anything more than the basic use of arrays.
I've written this function to re-dimension an array based on the size of the range it is storing. Originally all my variables were simply declared as:
Code:
Dim XLRecOutput() as Variant
I then wrote a function which took the variable and the range as follows:
Code:
Function LoadRangeToArray(dataArray() As Variant, selectedRange As Range, Optional blnLoadData As Boolean = True)
With selectedRange
ReDim dataArray(1 To .Rows.Count, 1 To .Columns.Count)
End With
If blnLoadData Then dataArray = selectedRange
LoadRangeToArray = dataArray
End Function
To call the function, it's simply:
Code:
XLRecOutput = LoadRangeToArray(XLRecOutput, Range("XLRecOutput"))
Now I've created a class and set up Get/Let properties for the variables instead, but the above function fails with a Compile error on the call - 'Type mismatch: array or user-defined type expected' and I can't get my head around how to resolve it.
I need to calculate a Sums of Products with unusual requirements. Please refer to the attached picture. The Orange Cells under "Noise Weight" are the Formula Cells. The Values in those Cells need to evaluate as shown in the equations below. I want this to be dynamic, so that if I expand the FM Region (Add Columns between "Noise Weight" and "FM15") AND expand the Severity Region (Add Rows between Severity and Noise Weight), I will not have to update Formulas manually. I know for sure that the standard SUMPRODUCT Function cannot handle this.
PRODUCT 1 = D29*E31 + D28*F31+D27*G31.....+D15*S31
PRODUCT 2 = D29*E32 + D28*F32+D27*G32.....+D15*S32
PRODUCT 3 = D29*E33 + D28*F33+D27*G33.....+D15*S33
...
....
PRODUCT 7 = D29*E37 + D28*F37+D27*G37.....+D15*S37
I've created an array that is 60 pairs of cells wide. Each row in the array represents a different account. The 60 pairs of cells contain up to 60 payments in a payment stream. The first cell of each pair may contain an amount of interest to be paid, or zero if the time band in which it was/will be paid is not within the time range that I'm evaluating. The second cell of each pair may contain a number between 1 and 10 representing the time band in which the interest will be paid, or zero if the payment date falls outside of the time bands.
I need to be able to sum the interest to be paid in each time band for all of the accounts in my database.
Is there an Excel function that I could use or will I need to write some VBA code to loop through each row?
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:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
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
See attached sheet "Selected estimate", Cell B4 and Down, This was working before I made some edits to the "checklist" sheet. I have hit C+S+E a million times and I cannot figure out how I broke this forumla. Btw, this is excel 2007
View 2 Replies View RelatedI have two column on two seperate worksheets. One worksheet has both columns filled out. On the second worksheet, I want to be able to type in info in once column and have excel pull the other column from the other worksheet.
WKSHT 1 WKSHT 2
ID Name ID Name
1 Bill 3 ?VLookup?
2 Steve
3 Mike
Ive tried using Vlookup('worksheet1'!A2:A4,'worksheet1'!B2:B4,2) but can not get it to work.
I would like to copy the existing sheet "Template" 17 times and each of the copied sheet should be names according to the order in the array:
"XX","TT","YY","WE","TG","KJ","IO","RT","EF","VU","GF","DW","QA","EZ","QU","OF","BB"
how this is done in VBA?
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.
View 1 Replies View Relatedi want to do is import multiple text files,with fixed arrays (luckily it's standar )
I have the piece of code that i currently automatically importing these .txt files,although it's for one per turn.Here it is though,to see the arrays
what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
View 9 Replies View RelatedI'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
=INDEX($C$2:$Q$51,MATCH($B2,$A$2:$A$51,0),MATCH(G$1,$C$1:$Q$1,0))
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
View 8 Replies View RelatedI track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I need creating a formula for the following situation.
I need to link multiple arrays from different sheets. But am having a hard time concocting a formula that will work.
I have 2 columns of data on sheet1. For example:
A-------------B
apples-----1245
oranges----456
nuts--------384
etc.
Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:
A-------------B
apples------West
oranges-----East
nuts---------West
This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.
I'm looking to look up sheet name based on a date, then look the date up within that sheet which is a merged cell, then return the figure in the corresponding cell.
I have a sample worksheet to attach, just have to work out how to do it.!
In the sheet "Cashflow Summary" in cell E24, i want to lookup the sheet name based on the value in C22 (merged cell), then lookup the date in that sheet (6 August 2014), and return the value in cell y8.
The difficulty is with firstly the merged cells, but also performing the lookup over a number of different arrays, as the sheets for each month, have the months listed under each other in rows of 5 days at a time.
Multilookup based on Multiple criterion without using Arrays
Dear Forum,
I am well aware of the oft-mentioned Array Solution which gives us more than one record based on mulitple criterion using the INDEX function..
the formula layout being as mentioned below:
[ ={INDEX(A2:A100,SMALL(IF((B2:B100=<Condtn 1>)*(C2:C100=<Condtn 2>),ROW(D2:D100)-ROW($D$2)+1),ROW($D$2:$D2))} ]
I like this formula a lot and have used it more often, however it has made my file exceedingly slow..
Considering that it takes every small change in the file to compute around 5 mins or more than this the whole purpose seems to be defeated, I have read a lot of posts on Array Vs Non-Array of late by DonkeyOte.
I have code that runs through multiple arrays trying to match data to items in the arrays and it takes a long time to run.
Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long
dim MyAarray as variant
[Code] ..........
That's basically what the code does. however, it takes an extremely long time to get through with everything as each array increases in size.
