"Ever Growing Values Constraint"
Apr 20, 2009
I'm having trouble figuring out how to put a particular type of constraint on a column. The data entered in each successive cell in the column (going downwards) must be superior to (or superior or equal to) the previous cell, i.e., the one immediately above it.
I've searched on the Net and don't even know which key words to include. Can I use the solver to do this? Must I write a short script (I've never programmed anything in an Excel document). How do you make sure that this constraint is always applied even in the 1,000,000th line of data? A basic version assuming no blank lines would be great. Another version where blank lines are permitted would be cool too.
View 13 Replies
ADVERTISEMENT
Jun 19, 2009
In my problem I have 2 columns(a2:a351 and b2:b351), each containing data, however some cells are empty. I want to be able to count when each corresponding set of data(ie, a33 and b33) are both non empty cells. So if the entire data was to be all blank, the count should be 0 and if each cell had data in it the count would be 350.
I have been trying to use the count and sum functions in numerous different ways but have had no luck whatsoever. Here are some of the syntax I have been trying.
=COUNT(IF(A2:A351"0",IF(b2:b351"0")))
=SUM(IF(A2:A351"",IF(B2:B351"",1,0),0))
=COUNTIF(A2:B351,AND(A2:351"",B2:B351""))
View 9 Replies
View Related
Mar 13, 2013
I'm looking to do a countifs of the following formula, but I can't seem to get a result that counts those items in Column E as Unique results.
Code:
ActiveCell.Offset(0, -2).Value = Application.WorksheetFunction.CountIfs(Sheets("Table").Range("B1:B10000"), ActiveCell.Offset(0, -20).Value, (Sheets("Table").Range("C1:C10000")), "Self Trade, Off", (Sheets("Table").Range("D1:D10000")), "Sat", (Sheets("Table").Range("s1:s10000")), ActiveCell.Offset(0, -3).Value, (Sheets("Table").Range("I1:I10000")), "Completed") + Application.WorksheetFunction.CountIfs(Sheets("Table").Range("B1:B10000"), ActiveCell.Offset(0, -20).Value, (Sheets("Table").Range("C1:C10000")), "Self Trade, Off", (Sheets("Table").Range("D1:D10000")), "Sun", (Sheets("Table").Range("s1:s10000")), ActiveCell.Offset(0, -3).Value, (Sheets("Table").Range("I1:I10000")), "Completed")
View 1 Replies
View Related
Feb 25, 2009
I am trying to generate random numbers with the the constraint that the maximum value is 40 and the most likely value is around 40% of the maximum value. Are there any distributions (lognormal??) that would suit this requirement? Is there any way I can do this in Excel?
View 3 Replies
View Related
Jul 31, 2006
I am keepting track of all the calls i get so the range changes daily. In A
is the date and column J is who took the call.
Is there any way to take this formula and have it refer to one place for the
range end as my table grows?
=SUMPRODUCT((MONTH(Data!$A$5:$A$670)=MONTH($A25))*(YEAR(Data!$A$5:$A$670)
=YEAR($A25))*(Data!$J$5:$J$670=C$18))
View 9 Replies
View Related
Sep 1, 2005
I have a large column of data that’s about 500 rows deep. I enter a few points of data a day. I want a simple bar graph to chart that data in order. Simple enough.
I set up the chart and everything. But after 2 days the chart is all scrunched up on the left side. And the remainder of the chart is considered 0. I want the chart to grow as I enter data without changing the data range every day
View 9 Replies
View Related
Apr 13, 2007
I've been making a spreadsheet with lots of optionbuttons, and I'm slowly starting to understand how to link them to values, execute VBA script when their value changes, etc.
There is a problem that completely puzzles me. When I click the button (not in Design Mode), or any of the other buttons in the same group, they keep on growing! Please see the attached picture for an idea of what it looks like after a few clicks. I want the text and button size to be stable. I haven't changed the zoom, column sizes, etc. so I can't be that. They grow by simply clicking buttons in the same group.
View 9 Replies
View Related
Dec 1, 2006
The spreadsheet calculates the Up and Down Capture ratio with input for the fund and benchmark's monthly return, and results are displayed in cells F96 & I96. Because I don't know how to write a macro in VB, How to turn this into macro so that I can have the formula, =UpCapture(B:B, C:C)
because the input monthly return will change in length with different funds. the definition and formula to calculate up capture and down capture ratio is as Follows: (also the method I used in the spreadsheet)
http://support.pertrac2000.com/statbody.asp#upcap
View 7 Replies
View Related
Feb 6, 2008
I'm getting adding values from a worksheet using sumproduct. The formula is as follows:
=SUMAPRODUCTO((Datos!$F$2:F459)*(Datos!$A$2:A459=Tendencias!E3)*(Datos!$C$2:C459=Tendencias!$A$5))
But when i replace the range from row 459 to row 55000 i get the NA.... i need the range to be as big as possible as i keep adding data on a daily basis.
what do i need to add to the formula?
View 4 Replies
View Related
Jan 14, 2010
I have a workbook that grows in size on every use. I import a text file, manipulate the data, manually export it and delete the data from the workbook.
So why does it continue to grow in size? By manipulating I mean, add rows and text to the new rows. Attached is a copy of the workbook in native form (have not ran the macro) and a sample file to import, the excel file is 59kb in size and the test file is 9kb in size. I have seen my original file grow to over 500kb from repeating the same macro on the same file many times or running many different files for testing.
There is a CommandButton on Sheet1, it will open a userform. Click in the largest textbox and it will open a file dialog box in "C:". Place the supplied text file in "C:" or some other folder of your choice as you can navigate to any folder or drive from the dialog box. The macros that add lines and or text are "JumpToLabelName" (this runs first) and "InsertRows" which runs second. These two macros do all the work.
Take note:
I store information in the Registry, "GeoMeasure" under "VB and VBA Program Settings" (Normal windows key entry). You can search for "GeoMeasure" and delete it if you decide to test run the file.
View 5 Replies
View Related
Aug 12, 2008
I have a workbook that I am working on that seems to be having file size growth issues.
All it is is 2 worksheets.
Worksheet 1- Columns A-S & 63 rows. That worksheet alone is 7mb.
no macros..No VBA..No formulas. Just straight text data with a handfull of auto-filters. I took the auto filters off and saved to see if that was the issue and it did not make a difference.
Worksheet 2- Columns A-S & 35 rows. This worksheet is almost 3mb.
Now I can make a change (when my computer catches up) to 2 cells and sometimes it will jump in size by 1mb or more.
View 9 Replies
View Related
Jun 5, 2007
I have been reading up about shared workbooks within here which quite frankly is annoying me! I have rebuilt a spreadsheet from yesterday and shared it with:
Dont Keep change history
Update changes when file is saved
The changes being saved win
At start of day the file is 660kb, now by lunchtime I have it as 15mb. Does anyone know why this occurs? Becuase the file is getting bigger this is affecting performance
View 9 Replies
View Related
May 7, 2014
find macros to sort fixed ranges but not an entire column with existing header rows from 1 to 4.
Added to that is the need to keep the code compatible with Excel 2003 even though I have Excel 2010 at office (it's a file that needs to be "openable" in both versions so the vb code needs to be in XL 03.
Column currently starts at C5 but goes down to C47, at this point. But it's a growing list. Some of the rows are blank at the bottom, too.
View 2 Replies
View Related
Mar 6, 2008
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
View 3 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Mar 2, 2014
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
View 3 Replies
View Related
Jun 3, 2009
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
View 13 Replies
View Related
May 22, 2014
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
View 4 Replies
View Related
Mar 26, 2014
I have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)
IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)
Find the attachment & basic code take this code for this task
[Code] ....
toggle-2.xlsb‎
View 5 Replies
View Related
Sep 15, 2013
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
View 7 Replies
View Related
Jun 5, 2014
I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button
I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)
If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".
If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".
If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".
"C:UsersmaniDesktopNew folderLenvo_ReportsONSITE CasesVlokupuf" This is path i stored existing 3 file dispath,closed,cancel
View 3 Replies
View Related
Aug 14, 2014
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
View 14 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
Mar 26, 2014
I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.
Formula:
[Code] ....
View 2 Replies
View Related
Jul 2, 2009
I have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
View 3 Replies
View Related
Feb 17, 2010
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.
Example:
On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.
I have attached a sample spreadsheet for clarification.
View 7 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Aug 2, 2013
I am working on creating a spreadsheet that can be updated by those unfamiliar to excel. I have a master list on one sheet and three separate lists for business, individual, and general. Each list has three columns giving name, address and postal code. There are numerous repeats on these lists and I wanted to hide any duplicates while keeping my cells aligned. So far I have been able to highlight any duplicates, but was interested in some function that would save at least one duplicate while hiding any additional ones.
View 2 Replies
View Related
Jun 19, 2014
One aspect of my Excel-based project involves comparing the operator-entered part number (in Excel) to a predetermined list of part numbers in one column of an Access database table. Right now, my program is telling me that every part number entered in the spreadsheet (50+) does not match any part number in the database, which I've verified to be incorrect. I've checked that both the spreadsheet part number and the database part number are of the string datatype.
To the best of my knowledge, my looping logic seems valid and robust. To the best of my knowledge, there are no hidden characters in either the database cells or in the spreadsheet cells causing this apparent mismatch. I'm completely stumped at this point as to why my program doesn't detect any matches between the spreadsheet and the database table. Below is the Sub containing the code for checking that the part numbers match:
[Code] ....
This issue seems to be a hybrid issue between Excel and Access with (to me) more of the issue on the Access end.
View 11 Replies
View Related
Jun 5, 2012
I have .csv file from which the data is importing to master schedule. i have a column in .csv file which is spitted into multiple columns. and my need is i have to compare this each individual cell value with the range of header values in master schedule and if match found i have to place that cell in the row.
I am adding 2 attachments one is .csv and other is master schedule.
I couldn't able to find where i have to attach my files.
View 2 Replies
View Related
