# Excel 2010 :: Count Consecutive Numbers Over Multiple Columns / Rows

May 27, 2014
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:

A B C D E

0 1 2 3 4

5 6 7 8 9

0 2 4 6 8

1 3 5 7 9

1 2 4 5 8

3 4 5 6 9

9 8 1 2 3

7 6 1 4 0

0 8 2 1 9

1 0 5 3 2

I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:

Number 1 appears:

1 consecutive time = 1 (appears in row 1)

2 consecutive times = 1 (appears in rows 4 & 5)

3 consecutive times = 0

4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)

Number 2 appears:

1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)

2 consecutive times = 1 (appears in rows 9 & 10)

3 consecutive times = 0

4 consecutive times = 0

Number 5 appears:

1 consecutive time = 2 (appears in row 2 & row 10)

2 consecutive times = 0

3 consecutive times = 1 (appears in rows 4, 5 & 6)

4 consecutive times = 0

and so on....

View 9 Replies
ADVERTISEMENT
Aug 24, 2012

I am trying to convert

Excel 2010

A

B

C

D

[Code].....

View 9 Replies
View Related
Jul 6, 2014

I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:

A1B1C1D1E1F1G1H1I1J1

12345678910

12345678910

12345678910

12345678910

12345678910

If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result

A1B1

12

12

12

12

12

34

34

34

34

34

56

56

56

56

56

.. ..

.. ..

It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.

View 6 Replies
View Related
Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies
View Related
Jun 24, 2007

I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like:

1

1

1

0

1

0

0

0

1

0

0

1

I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.

View 9 Replies
View Related
Jun 24, 2007

I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like;

1

1

1

0

1

0

0

0

1

0

0

1

I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.

View 5 Replies
View Related
Aug 13, 2008

I am preparing a attendance sheet. I am using 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for three consecutive days and if there is three consecutive 0 then the formula should return the value 0 ( the student gets 0 if he is absent for 3 consecutive days ) otherwise it should add all the 1s in the row. i.e

1 1 1 0 0 1 1 0 = 5

10 0 0 1 1 0 0 = 0

View 5 Replies
View Related
Sep 18, 2013

I would like to combine 2 columns, containing numbers on it, is it possible?

I have found the other option how to do it

=A2&B2 but then this doesn't work

I have

COL A COL B

12345 0000

The result I want would be= 000012345

View 2 Replies
View Related
Jul 31, 2012

I am currently using Excel 2010. How to Count Singles, Doubles, Triples and Quadruples in a group of number.

Frankie

0

1

2

9

Count how many single numbers (numbers all different). Example: 0129

7

8

8

8

4

8

3

6

[Code] .......

Count how many double numbers (two numbers the same in a draw). Example: 8830

9

2

6

2

8

3

5

4

[Code] ......

Count how many triple numbers (three numbers the same in a draw). Example: 7888

8

6

1

2

9

5

9

6

[Code] ......

Count how many quadruple numbers (all numbers the same in a draw). Example: 2222

6

4

5

1

5

1

0

2

[Code] .........

View 9 Replies
View Related
Feb 9, 2014

How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .

Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.

Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.

I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.

298

2/9/2014 12:28:02 PM

37

77

[Code]....

View 6 Replies
View Related
Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies
View Related
Oct 11, 2012

What I am trying to is to count the number of times a certain number or character appears (either on its own or in a batch of consecutive cells containing that number/character) in a column.An example might clarify things (for reasons of brevity I will write the columns in rows):

If a column looks like (each 1-digit numbers / characters being a consecutive cell) 0 0 X X 0 0 X and I am counting for X, then I should get 2. If my column is X X 0 X X 0 X 0 0, then I should get 3. If my column is 0 X X 0 0 X 0 then I should get 2. If my column is X X 0 X X 0 X then I should get 3. Is there a formula to perform that calculation?

View 1 Replies
View Related
May 2, 2007

Let's say I have a column A with the following values.

30

40

30

60

-20

-10

-50

-60

-70

120

320

20

-40

-30

40

How can I have 2 cells display:-

i)highest streak of positive numbers = 4

ii)highest streak of negative numbers = 5?

Also, how can I have another 2 cells display:-

iii)the sum of the highest streak of positive numbers = 160

iv)the sum of the highest streak of negative numbers = -210

View 6 Replies
View Related
Oct 31, 2013

I need an equation that will return a 1 in column F if data is available (cell is populated) in 2013 or last two years (2012 & 2013) or last three years (2011 & 2012 & 2013) or last four years or last five years. A zero counts as data.

Data is set up as:

Years in columns

2009

2010

2011

2012

2013

Count

product 1

34

32

33

31

35

1

[Code] ......

View 9 Replies
View Related
Feb 26, 2014

I am trying to separate two rows to two columns. How can I do this for +200 data. For example,

1

Apple

2

Ball

3

Candy

....

I need this to be:

1 Apple

2 Ball

3 Candy

and so on.

show me how I can do this for Excel 2010.

View 3 Replies
View Related
Sep 25, 2012

How do I freeze the top 6 rows and the first 3 columns in excel 2010 work-sheet so when scrolling they remain static.

View 2 Replies
View Related
Dec 8, 2012

I have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?

Excel 2010

View 9 Replies
View Related
Jun 17, 2014

I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.

BEFORE MACRO

ID

AREA

TYPE

CLASS

QTY

IDS

1

COAL

TYPE9917312

CLASS881345

2

FILER756911**/**FILER123188 ^** FILER877119*118

[Code] ........

AFTER MACRO

ID

AREA

TYPE

CLASS

QTY

IDS

1

COAL

TYPE9917312

CLASS881345

2

FILER756911

[Code] ......

What the MACRO would look like? This is for Excel 2010.

View 3 Replies
View Related
Nov 18, 2009

create a formula that counts consecutive rows that have wins and losses.

A B

1 Game 1 Win

2 Game 2 Win

3 Game 3 Win

4 Game 4 Lose

5 Game 5 Lose

6 Game 6 Win

7 Game 7 Lose

So Consecutive wins = 3

Consecutive losses = 2

View 9 Replies
View Related
Nov 20, 2013

For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.

View 1 Replies
View Related
Jun 16, 2012

I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-

Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)

Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.

Cell B2 onwards, down and to right, all spent amounts by month

What I need is a formula to get year to date number, which will change to Executives requirement.

Cell-ABCDE

1Jan2011Feb2011Mar2011Apr2011

24100100100100100

34101200200200200

44102300300300300

Year to dateMar 2011( Months will be changed)

Account 4101( Accounts will be changed)

Amount should be 600 What Formula ?

My excel version is 2010.

View 7 Replies
View Related
Jul 18, 2012

I have a worksheet with

Col A being Name,

B being primary skill,

C secondary skill and

D tertiary skill.

(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.

Name

Primary

Secondary

Tertiary

Person 1

x

[Code] .........

View 3 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
May 19, 2014

I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.

Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.

So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.

I am trying to use

Code:

Randomize(Seed)

where Seed is one of the 5-digit numbers.

The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.

I am using Excel 2007 and Excel 2010.

View 1 Replies
View Related
Jun 15, 2010

how to display Sparkline from non-consecutive cells.

I want to display it from two apart ranges: A1:C1 and F1: J1

If it can't be done within the worksheet - is there a VBA code to workaround - such as filling-in an Array from the non-consecutive and using that Array as the Sparkline source ?

View 4 Replies
View Related
Feb 10, 2014

We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)

Right now here's how the data is received:

a

a

a

b

b

c

c

c

c

d

d

d

e

e

e

e

Using two vba scripts, we first separate the data with row spaces between the unique data as follows:

a

a

a

b

b

c

c

c

c

Then with another script, we transpose the data as follows:

a a a

b b

c c c c

d d d

e e e e

When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...

a a a

b b

c c c c

d d d

e e e e e

where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.

The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.

Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?

My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.

I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)

The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.

One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.

We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.

For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.

One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.

View 5 Replies
View Related
Dec 13, 2011

I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.

Here is what I currently have:

Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4

11111......2008........FL

11111......2000........FL

12121......1999........GA

33222......2000........AL

33222......2011........AL

33222......2001........MS

Here is what I need it to look like:

Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4

11111......2008.......FL.......2000........FL

12121......1999.......GA

33222......2000.......AL.......2011........AL......2001.......MS

There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).

I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??

View 3 Replies
View Related
Mar 27, 2012

I need something that will take data from columns in one Spreadsheet and put in difference cells in a row. I know this could be done with recording a macro but the number of column will never be constant.

Below I attached examples of the Spreadsheet

Financials SpreadSheet

Need to have the data in column B to F put their respective cells in row in the

Master Spreadsheet

So we would have 5 rows.

Excel 2010ABCDEFGH5Job Number17542000250030003500Total6Date Booked01-Mar-1215-Mar-1215-Mar-1223-Mar-1223-Mar-127SalespersonJames ThorntonHoward StandenHoward StandenIan BullimoreSylvia Walton8AdvertiserNestleTalkTalkLloyds BankSkodaHonda9ProductNature ValleyBroadbandMortgageApril

[Code]....

View 6 Replies
View Related
Jun 14, 2013

I have 10 columns and five rows worth of data in each of those cells

What I really want out of that spreadsheet is 50 rows and only one column

I want to transpose or invert the data so I can apply some formulas in an easier manner

Is there a command to do that.....not too familar with that transpose feature

View 4 Replies
View Related
Nov 26, 2013

import it into to Stata to do statistical analysis. I always receive spreadsheets like this:

country

1991

1992

1993

1994

1995

1996

1997

1998

1999

2000

[code].....

and I have to manually reorganize it like this to import into Stata:

country

year

value

Benin

1991

20

Benin

1992

254

[code].....

Is there way I can quickly design a macro to do this? The problem is that I generally have a list of about 60 countries, and years from 1991-2011. So, it's really time consuming copying the column of data corresponding to the year, pasting below, repasting the list of countries and the years...then again..then again...then again...I'm using Excel 2010.

View 7 Replies
View Related