Finding 2 Out Of 3 Consecutive Equal Cell Values?
Sep 10, 2012
Say I have Column A populated with Employee Names, and Column B populated with "Pass" or "Fail" (which indicates a passed or failed customer service call monitoring).
For each employee, I want to be able to look at all of their overall scores (usually between 3 and 10 evaluations per month) and easily highlight people who have failed 2 out of 3 consecutive evaluations.
I want to integrate this feature in to a weekly quality report.
View 1 Replies
ADVERTISEMENT
May 11, 2009
I have 10 columns (A-J). Column B is the Date & Column C is the Time, I have the data sorted 1st by date then by time. The 5th Column, E, is Size. The 10th column, J, is "Position."
This 10th column, J, is in binary format. A 1 equals a Position & a 0 equals no Position.
1) I need to find the Max Sum of the Size in which the Max consecutive set of 1s has occurred.
2) I need to also do this on a day by day basis, using the Date in Column B, without having to manually go through & modify all the formulas to reference the date.
I have a Sumif formula for the 2nd but it won't copy down to update the formula for each unique date. I have to manually change the date values & I have ~957 unique dates to do; so it would be a rather painstaking process if only done manually. Moreover, it doesn't count the Max Sum of all consecutive 1s in Column J...
The length of the non unique columns (all columns w/o unselecting the duplicate entries) is 19,068.
If you can be of help, I'd be very thankful...my brain is overloaded w/googling & using trial & error.
View 6 Replies
View Related
Dec 27, 2013
I'm looking at test results and would like to know how many times in a row correct answers were given before getting one incorrect. The way I have this currently set up, is if a student has the answer correct it returns a "TRUE" value, and a "FALSE" value for an incorrect answer. A typical test may have 50 questions, so I would have either "TRUE" or "FALSE" values in rows A1 through A50.
So I've been trying to figure out a formula that counts the number of times "TRUE" rows repeat 'consecutively' in a given column, in this scenario column A. The formula would need to check the current row and if "TRUE" check the row immediately after, if that row is also "TRUE" then check the row immediately after that, until it runs into a "FALSE" string, then display that total count of consecutive "TRUES" in another cell. I understand there may be duplicate results but I'm only really interested in the longest value, so duplicates can be ignored.
If it makes the formula easier, each possible result can be assigned to it's own cell. Let's assume the test has 50 questions. So if a student got all 50 questions correct the result count would be 50 in cell B50, or if they got 25 correct before the first incorrect answer, cell B25 would say 25, etc. I'm guessing I could then easily query this column for the largest value to come up with what I need at a glance.
View 5 Replies
View Related
Jan 13, 2014
Need a formula for counting how many consecutive cells where the value is gretaer than or equal to +1
For example 1,
J18 = 7
K18 = 9
L18 = 3
M18 = 2
N18 = 8
O18 = 1
P18 = 8
Q18 = -17
In the above example the result would be 7
Example 2:
J12 = -22
K12 = 9
L12 = 4
In the above example the result would be 0 since the 1st value was -22
View 3 Replies
View Related
Jul 25, 2013
I was wondering if it would be possible to filter by two consecutive cell values or colors. For example if I have in "A"
X1 (red fill)
X2 (blue fill)
X1 (red fill)
X3 (xx fill)
X3 (xx fill)
X1 (red fill)
X2 (blue fill)
X4 (aa fill)
I want to be able to filter so that the consecutive cells for x1 and x2 show up or red followed by blue.
So when I filter it will only show rows 1 & 2 and 6 & 7.
X1
X2
and there would be many of these values in the spreadsheet.
View 10 Replies
View Related
Jul 11, 2013
I have got the following issue. I have got a large list of values in a column. I need to detect the the ones which are in non-consecutive order and display the difference in single numbers. For example:
1 fine
2 fine
3 fine
7 - 4,5,6
10 - 8,9
In other words I need to find the missing values and get them displayed.
View 9 Replies
View Related
Jan 18, 2013
I've attached an example of the data I am trying to solve this problem for. Basically I am looking for strings of 26 blanks or more in a row, but I want to determine the ending location of the last string of 26+ blanks. I've been able to determine the location of the maximum string of blanks, but that max group of blanks isn't always the last set over 26.
Any link to find the location of last string of blanks of 26 or more? The example I attached is a good example since there are two strings of 26+ blanks with the last one being shorter. I've also include the answer I'm looking at the end of the data in the example.
Example.xlsx‎
View 2 Replies
View Related
Jun 15, 2009
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like
Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
View 3 Replies
View Related
Jan 14, 2014
I have a workbook with 2 sheets - [Names] has with a list of Staff Ids column B, [Access] has a list of their system acces - with their staff ids in column A and access in F (There could be more than 1 type of access),
I am looking to create a code that :
For each ID in [Names] B lookup in [Access] A
if found create a string with value in F ( so I have a list of all access )
And paste into F in [Names]
I have tried all sorts of things and just can't get it working.
View 5 Replies
View Related
Feb 11, 2013
I have a column with text + string (e.g., T1000, T1001, etc.)
I have another column that's the same, but sometimes this column has new codes.
Right now I have to "eye" it side by side to see if there are any new codes, but I'm sure there's a better way to do it. I'm thinking of asking Excel to compare the columns and if they aren't equal, then it would be necessary for me to eye it. If they are equal, then I dont have to bother because there are no new codes.
View 4 Replies
View Related
Jul 5, 2007
I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.
View 2 Replies
View Related
Jul 13, 2006
I have a worksheet where column A is a list of numbers and Row 1 is a different list of numbers
Is there a way to activate a cell given the column A value and Row 1 value? (so I can then insert a value)
View 9 Replies
View Related
Jul 3, 2014
I am trying to create a cell that show the value of all specified rows with specific name.
For example:
Apples $2 $5 $10
Oranges $5 $10 $50
Apples $5 $ 6 $ 60
(each of these will be in their own cells, 3 rows and 4 columns)
How do I create a cell that will sum the total value of all the Apples by using Apples as the variable?
View 1 Replies
View Related
Dec 20, 2012
Code:
Sub DeleteCells()
Dim ws As Worksheet
Dim cell As Range, search_cell As Range
Dim i As Long
[Code]...
A few issues with the above code: I have manually checked that there is a sheet with a value in M12 that is found in the H column range, but it doesn't change the value to zero. (this was confirmed with an =IF(M12 = H101,1,0) returning a 1 value.
Secondly, it takes a long time to process not a lot of data. (about 2 minutes to get through 3 sheets of 180 actual rows of data, (rest blank). Is there any way I can speed it up? I will eventually be cranking it up to 30 sheets of data and up to 360 rows. (The 500 was thrown in earlier to make sure it covered everything).
finally, it also lines every cell value in I(end of data):I501with a zero, when there are blanks in other cells, can i get it to not put a zero in,
View 5 Replies
View Related
May 19, 2014
I am trying to write a macro that looks for the first empty cell in column G, Then once that cell if found, assigns that row to be the row that other data is copied and pasted to. My code currently just finds the first empty cell in each column and copies and pastes the data and am not sure how to get it to what i need it to do.
Code:
Sub seconddatatransfer()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks("filestransfer.xlsm") 'Edit file extension
Set wb2 = Workbooks("KBCG.xlsm") 'Edit file extension
Set sh1 = wb1.ActiveSheet
Set sh2 = wb2.Sheets("Tracking Sheet")
sh2.Cells(Rows.Count, 2).End(xlUp)(2).Value = sh1.Range("B6").Value 'left
[code]....
View 4 Replies
View Related
Apr 17, 2014
How to correct my formula because it does not work?
Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")
I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.
View 5 Replies
View Related
Nov 4, 2005
I have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.
I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.
=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
View 9 Replies
View Related
Apr 4, 2013
I have a list of data:
2
140
1
660
10
140
0.92
660
2
130
0.18
660
4
510
0.44
820
4
510
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
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
Feb 1, 2007
i have an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.
View 13 Replies
View Related
Aug 21, 2008
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
View 9 Replies
View Related
Dec 28, 2009
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
View 8 Replies
View Related
Feb 4, 2010
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?
View 11 Replies
View Related
Oct 25, 2007
Basically I want to calculate each team's home winning/drawing/losing streak. Here is a sample of the table (I've simplified it a bit and added fictional results):
(See Attached)
So, I want a formula that can tell me a given team's (e.g. Arsenal's) latest result, and how many previous consecutive results of that kind there have been. For example, for Arsenal, the result would be something like "W2" (i.e. Won the last two games). At the moment, the best I can do is to get the TOTAL NUMBER of a given team's latest result using this formula:
=LOOKUP("ARS",B:B,C:C)&SUMPRODUCT(--(B1:B13="ARS")*(C1:C13=LOOKUP("ARS",B:B,C:C)))
(Which also returns "W2").
View 4 Replies
View Related
Sep 22, 2006
I have a range of binary numbers and would like to count the number of times the value of 1 appears BUT there must be at least 4 consecutive 1's.
Here's an example:
1
1
1
1
1
1
0
1
0
1
1
1
The final count should be 6.
View 6 Replies
View Related
Dec 11, 2013
I was trying to see the values only if the same value arrived more than three times in the given range.
Ex:
ABCDEFGHIJ
1AAAABAA
2
in the given range A1:J1, I would like to count 'A' only if it repeats more than or equal to 3 times. For row 1 I should get count as 4.
View 1 Replies
View Related
Jul 18, 2014
I have been trying to adapt a couple of the posts I've seen on this topic but they seem to refer to counting in rows and I'm having trouble translating that to columns.
I have a simple situation. I have 1s or 0s in columns from left to right. I only want to count the first occurrence of consecutive "1"s and only if the series starts with "1" in the left most column. (I am trying to calculate consecutive years of giving and the columns are FY14, FY13, FY12, etc. Only people who gave in FY14 would count.)
Example:
Row 2: 11101010
Row 3: 11001011
Row 4: 00100110
I would like Row 2 to count "3", Row 3 to count "2" and Row 4 to count "0".
View 2 Replies
View Related
Apr 12, 2013
I have a user form with 125 checkboxes and if checked would like them to display one message box containing a specific message for that the checkbox slection, but all messages in one message box.
I have one command button that will list each message box individually, using code like this -
If CheckBox12 = True And CheckBox5 = True Then MsgBox "You would need to see your manager"
Each message box individually is time consuming and it would be better if they all displayed in one box. Of the 125 check box options, 7 or 8 will only be selected at any given time.
View 4 Replies
View Related
Dec 17, 2013
The formula in column B counts the number of values that are
View 9 Replies
View Related
Jan 26, 2014
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #
Lap Time
Threshold
2 or more laps
0
118.2
85
118.2
[Code] ........
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above,
I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
View 1 Replies
View Related
