# 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.

View 9 Replies
ADVERTISEMENT
Jul 3, 2014

I have a table with this sample data:

A Report Number

B Funding Code

C Approver Code

[Code]....

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?

View 1 Replies
View Related
Jul 18, 2007

Have this formula which works fine for finding the largest sequence in a list. (c/o Domenic from [url]

=MAX(FREQUENCY(IF('Overs-Unders'!B3:B1827"",IF(ISNUMBER(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))),IF(('Overs-Unders'!B3:B1827="")+ISNA(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))))

Now i need to:

(a) from the cells B5:CC5 that this formula runs through find the highest figure and return the name in Row 1 of that column.

(b) adjust above formula to get something now that ignores any run that contains 7 or more consecutive "n/a"s

(c) get a formula that counts the latest run. eg. from the bottom up (at the moment data only goes down to row 200)

View 9 Replies
View Related
Oct 29, 2009

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.

View 9 Replies
View Related
Mar 13, 2013

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)?

Values

5000 1

5000 1

6000 2

7000 3

7000 3

8000 4

8000 4

8000 4

9000 8

10000 9

10000 9

View 1 Replies
View Related
Mar 7, 2013

I have 2 columns,

column A has a series of 8 digit numbers (some will match some will not) sorting in A-z order from lowest to highest value.

Column B is blank at the moment.

What I want

In column B i need to add numbers starting at 1 and moving down in sequence.

If more than one rows have the same number in column A they get the same numbe rin Column B

It's really a pain or have to hand type in the numbers and I can't figure out a easy way to do this.

Attached example.

Tab 1 = Origanl Data

Tab 2 = results i'm lookin for.

View 2 Replies
View Related
Oct 18, 2011

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.

View 5 Replies
View Related
Apr 10, 2014

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.

View 1 Replies
View Related
May 3, 2008

I would like to determine the values of cells in column B by the values in column A. I can do that for one cell but not for two or more.

Here is what works:

View 11 Replies
View Related
Jun 29, 2012

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.

View 9 Replies
View Related
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 28, 2011

I need to put the order in the second column of the item that is in the first column.

In the last line item Computer is recorded for the third time.

MATERIALSSEQUENCEComputer1Computer2Mouse1Mouse2Computer3

View 2 Replies
View Related
Jul 4, 2014

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?

View 9 Replies
View Related
Mar 20, 2008

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.

View 3 Replies
View Related
Oct 29, 2009

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.

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
Apr 28, 2014

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 :

87 1

87 2

87 3

88 1

88 2

88 3

88 4

89 1

89 2

90 1

91 2

92 1

92 2

92 3

92 4

92 5

92 6

View 6 Replies
View Related
Jun 6, 2014

I was wondering whether it was possible to search for a sequence of numbers.

For example, if this is down a column:

A1: 5

A2: 4

A3: 5

A4: 6

A5: 9

Is it possible to have a find function to search for the sequence of numbers "4 + 5" and consequently highlight cells A2 and A3?

View 5 Replies
View Related
Apr 22, 2012

Creating a sequence in every column? It should look like this:

Original matrix (Top left cell is "A1"):

40 50 60

30 10 20

70 20 0

And I want to replace it with:

2 3 3

1 1 2

3 2 1

I need to create an ascending sequence of numbers in every column. First column contains: 40, 30, 70. That's why its ascending sequence is: 2, 1, 3.

Btw, I got N rows and M columns.

View 8 Replies
View Related
Aug 11, 2012

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.

Jan 12EUR

USD

GBP

EUR

USD

[Code].....

View 8 Replies
View Related
Sep 18, 2006

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.

View 2 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
Jan 26, 2010

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.

View 6 Replies
View Related