Method For Running Hundreds Of SUMPRODUCT Lines
Aug 22, 2012
I have a very large spreadsheet that included a large number of SUMPRODUCT formulas. Many of them are running formulas that have SUMPRODUCT layered.
I have most of the SUMPRODUCT lines running 2 arrays to check for the date field. It sees if the date is >= the start date cell and <= the end date cell.
It runs fine, and works great, from a data standpoint. However, I am already running into slowdown when altering the date fields. The workbook is still in the starting stages and is going to get MUCH larger.
I have attached a sample sheet showing a little bit of what I am talking about. The SUMPRODUCT functions on sheet 3 all point to the start and end date fields, and the data updates as they are changed.
I need to find a way to condense the SUMPRODUCT lines. Is there a way that I could run one SUMPRODUCT line in a cell and then reference that cell for all of the other lines? Or is this just the best way to do this?
Streamline.xlsx
View 4 Replies
ADVERTISEMENT
Feb 13, 2013
I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.
My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.
I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.
Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?
Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.
View 1 Replies
View Related
Mar 31, 2009
I am having a problem with a sumproduct strand. I've written 4 different ways to no avail. What I'm trying to accomplish is to select specific the sum of various lines of data that fit within 3 parameters
1.) =SUMPRODUCT((Sheet1!$H:$H="XYZ"),(Sheet1!$G2:$G136="Withdrawal"),(Sheet1!$I2:$I136="113183"),Sheet1! $K2:$K136)
Response: #NUM!
2.)=+SUMPRODUCT(--(Sheet1!$H2:$H136='Sheet2'!$A$5),--(Sheet1!$G2:$G136="Withdrawal"),--(Sheet1!$I2:$I136='Sheet2'!$B$5),Sheet1!$K:$K)
Response: #Num!.................
View 2 Replies
View Related
Mar 12, 2008
I am encountering a problem with worksheets.copy that occurs after the copy method is called a certain amount of times. My issue is similar (exactly the same?) to what is described in these threads:
- Worksheet Copy: Method 'Copy' of Object 'Worksheet' Failed
- Copy Method of Worksheet Class Failed in Loop Code
In my case, after running this code 40 times, it fails.
If loc_after Is Nothing Then
.COPY after:=Worksheets(sht_name_orig) 'throws error here after being called 40 times
Else
.COPY after:=loc_after
End If
I would like this code to be able to run more than 40 times. I have read Dave's suggested solution regarding creating a worksheet template:
http://www.ozgrid.com/Excel/excel-wo...-templates.htm
However, in my situation the client has specifically asked that the report come as a single stand alone excel file with no add ins. In addition, I am copying several different worksheets, not just one single template that is being used over and over.
View 7 Replies
View Related
Mar 9, 2009
I have hundreds of .dbf files that I am manually converting to .csv using Excel. Unfortunately, my .dbf collection is growing faster than I can convert them. Is there a way to convert all the .dbf files in the current folder to .csv? All the .dbf files are in the same folder.
The path to them would be the same as the macro file.
View 9 Replies
View Related
Aug 10, 2008
ive got table consisting of about 4000 entries. Below is a small example.
[url]
I want to be able to make a button through macro's, where when pressed, it shows only certain postcodes - the way i've been doing it so far is using a combination of If, And, and Or functions to return true or false if a postcode begins with either 'AH4, AH1' and has an order number beginning with 'W', for example. Then I'd record a macro where it shows the ones showing true, via data filter. The problem I've got is I need to search for about 50 different combinations of postcode, out of a list of maybe 300 types of postcode, and using my current IF,AND,OR functions, I simply run out of room in the formula bar! For example, I need find a postcode beginning with CB1-25 (i.e. CB1, CB2, CB3) etc..etc.. the only problem is if i use my current LEFT function, and i want to search for say, CB1, CB14, CB15, out of a list of 'CB1,CB14,CB15,CB16', it will still return a postcode beginning with CB16, due to the CB1.
In the link I've highlighted the postcodes I want to search for out of that small list, and hopefully any solution I'm giving here I could expand to up to 100 combinations out of 500 combinations of postcode, for example.
How do I get it to search for a massive combination of criteria?
View 14 Replies
View Related
Jan 24, 2009
I have hundreds of numbers in column A and I want to combined these numbers in call B1 with “ , ”
e.g.
column "A"
451
45
61
48
194
Call "B1"
451,45,61,48,194
I am using attached sheet for this thing but I can’t use any other new sheet, is there any another way to do this. I need formula. So I can use any sheets.
View 10 Replies
View Related
Aug 3, 2012
I am tasked with making a matrix that I cant seem to solve.
I have solved it on a small scale using
=SUMPRODUCT($E$5:$E$19,--ISNUMBER(SEARCH(O11,$C$5:$C$19)),--ISNUMBER(MATCH($D$5:$D$19,{"One","Two","Three"},0)))
The issue is that I need where {"One","Two","Three"} is to contain between 3 and 334 conditions.
Basically we have a data table that contains "channels" that have multiple affiliates under it. Then we have a data table by affiliate by country and revenue... I want revenue by Country By Channel...
View 3 Replies
View Related
Aug 29, 2012
I have this value in a cell 38761.8296804781 which is displaying as 38,762. I want to display it as $38,761,829. Is this possible without going through each cell and moving the decimal point?
View 3 Replies
View Related
Aug 17, 2009
I have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.
View 5 Replies
View Related
Apr 28, 2013
I am using Excel 2010
I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.
I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.
How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.
Here is a visual demonstration of how things currently are and how i'd like to get them to be:
As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.
Pic1
How this looks when i try to print, it's only on the left side. right is all blank
Pic2
How i want it to look like upon printing
Pic3
As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.
[URL]
View 7 Replies
View Related
Dec 10, 2008
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
View 3 Replies
View Related
Oct 28, 2008
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies
View Related
Feb 18, 2014
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
View 3 Replies
View Related
Nov 22, 2012
I have a sheet which looks a bit like this
Day
A
B
Day
Cost A
Cost B
[Code]....
For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.
The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)
How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?
View 1 Replies
View Related
Feb 19, 2014
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7
K,6,7,7,A,9
To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
View 8 Replies
View Related
Jan 1, 2008
i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.
how would i go about eliminating the duplicate strings of text quickly?
View 9 Replies
View Related
Jun 20, 2009
I need a msg to pop up when a worksheet opens. Pretty simple:
Private Sub Workbook_Open()
If MsgBox("My msg goes here!") Then
Exit Sub
End If
End Sub
But I need to be able to have multiple lines, maybe a short paragraph.
How do you get it to allow additional lines?
View 3 Replies
View Related
Aug 15, 2012
In Column B I have some measurements. These are measurements. I chart them in a line graph. I also have my max and min over in some other cells. These aren't the min and max of column, but these are what I have set the min and max at. To make this clearer, my max is 27, but the data in Column B could be anything....20, 28, 35, 15, etc.
I want to plot my data in Column B just as I am currently doing. However I also want to plot the min and max on that same line graph and show them has horizontal lines going across. That way, if one of my numbers is above the max (or below the min), you will be able to see it on the line graph.
Here's what my data looks like. The Viscosity (column b) is what I'm charting on the line graph and you can see the min and max data that I want to plot as well.
Sheet1
*
A
B
G
H
I
J
2
Batch No.
Viscosity
*
*
Min
Max
3
4028634
26.7
*
Viscosity
21
27
[Code] .......
View 9 Replies
View Related
Jan 22, 2007
There are four lines of numbers, in all line 8 numbers.
1 1 2 2 3 1 1 2
1 2 3 1 2 2 2 1
2 1 1 3 1 3 1 2
1 2 2 2 3 1 1 2
I would like to find a function that will examine, if there are similar lines according to my definition, and several.
my definition is 7
For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.
View 9 Replies
View Related
Apr 26, 2007
I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.
View 9 Replies
View Related
Jan 10, 2009
I have a text file which is wrapped and i am trying to read it through VBA. I want to combine wrapped lines in one line
for e.g.:
abcdefgh
ijklmno
pqrstu
vwxyz
need
abcdefghijklmno
pqrstuvwxyz
View 9 Replies
View Related
Jan 14, 2009
Is it possible to have worksheets in 2 or more lines at the bottom of excel window?
View 9 Replies
View Related
Oct 22, 2009
How to get together all duplicate lines? ...
View 9 Replies
View Related
Dec 28, 2009
I need a macro that add's after each 9 lines 2 new rows. The data I've it's not the same every month, one month I can have 27 lines the next one I can have 90 lines for exemple.
I need also to calculate in one of those added rows this "=B8+B4-B6". This calculation is to be apllied in each block of nines lines.
View 9 Replies
View Related
Mar 14, 2007
I manually added a center header to my report that take 4 lines
So I would like to repeat this using vba
Line1: Looney Tunes, Inc.
Line2: Bugs Bunny Subsidiaries, Inc.
Line3: Consolidated Rollup
Line4: FY 2007
Furthermore, I would like to read this from cell(s) as at least a 52 page report
and lastly I would like to alternate font sizes
Line1: 18 pt
Line2: 18 pt
Line3: 16 pt
Line4: 14 pt
View 4 Replies
View Related
Jun 12, 2005
I like to create a float chart in excel. for this purpose I need to have a candle chart and add lines into the same chart. I can create a candle chart in excel. But as soon I add lines to it (additional data rows) the candles are distorted. The principle I'm interested in applies as well to: how do I add moving averages into an excel candlestick chart? As soon as I add the data for the MAs, the candles are distorted.
In short: how do I combine candles and lines in one chart?
View 8 Replies
View Related
May 8, 2014
Recently I work on a project involving various formulas and functions in it.
At this moment I am trying to add some option to my file and I will explain briefly.
I intend to include formula which will number the cell in a line (row) when it is filled with some data.
I guess it is connected with array formula and also may be COUNTIF function but could not figure out how to use it.
I will upload a sample file which will show the idea.
Sample file: Number the lines.xlsx
View 4 Replies
View Related
Sep 5, 2008
I have an excel document that looks all nice and pretty except for the little white grids all over it with unused cells. Is there anyway to hide the lines? i dont care that the cells are there just the lines make the form look kinda crappy.
View 4 Replies
View Related
Oct 23, 2008
i really dont know how to do it. remove these lines in excel sheet???
View 2 Replies
View Related
