# Grouping Same Values By A Number Sequence For A Column

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
ADVERTISEMENT
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
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 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
View Related
Aug 4, 2009

I have code that adds the content of a userform directly onto the next available row starting at column B, using:

View 3 Replies
View Related
Nov 10, 2009

I have Excel 2003. I am trying to find the next number in a sequence of numbers. The number range is 1-59, and the sequence is 89 numbers that go like:

1

5

8

3

7

10

6

2

5

3

8

11

41,...(to the 89th number)

View 14 Replies
View Related
Apr 16, 2012

Need to find and then sum the sequence on a certain number. Using the number 1 in the example.

Example:

0

2

1

2

1

1

1

4

3

2

Answers: 1,3

View 9 Replies
View Related
Jun 6, 2014

I have a problem to name correctly my new worksheet in order to obtain a sequence.

So my first worksheet' name is 3114

My macro is : [Code] ..........

And I don't know what I have to put after Name = ... to obtain the sequence, what means that the next worksheet is 3115, the next one 3116 etc.

I tried with Worksheet.Count but it puts the number of sheet starting from 1 ...

View 14 Replies
View Related
Nov 9, 2013

About sequence number with 4 condition...

code number (as text)

desired result

date

condition

cost ($)

'1003

1

31/12/2000

1

5

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

if "code number", "date","condition" and "cost" the same line of rows so "desired result" = 1 like code "1003" or so code "1005" = 5, otherwise if there is a no match/same condition "desired result" start from = 2,3,4,etc.......

View 2 Replies
View Related
Feb 9, 2007

"A1" has a number containing "1000"

I want column "B2" to choose the next number in sequence,(1001) so long as "A2" contains these letters "EHT". if it does not contain "EHT" i dont want excel to insert the number "1001"

But if cell A3 contains, "EHT" I want the next number in sequence to be instered automatically... (1001, 1002, 1003")

View 9 Replies
View Related
Jul 25, 2006

I'm trying to set up an auto sequence number(col A) based on a date entry (col B) as in the example below. The sequence number should reset to 0001 each time the date in Col B changes.

Col A Col B

072306-0001 07/23/06

072306-0002 07/23/06

072306-0003 07/23/06

072406-0001 07/24/06

View 5 Replies
View Related
Dec 2, 2013

I have a list of dates in column A as shown below & I want to get a count formula in column B for the # of dates in sequence. When the date sequence breaks a new count should be done at the end of the next sequence. The formula should be 1 I can put in cell B1 and copy down so that there are blanks & a count is only done at the end of the date sequence like in cell B4 or B7 below.

Col A Col B

Date Count

1 12-Oct

2 13-Oct

3 14-Oct

4 15-Oct4

5 17-Oct

6 18-Oct

7 19-Oct3

[Code]....

View 4 Replies
View Related
Nov 10, 2008

If I want to create a column of numbers, say 1 2 3 4 5, I can simply add +1 to the previous cell and then use "fill down" to generate my number sequence. How would one generate a column of numbers that repeat once? e.g.: 1 1 2 2 3 3 4 4 5 5, etc

View 3 Replies
View Related
Feb 21, 2013

I need to create a grouped sequence that changes every 10 rows.

So in Column A cell A2 the number would start at 01349 and every 10 cells it would decrease by 1 and go on down to 0

so it would look something like this:

01349

01349

01349

01349

01349

01349

01349

01349

01349

01349

01348

01348 ...

View 8 Replies
View Related
Dec 11, 2005

I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,

with numbers in them, (as an result of formulas)

I need to count the number of rows in each sequence that have numbers

of 35 or less , but only up to the first number of 35 or higher. eg:-

AU result

row 39 32 3

40 14

41 19

42 107

43 11

44 21

45 #value!

46 40

47 45

48 16

49 15

50 #value!

51 29 2

52 11

53 146

54 14

55 17

56 11

57 #value!

and so on down to 500 rows

View 10 Replies
View Related
Nov 7, 2006

Add Worksheet Named As Cell. Number Sequence

how would I set it up so that it copies the page not just creating a blank page.

View 5 Replies
View Related
May 8, 2008

I have worksheet with a column of client codes. These are made up of:

(a) a "base code", being a minimum four to five digit, i.e.

0000

0001

0002, etc

(b) plus a decimal point and a "sub code" (i.e.

.00

.01

.02

.03 through to .99)

The worksheet also contains the client name (i.e.

1031.00 John Smith

1031.01 Mary Smith

1032.00 Jack Brown

1033.00 My Company etc

As the supplier codes list has been manually done, there are numerous "gaps" in base code numbers as well as the subcodes.

I am looking to have a userform that a user can:

find the next unused base code and apply the 00 subcode (with client name).

View 3 Replies
View Related
Aug 14, 2007

How do I express the following as a formula for range f2:h2:

case 1

if any number is equal to 0 or 5, then 1

if any number is equal to 1 or 6, then 2

if any number is equal to 2 or 7, then 3

if any number is equal to 3 or 8, then 4

if any number is equal to 4 or 9, then 5

example:

f2:h2=3,5,9

result

415

f2:h2=0,2,7

result

133

View 9 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
Feb 11, 2012

Is there any way to do this on Excel automatically ?

From this:

Code:

Column C

row1 A

row2

row3 A

row4

row5

row6

row7 A

row8

row9 A

To this :

Code:

Column C

row1 1

row2

row3 2

row4

row5

row6

row7 3

row8

row9 4

Note1: number of lines between each value may change.

View 2 Replies
View Related
Jul 16, 2013

I have the following data: (The Dept can occur multiple times in Col B.)

Col A

Col B

Row 1

[Code]....

What I want to do I want to insert a formula in each row of Col A that returns a number that indicates if the Dept in Col B is the first occurrence of the Dept, the second occurrence of the Dept, etc.

View 3 Replies
View Related
Aug 1, 2014

I am trying to create a number counter which runs up to a value which is determined by a COUNTIF Statement in the spreadsheet. I can get the number counter to run to the given value. However, due do formatting reasons I do not want the counter to start in cell A1 but A3 instead.

Essentially my code looks like this:

...

ActiveSheet.Cells(3, 1).Select

Dim i As Integer

For i = 1 To Range("A1")

Cells(i, 1) = i

Next i

...

View 3 Replies
View Related
Sep 4, 2006

I'm using a simple auto numbering command.

eg: cell A1: 1

cell A2: =(A1+1)

then shift, copy/paste cell A2 until A600,

This creates a sequential number column from 1-600. My problem lies when a new row is inserted, which is left blank. How can i maintain this auto function upon a new Row insertion?

View 6 Replies
View Related
Oct 28, 2006

I'm trying to set up a worksheet so that when i enter a number in say cell J5 it will automatically create a new worksheet with the next number in order so if cell J5 was 1500 it would create a worksheet named 1501.

View 8 Replies
View Related
Mar 17, 2008

2 sites are better than one, eh, cos i think this is very tricky. If anyone suggests a solution I'll ensure both sides are kept up to date to ensure nobody wastes their time.

[url]

View 10 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
Feb 21, 2014

I have a sheet with hundreds of rows of customer information; including a 7 digit customer number. I need a macro to delete all the rows where the customer number sequence start with 1 thru 8 (that would be the first digit of the customer numbers).

View 9 Replies
View Related
Apr 9, 2014

i am looking some formula to make sequence number with criteria;

1) if in cell B2 contains only single/one data ----the result is mark "-"

2) if in cell B2 contains several data (not single)----the result is auto numbering with adding mark "." (dot) and start from 1.,2.,3.,4,etc........

for the detail,

condition 1 (multiple data in col.B)

number

name

1.

john

2.

mike

3.

sisca

etc...

condition 2 (single data in col.B)

number

name

View 2 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
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