What is the formula I would need to use in the attached spreadsheet to calculated the lowest or first occurrence within a row? As you can see in the top table Product A is delivered in weeks 2, 4 and 6, what I need is the lower table to show the first week i.e. wk 2 that the product has been delivered.
View 2 RepliesIs there a formula which will result in the 1st occurence in a set? I would like the formula to look for "Y" in columns A, B and C and let me know the month in which "Y" first appears. I've attached a spreadsheet to make sense of this.
For example, Header Row
(A1) July (B1) August (C1) September (D1) 1st Occurence
Rows
(A2) Y (B2) N (C2) N (D2) July
(A3) N (B3) N (C3) Y (D3) September
(A4) N (B4) Y (C4) N (D4) August
=find("a",F3,3) will find where the letter 'a' occurs for the third time in cell F3 but how do you find the last place 'a' occurs in cell F3?
View 4 Replies View RelatedHow do I insert a row on top of the first time a value is found?
e.g.:
initial:
B
B
B
final:
"There are 3 Bs"
B
B
B
I have attached a sample file. What I need the formula to do is lookup the value in Column A on 'EE Data Sheet' (which has multiple occurances of the same value) and give me the corresponding value in Column E (ID #) only if the date in Column K (Service Dt) is greater than 7/21/2008. The return value needs to be on the '151_SD Sheet'.
In other words, I need the ID number from Column E on the 'EE Data Sheet' returned to me on Sheet 151_SD for each occurrance of 1806-151 (Column A on EE Data Sheet) only if the date in column K is greater than 7/21/2008. The areas highlighted in yellow show the data to be returned on the 151-SD sheet. I tried vlookup & match and hlookup & match, but I only got the first occurance of 1806-151.
I am wondering if there is any way that I would be able to see when the last time an instance occurred within my Pick 3 lottery spreadsheet.
I don't know that I am stating this correctly, but here goes.
Pairs are located in columns F:K. Dates are in column A.
If I wanted to determine the last time that the 17 pair occurred, what formula would I use to give me the actual dates that this pair occurred on .
I need the dates listed so that I will then able to count the actual days in between hits.
Or would it be simplier to just try and use the filter feature of excel??
How can I find the 2nd occurence of a value in a list and lookup a value in the same row?
My data looks similar to this:
Name.............Order No
Alan...............1234
Bob................4567
Steve.............7890
Alan...............6543
Steve.............0985
etc.
How can I lookup Alan's 2nd Order No.?
Count number of times numbers occur in long Excel data list. I get list of bar code entries representing how many times parking passes were used each month and need to count occurences by number. Ex. 890093= 23, 890097=123, 980403=0, etc
View 4 Replies View RelatedI have a spreadsheet that contains the number of faults in 12 hours. What I would like it to know the frequency of fault i.e 204 faults means a fault every 00:02:03
Is there a formula I can use.
I'm Tim, and I'm just an average Excel user who found his way here through Google. I did a little poking around and this looks like a great community. Maybe I can contribute on future visits.
I'm wracking my brains over this one but I can't seem to get anywhere. What is easy on a single worksheet becomes a conceptual nightmare when extended across multiple sheets........
I have an Excel 2007 workbook with ten worksheets, each is 5 columns by *about 50 rows. My column headings are the same on each sheet. All of the cells are formatted as Text.
Transpose rows at every occurence of a value in column ...
View 9 Replies View RelatedI am looking at a register of documents that are constantly being revised. What I need to do is mark up which ones need to be reviewedDocument RevisionViewedapples1apples2yapples3apples4apples5pears1pears2ypears3pears4oranges1oranges2oranges3
If a revision is marked as Y then previous revisions don;t need to be looked at and can be marked with n/a anything older doesn't need to be reviewed except for the latest one. In the above table if apples revision 2 has been viewed so rev 1, 3, and 4 are no longer relevant and only rev 5 needs to be reviewed. Pears rev 2 has been reviewed so on rev 4 needs to be reviewed. No Orange documents have been reviewed so only rev 3 needs to be reviewed.
The above is just an example my table consists of 8000 entries.
The ideal is I have a list which the use fills in, for sack of argument Goal 1, Goal 2, etc but I have a problem. This list which the user builds I want to appear in a combobox with is quite easy using the list function and naming the range.
The problem I have is that Goal 1 or 2 can be in this list more than once, if it is at all possible I want or would like the Combobox to only show Goal 1 once and not twice or how many times it occurs. I require the Combobox just to show all Goals once no matter how many time they occur.
I was wondering if you have similar values how you use formulae to return the first or the last value from a list?
I have attached an example and I was wondering if you guys could have a look at it?
I need to find to the occurrence/count of a cell value in a column/range.
Like say column A has following values
A12
A12
A12
A12
A13
A13
A14
A14 and so on ..
I need to find the no. of times or count A12 is there in the column A.
I have tried using .count function in the range A but this instead gives the number of cells in complete range A
I have a list of data which is not in any particular order....
View 9 Replies View RelatedI am trying to create a run balance sheet (see attached sheet). Column C has a list of job numbers, with column D showing the reqd quantities. Once the job is run, the qty is entered in column F and either a balance or the word complete returns in column H. My problem is, is that, when a job with a balance attached to it is re-run, that balance should be returned in Column D.
Ie 574361 has a total of 707 of which 320.4 was run, leaving a balance of 386.6.
When I type in 574361 again, I need column D (in the cell next to the job number)to automatically locate the last reported balance and return its value.
I have tried VLOOKUP and INDEX, but cannot get the thing to return.
how many accounts, and list the total on the bottom.
So, I have something like this:
10003722
10003722
10003722
10003722
10008981
10008981
10008981
10008981
10008981
10028163
10034527
I need the column next to it to show #1 next to 10003722 than jump to the next one and have 2 next to 10008981 and so on...
I want to count if cells in a specified range =yes then divide the range to get a total percentage of yes cells. I have a data validation list set up with the values: yes, no, n/a. I don't want the total percentage to change when using n/a.
View 9 Replies View RelatedI have one column with many numbers. Some have one dash and some have two.
Example:
123-123456-65
012-789546-1
98B12354-889
Is there a way that I can remove all characters after the last – (dash) in the number?
Example:
If number is 123-123456-65
Then 123-123456
If number is 98B12354-889
Then 98B12354
If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;
1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for
Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................
I have another challenging solve for a VBA macro. So here it goes, as I have become frustrated trying to make an array formula with no joy. On my sample worksheet provided below this is what I am trying to accomplish:
(Solution cells) B4:I4 looks to the (Combination cells) M5:R10 for a match
If a match is present then cell J4 gives a "win" ,
If no match then cell J4 gives a " lose ".
Next if a win is present in cell J4, then cell K4 looks for when the draw number that matches occurred on from cells A4:A10, then subtracts the two(e.g. solutions cells from combination cells) to give the actual " # of draws to a win ". If no win is present in cell J4, then the default is zero for cell K4
The formula would be copied down thru cells J4:K12
Please refer to sample worksheet attached so that you can understand more clearly of just what I'm trying to do.
I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.
I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.
I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.
If value I5 says Period_1 it will only look in rows 12:46.
If value I5 says Period_2 it will only look in rows 48:82 etc.
I believe I need to modify the xl look part of the code with if statements.
Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.
Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long
I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.
I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?
I need to search across 15 columns and return the lowest value in that row? How to do this?
View 8 Replies View RelatedI have a long list with names of banks. I also have three different numbers (between 1 and 10) for every bank. Eg Bank A 5 7 4, Bank B 5 9 3. Sometimes there are not three numbers for a bank but only one or two. There are never more than three numbers though. I want to have a forth column. This column shall give me a new number based on the other numbers. The sorting shall work like this. If a bank only has one number then that number shall be presented in the forth coulmn. If there are two numbers then the lowest one shall be used. If there are three numbers then the two highest numbers shall be chosen and if they are different the lowest one of the two highest shall be presented in the forth coumn. I do not know if you can do this using normal worksheet function of if a user defined function is necessary.
View 9 Replies View RelatedI am trying to create a formula that will show the lowest of three separate cells. I don't know how to do the Vb script,
This is what I need:
Cell H34 HOURLY RATE (=$920)
Cell I34 DAILY RATE (=$980)
Cell J34 MONTHLY RATE (=$1020)
Cell K34 displays the text "HOURLY" since it's the lowest of those 3 options
Private Sub cmdShowdata_Click()
Dim Tgt As Worksheet
Dim Source As Range
Dim wbSource As Workbook
Dim cel As Range
Dim rng As Range
Dim c As Range
Dim i As Long
Application. ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open("C:Documents and SettingsDesktopStaff Recoed 2")
Set Source = wbSource.Sheets(1).Columns(1)
With Tgt
.Activate
'clear old data
Range(.Cells(3, 2), .Cells(200, 5)).ClearContents
' Loop through names in column A
For Each cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then...................
The above vba command which is extract the data from the Other workbooks. It looks for the "Staff 001", "Staff 002"...these parameters to transfer the data to the worksheet. But, the "Staff 001" data must appear twice in each workbooks. If i use the above command, i only can extract the FIRST "Staff 001" average data. But SECOND "Staff 001" average data cannot extract. I know it may be use FindNext method to do this but i am not sure how to write it
i have a table with multiple codes and quantities along with other info in it. What i need is to take each MATERIAL CODE and its lowest quanity and transfer it to another cell. I have tried using DMIN but can not quite get it. It stopped working after the 6th one.
103057300 -800
103057300 -700
103057300 -250
101789050 50
101789050 -70
101789050 -90
So i want to to take the following quanities that are in this font and put them into a separate cell.
I have 4 TextBox on a UserForm. First three to enter value and the last one to return the lowest value entered in to the first Three TextBox. How to determine the lowest value and show it on last TextBox.
View 2 Replies View Related