Locating Next Similar Condition In A Range And Performing Calculations
May 11, 2007
I have a set of data with names in col A, Currency in col B, Amount in Col C. I want to convert all the amounts in NON-USD based on exchange rate I input daily into column D.
How do i search down the row for the next name that is denominated in GBP / YEN etc and keep converting until I dont find any more other currencies in it. I tried to improvise on the finding nth but couldnt get anything out of it. Have attached a spreadsheet.
View 9 Replies
ADVERTISEMENT
Feb 26, 2008
i've managed to get a set of cells to show (with comments) what the last weeks data looked like. (so cell value = this week, comment contains last week)
What i now want to do is to use conditional formatting to highlight any major differences between the figures... so:
My comment i'm sure is stored as text in the format:
Previous value was
xxxxx
So the first thing i'd need to do is to to strip out the text at the start, and then perform the calculation.
View 9 Replies
View Related
Oct 22, 2013
When you filter a spreadsheet, you can find results that are similar to a condition. I have families of products that share a common 3 digits, but the rest of the model number is different. The 3 similar digits could be the first 3 digits, or it could be 3 digits starting with the 2nd digit. This spreadsheet has each complete model number on its own row. In that row is a number that represents a volume. I want to be able to sum the volume column if the model number contains the 3 digits.Model Number
Volume
A hat
5
B hat
1
A glove
4
hat
8
In the above example, the total for "hat" = 5 + 1 + 8 = 14
I would type in a condition (family name) into another cell (D1) and the formula in D2.
View 8 Replies
View Related
Mar 28, 2014
I am creating a range of numbers from 1 to 2000 with increments of 0.5. Then I have to subtract certain range, for example, 100 to 350. So, the result should display, 1 to 95.5 and 350.5 to 2000.
View 4 Replies
View Related
Jul 6, 2008
I have a range of date values that span B1:BA1 and I need to find the next date value greater than TODAY() and return the column number.
View 14 Replies
View Related
Oct 9, 2012
I would like to sum the values of a range using something similar to vlookup. The problem with using vlookup is that vlookup returns only the first instance. I have multiple values therefore vlookup would not work. I have 2 variables which is causing my frustration. I have added an example to explain. On the column where it says "correct answer" is where the formula should go. I displayed what the formula should produce so that it explains what I want as the output. The "static column" which contains variables 1 and 2 doesn't change.
The two variables it needs to search for is shown in the variable 1 and variable 2 columns. And the lookup range is on the left.
So basically it needs to match the two variables in the range and then grab the sum of ALL quantities where the two variables match . quantity amount is found on the leftmost column.
Is this possible. sumif works but only with 1 variable.
LOOKUP RANGE
formula column
static column
static column
formula column
static column
static column
quantity
variable 1
variable 2
correct answer
variable 1
variable 2
[Code] ......
View 3 Replies
View Related
Nov 5, 2009
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View 4 Replies
View Related
Jul 11, 2012
Is it possible to have an input box for a range of calculations and then in my formulas set the range to anchor + variable ?
Something roughly like:
Range = 20
Product( F13:F & Range_Variable_Cell_Value)
View 2 Replies
View Related
Jan 8, 2013
I have attached the file, Problem.xlsx
How I will populate data of Weekday through the cells.
Is there a formula or function to populate data.
View 1 Replies
View Related
Feb 27, 2010
I'm trying to get my head around using this syntax for EVALUATE() to allow an entire range of values to be changed all at once instead of via loop.
This macro works:
Sub TruncateValuesInSelection()
'JBeaucaire 2/23/2010
'Select a range and then run the macro to truncate values without looping
Dim Cell As Range
With Selection.Cells
.Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),TRUNC(" & .Address & ",2))")
End With.........
View 9 Replies
View Related
May 11, 2009
The sheet has a price list (I attached the sheet). its a width x height(drop) format. If width or height <= minimum width/height then use the minimum listed. if width or height > minimum <=maximum then lookup in table next heightest value. here is the complication. any oversized items are priced as roundup((size -biggest size) / (biggest - second biggest size),0) * ( price of biggest-price of second biggest). so if my widths are
4600 4700 4800
10 25 35
and I am pricing 5050 I would do :-
calculate howmuch its oversize
5050-4800 = 250
Calculate the difference in the last 2 sizes
4800-4700 = 100
Calculate the rounded up multiples
250/100=2.5 rounded up = 3...........
View 3 Replies
View Related
Apr 22, 2009
I have a VBA method that goes thru and calculates a bunch of stuff and puts this calculated info into a static data structure. This method is called 'setProjectInfo()'. This method is reliant on values on my worksheets, So as the user makes changes to the worksheet, I want setProjectInfo() to get called so it refreshes the data structure with updated information. I then have a bunch of helper methods to access different parts of this data structure. These helper methods are used in the formulae of a bunch of cells on a worksheet (ie '=getPercentComplete(period)', etc, etc).
This is all working well except for one thing. Sometimes the helper methods get called BEFORE setProjectInfo() gets called.. So, the helper methods return stale information. I can only get around this by manually recalculating the spreadsheet again. setProjectInfo() does enough stuff and takes a long enough time that I don't want to have to call it at the top of each helper method.
I've put the call to setProjectInfo() in a cell formula at the top of the my spreadsheet in hopes that Excel would know enough to call it first.. But that does not seem to work. Net, how can I get VBA to always call setProjectInfo() BEFORE it calls any other VBA methods?
View 3 Replies
View Related
Jul 12, 2013
I'm a fairly new user to using VBA and I'm currently running into a little snag. I'm trying tu create userform which will let me know if a person already exists in my sheet by looking up the last name in "A", then looking up his first name in "B", if such entry exists then display a message saying " Yes". If entry user has not been entered in the sheet then use a combox, to select the reason , then choose a date ( i have a working date picker ). Then entry should be entered in sheet (Name, Surname, Reason, date).
Whats working, I can use all of the above to enter all the info into the sheet, what I need to do is verify if user is alread in there, if not, then add it. The so on and so forth
View 3 Replies
View Related
Aug 5, 2008
I have the following code which previously was showing an error on the Do While line. It was suggested that I change the line too Range("B2").Text. However, the loop is not deleting the rows based on "#N/A". Can anyone offer any suggestions?
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess .....
View 9 Replies
View Related
Mar 13, 2009
I have a very large grid of data, about 400Rx200C, which contains a lot of blank fields. All of this data is currently in values from 0-100 representing percentages, but not formatted as such. I need to make all of these values into Excel Percentages so naturally used Paste Special and Divide from a field with the value 100 in it and then simply format the cells as %.
However, this fills in all of the original empty cells with a 0.000% which I do not want, I need them to stay blank. And some of the cells that do have values also have 0.000% so I cannot just delete all of these values with a VBA script. I had been hoping the Skip Blanks option would do this, but it does not unfortunately.
View 3 Replies
View Related
Sep 22, 2009
I am trying to write a Macro that will do the following.
1. Using a new workbook
2. Open a file in a in a directory. Then
3. Run a vlookup using the opened file as the filename in the vlookup
4. Paste the results of the vlookup into the original file next to the file name.
5. Close the file and open the next one in the folder.
The folder contains about 2k files that it needs to loop through. I found a macro on this site created by Ravishankar and attempted to add to it. Not going so well.
View 2 Replies
View Related
Jun 17, 2013
I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:
Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures
[Code]....
View 4 Replies
View Related
Jan 19, 2012
The data is a copy of CSV file in to a spreadsheet. Below is what the paste looks like:
100100 Books
1234.ab
1345.bf
1456
1567
100100 Books
100200 Magazines
100300 Videos
1234.ab
1345.bf
1456
1567
100300 Videos
All of the numbers between the 100100 Books, 100300 Videos belongs to that category. This repeats hundreds of rows down. I need the column to the left of this range to display which division it under. I parse this in excel so Books, Videos, Magazines will be in their own column. I am not exporting the CSV so have to manipulate it in excel. I need to have the column to the left combine the whole identifier so I can perform a VLOOKUP on it. It should look as follows:
100100.1234.ab
100100.1345.bf
etc.
My approach was to get the column to the left of the range to display the correct division, then have another column combine by using (cell)&"."&(cell).
View 1 Replies
View Related
Nov 20, 2006
Office 2003 with Windows XP
This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want
I have an Excel sheet with data
A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.
A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM
[Sample]
a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone
This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source
my desired output would be:
time Phone email
12:00AM
to 2 3
12:30AM
My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.
I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)
Basically, pull the hour from a2 and evaluate the mins/secs.
If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.
Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
View 9 Replies
View Related
Nov 21, 2006
I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:
=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"")
Everything works fine, until I attempt to…
- cut & paste a block of cells, or
- do a SaveAs, or
- change a worksheet name
The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.
The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).
I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.
After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.
I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.
Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8):
Dedicated Cell To Choose Lookup Table
View 9 Replies
View Related
May 14, 2009
I've got a table similar to the below (but with a few more columns & rows). I need 2 look at 2 cells one contains a $value (say $75,000) the other contains a code (say C). I need it to return the correct value - in this example 3.00%.
This is going to be too messy to use a vlookup, I'm sure there is something better. Is this where you use Match or Index ... both of which I don't understand how they work. Or something else ?
ABCD
$ 1 $ 50,000 1.00%1.50%2.00%2.50%
$ 50,001 $ 100,000 2.00%2.50%3.00%3.50%
$ 100,001 $ 250,000 3.00%3.50%4.00%4.50%
A B C D
$1 $50,000 1.00% 1.50% 2.00% 2.50%
$50,001 $100,000 2.00% 2.50% 3.00% 3.50%
$100,001 $250,000 3.00% 3.50% 4.00% 4.50%
View 9 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Oct 7, 2006
I need to check a range of cells (B4:B35) and see if any of the contents are less than a specified cell (M1) and then show a message, (the message part I can do). I have tried using For Each but I then get the message for every cell that is below the specified cell (which in theory could be all of them). I have also tried using an If Any statement but didnt work.
View 5 Replies
View Related
Nov 19, 2009
I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?
View 4 Replies
View Related
Apr 8, 2014
I have used a vlookup code from a past post (Vlookup multiple values in one cell) in VBA and successfully retrieved my information to one cell.
However, I would now like to remove any duplicates in this cell; preferably while running the same code.
--vlookup code
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range
[Code].....
View 2 Replies
View Related
Oct 24, 2008
How can I locate 1000 or the closest number from column 5, and all the data to the left of it? I have tried all the commands I could find, and nothing has worked for me.
This is the data I want to pull from the table.
1.463149.6271.0440.985999.87
View 11 Replies
View Related
Sep 15, 2012
I need a macro that identifies duplicates within a spreadsheet. Unfortunately, the utility provided by Microsoft is not adequate because the text strings that are potential duplicates are nestled inside other text.
Fortunately, however, there is a unique text string that will locate the duplicated data. Below are two examples:
Plus -- Zero Box ==> Zero Box Medium ID=7288 (submittedby Anna Johansson)
Basic -- Zero Box ==> Resolve Medium ID=6397 (submittedby Kazy Suzuki)
The data that needs to be identified as potentially duplicated is the four digit number (i.e. 7288 and 6397). You will notice that they are preceded by “ID=”.
The spreadsheet has over 26,000 rows of data and 1700 plus IDs. The characters ID= proceeds the four digit number in all cases.
I simply need a macro that will provide a list of the four digit numbers that are duplicates. This will allow me to easily locate and manually delete any surrounding, unwanted data from the spreadsheet.
The data that the macro needs to search is contained in and restricted to Column C.
View 2 Replies
View Related
Dec 24, 2012
I want to assign macros to buttons from VBA. I know that buttons have a TopLeftCell property and I've used this before to point at cells around the button but not the other way round.
What I'm trying to do is have excel assign a macro to a button when the workbook is created (if thats possible - if not then just whenever it's opened the macro can be reassigned).
The recorder gives me the line of code:
Selection.OnAction = "ThisWorkbook.ImportJobsButton"
so I really want to change Selection for something that points at the specific shape. I want to do this with a few shapes.
View 1 Replies
View Related
Jan 12, 2010
Take a look at these columns below;
.00 810.00 .00 1,729.00 .00 1,594.00 .00 1,599.00 .00 498.00 498.00 .00 .00 1,852.00 .00 454.00 .00 50.00
I want to find the cell reference number of any value of column A (which is greater than 0) in column B. I could only manage to find values with my formula i.e.
=IF(A1>0,VLOOKUP(A1,$B$1:$B$9,1,0),0)
View 9 Replies
View Related
May 13, 2014
The spreadsheet has over 200K rows and two dozen columns. My job is to:
1. There are two columns, one is called OCR code where we have repeating ID's and other column is called Minutes Count which is basically a number. I have to filter the OCR column for similar OCR codes(values) and then add their time(Minute Count) so that I get total minutes for a specific OCR code. After adding the min I will copy them into a new sheet with the code in one column, total minutes in another and number of repeatitions for that specific OCR code or count number in a separate column. Example:
OCR_Code; Minutes
54xg; 456
45fk; 65
23IS; 18
54xg; 971
45fk; 265
.
.
.
Now I've to filter 54xg so that I can only see it and its corresponding minutes. Then I will add up all min for 54xg. Then I will count the number of instances a 54xg has been repeated in spreadsheet(here its 2 but it can be in hundreds in my case). Last thing is to transfer all this data to a new sheet. And automatically repeat the process for other OCR codes like 45fk, 23IS etc...
View 3 Replies
View Related
