Determine If Column Of Values Contains Sequence Of Consecutive Matches
Oct 12, 2012
I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
For cells with column F="NEXT" and matching values in column A (i.e. Rows A2-A5 all match) and "Yes" in any of those same rows (i.e. E2-E5) for column E, column G should read "No". If no "YES"s are in those rows (i.e. E2-E5) then column G should read "Yes".
I can't figure out using VLOOKUP or INDEX and MATCH how to make this work. Would I need to get into VB to do this?
I am writing a sports schedule or fixture list. The home teams are in column B and the away teams are in column C.
I want to prevent any team having 3 consecutive home matches. Is there any way a formula can be used to alert me if there are 3 consecutive home matches.
I am trying to group the same values in columns together and i was thinking about having a number count. What's the function that might be able to display this (display whats in Column B in the example below)?
How do I count unique values in Column A only if the values in Column B match?
A B 1 a 2 a 1 a 4 b 5 b 1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple A2 - Banana A3 - Apple A4 - Banana
B1 - 3 B2 - 2 B3 - 1 B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
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.
I'm building a work schedule based on 12 hour days and I would like to separate a full weeks hours, 84 total, into the Regular hours and the Overtime hours that the employee will be paid. The way it is set up is I have 1 spread sheet that has the schedule for a full year (using "IN" as a working day and leaving the cell blank as a day off) and on a 2nd page I've set up with a COUNTIF formula to total the number of days worked (IN) during each pay period and then it multiplies the number of days by 12 to get the total hours.
Where I am having trouble is that the employees are paid Regular hours and OT hours in a single day and I don't know how to separate the total hours automatically into those two types of pay. For the first 5 days of their shift they are paid 8 hours of Regular time and 4 hours of OT time, and the last 2 days of the shift they are paid 12 hours of overtime. In a total of 7 days they will get 40 hours of Regular time and 44 hours of OT time.
To break this down into simple formulas, ie. (84 -40 = 44), won't work because if an employee works more than their 7 days the pattern they are paid restarts again with the 8 hours of Regular time and 4 hours of OT time and etc. (8 total days would be 48 hours of Regular time and 48 hours of OT time, max of 14 days per pay period).
Is there a formula out there that can break a number down in sequence (8-4-8-4-8-4-8-4-8-4-12-12) and total them in two different cells?
there may even be an excel formula answer to it rather than a macro. Coloumn A has a series of numbers, I want column B to show those numbers in what would be there Descending Sequence.
Thus Col A 100 97 14 87 32 108 21
So with this routine you would then see
COL ACOL B10029731478743251081216 The above I suspect is not too difficult, however if two (or more) numbers are the same I want the sequence to reflect that thus COL A COL B100297 314 797 332 5108 121 6 and no... the data - Col A - cannot be sorted, it must stay in that sequence.
If possible I would want this done automatically when any Col A value changes (any unused cells would be 0 by the way). Oh, there will in fact be 25 cells in column A for this.
I'm trying to write some VBA code to do the following (don't think its possible using formulas only). In column A i have a list of codes (A1:A32) in column B i have a value (between 1 and 32 in this case, but feasibly could be any integer) I am trying to achive an output in column C which would take the code from col A and then "count up to" the value in col B;- for example "AAA" in col A, and "4" in column B would return AAA1, AAA2, AAA3, AAA4 in column C. For the next code say BBB and corresponding value, would start its sequence in the next empty cell below AAA4 in col C.
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?
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:
I am trying to fill a column with a number series starting with 1 whenever a value changes in the adjacent column. So for instance I am trying to do something like this :
I have a set of data in cells sequencially in every 3rd column. I would like to create a new rows where the data I have in columns are placed into rows in accordance with its catogory.
So all EUR are in one row sequencially over the colums and the same for USD & GBP. This way I can create a graph.
I am stuck on what should be a simple formula. I have a spreadsheet that is sorted by "target" number in column A, basically 1,2,3,4. I am trying to create a formula that will increase the number in column B by one based on column A. So when the value in column A changes column B will reset to 1. Also I need this sequence number to be preceded by zeros up to 0999. So 1 would be 0001 and 895 would be 0895.
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".
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.
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.
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.
So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.
Is there a formula I can use in column N for this without doing 12 "if" functions? I think this needs to be an array formula but the ones I've tried I can't get to work.
