Find, Meet Condition, Sum Range, Deduce, Find Next
Sep 23, 2007
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
View 9 Replies
ADVERTISEMENT
Dec 31, 2013
I'm developing a spreadsheet for a nonprofit that provides feedback on their major fundraiser/auction. I'm trying to create a formula in a cell that will look at the bidder #'s in column B and add the value of the their purchased items found in column C and return the number of the bidder that has the largest total value of purchases.
This can be done with a pivot table and give a table of all bidders, their total amount paid for items, and the number of items purchased.
View 1 Replies
View Related
Feb 1, 2010
The fuction works if the statement is found to be "True" but returns a value error
if the statement is "False". Anyone know the reason I'm getting a value error?
View 5 Replies
View Related
Apr 4, 2007
I need to look up a maximum value in a table depending on a condition. I have attached the spreadsheet. Basically, I need to find the maximum value for " Sum" column on sheet "Column Data" depending on the Coltype value on Column Summary. This should be returned to Sheet "Column summary"
For example, the formula should look in Cell A4 of column summary, see that Coltype is B, then look in column C of sheet column data and find the maximum value in column F:F of all the Coltype (column C:C) values that are of type B.
View 3 Replies
View Related
Mar 14, 2014
Let's say I had the following data ...
47
50
50
50
44
50
46
50
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
View 4 Replies
View Related
Nov 21, 2006
I have a spreadsheet with 3023 rows and columns A-L. Here's a small sampling of Column A (with heading "sku" included, copied exactly as it appears in the spreadsheet):
A
sku
3102-0400-100000
3102-0400-200000
3102-0500-100000
3102-0500-200000
3102-0600-100000
3102-0600-200000
3102-0700-100000
3102-0700-200000
I need to delete every row in which column A includes the text "200000".
Although the above sampling shows "200000" appears in every other row, that is not the case in all 3023 rows.
View 9 Replies
View Related
Aug 6, 2014
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
View 5 Replies
View Related
Aug 4, 2014
I set format condition in range by tool bar(=if formula is yes then display cell.interior.colorindex=3), but I have no idea how to find out (.address) the red colour cells are displayed in range with VBA?
View 5 Replies
View Related
Jun 6, 2012
I want to find the most recent date in column D, when the variables in column E are all the same. There are many different variables in column E, and for each group of the same variables (eg. all cells containing "A"), I want to find which is most recently dated (dates are in column D). This is my code so far...
Sub adddiv()
Dim ticker As Variant
Dim freq As Long
Dim csheet As Worksheet
Set csheet = Worksheets("Sheet2")
[Code] ....
View 3 Replies
View Related
Dec 5, 2006
I want to search a column, from A30 moving up to A10 for the first value in excess of 0.
Having found the value the search stops and I want to select the cell next to it in B column.
Then I want to check if a value in B column exists. If no value exists I want to enter a fixed value from another sheet.
If a value does exist I want to take no action and move on to Column C to repeat the checking process for columns C and D.
Then I want to move to the next spreadsheet and repeat the process.
The code I have written so far is below and I just can't get it to work.
I have attached a spreadsheet to illustrate the problem.
Sub Closingdata()
Dim a As Integer
Dim b As Integer
Dim rngOutput As Range
Dim shtTemp As Worksheet
Dim vntName As Variant
For Each vntName In Array("sheet1", "sheet2")
Set shtTemp = Worksheets(vntName)
shtTemp.Select
shtTemp.Range("a30").Select
Do Until ActiveCell.Value > 0
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
End If
View 7 Replies
View Related
Aug 21, 2007
I have a simple problem I am trying to find a set a values on a column and what to replace once found with the first 5 most left characters. Example:
in column A I can have text and numbers; the numbers that I want to find are in the following format xy:zw / xy:zw on each case the values can be from 0 to 9 so this is tons of posible combinations, I been trying to use **:** ? **:** but I don't get none. This is what I am doing
Set target = Sheets("Sheet1"). Range("A1:A9000")
For Each cell In target
If cell.Value = "**:** ? **:**" Then cell.Value = "00.00"
Next cell
A example would be 02:47 / 03:15 the next row could have 03:15 / 04:09
and so on however I could have text in some rows. In reality I would like to find and replace the cell.value with the Left 5 characters if the cell contains ANY value maching that format. Text in that column will never have that format combination
View 4 Replies
View Related
May 8, 2014
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
View 6 Replies
View Related
Aug 18, 2014
In cell A1, I have three possible text strings: (1) "change/s: changed the color green to red", (2) "change/s: changed the color from green to red, changed the size from big to small", or (3) "changed the color from red to green".
I need a formula that will look at a text string, and if there is a comma in the string, it finds the word "change/s" and substitutes it with "changes", if it finds no comma, it substitutes the word "change/s" to "change", and if the word "change/s" is not in the text string at all, it leaves that text string unchanged.
View 4 Replies
View Related
Aug 21, 2008
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies
View Related
Mar 31, 2008
1. I need a script to retrieve data (member number) from "Search List" worksheet and then to search it in "Members List" worksheet.
2. Once the search result (member number) found, e.g. 00311, it will copy the entire row to the "Only Selected" worksheet.
I have also attached a sample excel for better understanding.
View 9 Replies
View Related
May 23, 2008
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).
So now I just need it broken down by training type per month. How many x's in each column for February as an example.
View 7 Replies
View Related
May 23, 2006
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies
View Related
Oct 8, 2006
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
View 3 Replies
View Related
Aug 6, 2013
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
View 9 Replies
View Related
Aug 24, 2007
I recorded a macro to search for a cell with a specific string, and then modify that string. It works fine, unless there is no cell containing that string. If the spreadsheet does not contain the string, this error results:
Runtime error '91'
Object variable or with block variable not set
I need to modify the code to simply end the macro if the string does not exist, and although I'm sure it's really simple, I don't know how to do that.
Here is the code (minus initial comments):
View 9 Replies
View Related
Feb 20, 2007
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
View 4 Replies
View Related
Oct 10, 2009
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View 9 Replies
View Related
Aug 5, 2009
DSee the attached.
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.
BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).
I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.
View 5 Replies
View Related
Oct 9, 2006
I'm using the below VBA
Dim c, DataRow
With Data
Set c = . Range("A5:A350"). Find(KPI, LookIn:=xlValues)
If Not c Is Nothing Then
DataRow = c.Row
End If
End With
Now,If KPI is for example = "Favourite Hotel" and if i have data that looks like :
Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham
Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).
How do i make it search for an exact match?
View 4 Replies
View Related
Feb 12, 2010
I have a module that contains the lines below:
Dim myRng as Range
set myRng = Range("B1:B100").Find(what:= "Symbol")
I have run this module frequently and successfully over the past several months, during which time Column B has been hidden. When I tried to run it today I got a Run-time error 91: Object variable or with block variable not set. I checked to see that "Symbol" was present in the stated range (it was), and noted that when I went to debug the Run-time error, myRng was 'Nothing'.
Now I find that if I unhide the column before the set myRng statement, the code runs without a hitch.
My question is: Is there a known restriction on the Range.Find Method that prevents its use on a hidden range?
View 9 Replies
View Related
Jan 20, 2010
I have a table of financial data based on individuals contributing to a non-profit by year over 6 years.
I am looking for assistance in determining the first year of contibution, the last year of contribution and also the gap year if any of giving.
View 14 Replies
View Related
Feb 24, 2014
I am trying to find a specific value if the cell falls in between a certain range. Currently I am using a nested if statement to find this value but would like something easier to work on. I am trying to find the MAV based on weight. Currently this a formula that I am using.
[Code] .....
Attached is part of the table it is drawn from.
mav.jpg
Is there an easier way to do this? I want to have a value in a cell of say 100 g and that would return an MAV of 7.2 g.
View 1 Replies
View Related
Mar 23, 2014
Please see attached : Excel Problem.xlsx‎
In the Worksheet sheet there are two columns:
Value - value to find in the Report sheet which identifies the table
Assigned Name - name to assign the table
Basically, I need a macro that will:
> Find the value in the Report sheet
> Select the current region except the 1st row
> Name the selection the assigned name
So for example, for Total Received and Shipped (cell A2 in Worksheet):
> Find the value "Total Received and Shipped" in Report (cell B2 in Report)
> Select the current region except the top row (A3:C9)
> Name the selected range as "Total_Received_and_Shipped"
And vice versa for the other values in the Value column.
View 1 Replies
View Related
Jan 8, 2010
In vba code, how can I restrict my "find" to a given range? I'm dynamically building the range (anywhere from 5 - 100 cells in column B on my spreadsheet) and need to see if two values are present anywhere in that range. There will be multiple "ranges" to search, all in a single column.
View 2 Replies
View Related
Jan 27, 2012
In a range (a1:a10), how would I find the min and max? Then remove the min and max from the range. Then avg the remaining 8 calls.
View 6 Replies
View Related
