# Create An Alphanumeric Sequence

Sep 14, 2009
I want to create a 4 character alphanumeric sequence. The sequence is to be like this: a1a1-z10z10. If it's a number, increment 1 until you reach 10, then move to the next column, if it's a letter, increment a until z, then move to the next column. Excel's autofill feature doesn't seem to work well for me due to the letters involved, so if someone has a macro handy that would be great.

View 11 Replies
ADVERTISEMENT
Mar 24, 2014

I'm looking to populate a series of sequential codes based on a code prefix.

Sheet 1: Master Code list

Column A = Lookup Value

Column B = Code Prefix

Sheet 2 - Results page

Column A - list of lookup values - sorted in value order - The number of occurances varies for each value

Column B - Results list

I want to populate Column B in the second sheet with a sequential code list for each different value in column A. So for the first value it would have the prefix then proceed numerically (Prefix0001, Prefix0002, Prefix0003...) then for the next value its prefix in sequence (2ndPrefix0001, 2ndPrefix0002, 2ndPrefix003...), etc

I'm wondering if there is a way to do this all in one step or else if there is a way way to number each occurance of the lookup value in the second page, in which case the code can be concatenated easily by a lookup of the code.

I suppose on the back of this there is another query - is there a way to concatenate a number where the format is 000# where the concatenated value will retain the leading zeros? I've been trying and it is stripping them out.

View 2 Replies
View Related
May 14, 2007

I would like to use an excel spreadsheet to generate a series of passwords. The password must include alphanumeric content, and be at least 10 characters long, and case sensitive.

For example: 14sH2dA456a

Thought about concatenting each variable, but do not know how to control the random variable creation in a spreadsheet?

I have looked at RAND but can't figure out how to use it to any avail?

View 9 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
Apr 19, 2013

How can I create a sequence with a formula starting in A1 that will give 1,1,2,2,3,3 and so on horizontally?

View 3 Replies
View Related
Jun 23, 2009

I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...

A1=MFR0001

Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)

This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).

View 3 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
May 12, 2006

I have in column " A" 500 rows with numbers in random sequences each

sequence has random number of rows , each sequence is seperated with a blank

cell (the result of a formula) some sequences have zerrows included.

I need to find the lowest number in each sequence and put that number in

column

" B" I also need to find the highest number and put that number in column "

C"

eg:- A B C

4 2 10

6

2

8

10

blank cell

7 7 50

9

11

13

50

18

21

30

15

blank cell

3 1 17

5

0

1

0

0

17

6

some sequences have two or more of the same numbers , in case of two ore

more of the same numbers only one is needed

View 11 Replies
View Related
Jun 24, 2009

I would like to create a column with letters from alphabet in a sequence. If I write A and in cell below I put B then highlight the two cells and drag down I get a repetition of A and B. How do I get the following alphabet letters ie. C,D, E etc.?

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

How can I get the Longest sequence of 3's. E.g.

CA

1

2

3

5

3

3

5

4

View 2 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
Mar 31, 2009

My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is:

=IF(MID(C378,LEN(C378)-1,1)="W",C378&" ,",C378)

And what that does is it would change data in a cell that looks like this

01-02-034-05-W2 TO 02-03-045-06-W2

To this:

01-02-034-05-W2 TO 02-03-045-06-W2 ,

The formula works great, thanks to the help of the posters!

However I just ran into an issue, what if I have a set of data in a cell that looks like this:

01-02-034-05-W2 TO 02-03-045-07-W2

02-02-034-05-W2 TO 02-03-045-07-W2

03-02-034-05-W2 TO 02-03-045-07-W2

And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied:

01-02-034-05-W2 TO 02-03-045-07-W2 ,

02-02-034-05-W2 TO 02-03-045-07-W2 ,

03-02-034-05-W2 TO 02-03-045-07-W2 ,

Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.

View 9 Replies
View Related
Jan 5, 2008

I'm trying to write some code to calculate the value of a Fibonacci Sequence, at a random point in the sequence.

For example if I have 5, 15, 30, 50, 75, 105... and my random value is 3 it would return 30.

Being new to VB the only way I've of thought of to do this is a massive if then statement.

View 4 Replies
View Related
Jan 15, 2013

I have a dynamic worksheet containing a variable number of rows that I would like to have automatically numbered when I run my VBA script. I used Dave Hawley's code as follows, with a modification to begin at cell A2, but it doesn't seem to work, as it only numbers cell A2 and not the rest.

VB:

With Range("A2")

.Value = 1

.AutoFill .Range("A2:A" & RowTotal), xlLinearTrend

.Range("A" & RowTotal + 1).Clear

End With

RowTotal is just a variable that holds the results of a simple last row finder. How to make this work?

View 4 Replies
View Related
Jul 25, 2014

I'm trying to automate a sequence.

I was gonna explain it but I couldn't make myself clear so I took a screenshot:

sequence.PNG

folder_type is what the user types, (for example, the letter "A"), and folder_code is a sequence that should auto-generate according to what was manually inserted in the folder_type field.

What formula could I use for this process?

View 6 Replies
View Related
Aug 23, 2007

I have a task to identify all the numbers in sequence in a worksheet, how do I do that?

Ex:

Column A

1

2

3

4

5

7

9

10

11

12

13

14

15

16

20

21

22

Desired output 1-5,7,9,10-16,20-22.

View 10 Replies
View Related
Sep 11, 2007

I am fighting with this for a longer time now and can't find a solution:

is it possible in Excel to generate a number sequence (to create a list for auto fill) representing numbers like this:

000001

000006

000002

000007

000003

000008

000004

000009

000005

000010

000011

000016

000012

000017

000013

000018

000014

000019

000015

000020

and so on till 001000

View 13 Replies
View Related
Nov 29, 2012

formula that will produce the number sequence as shown in the third column of the table. The sequence starts at 1 and if the colour changes then the number is 2 and if the colour changes again then the number is 3 and so on. The part I'm having difficulty with is that if the name in the first column changes then the sequence must reset to the number 1 and become 2,3 etc. as above when the colour changes.

Peter

Blue

1

Peter

Blue

1

[Code]....

View 2 Replies
View Related
Feb 22, 2013

I need to create a Conditional Formatting in a sequence (every ten 0 [zero])

View 4 Replies
View Related
Mar 12, 2014

I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually.

12

24

36

48

510

612

7

8

9

10

View 5 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
Mar 28, 2009

I want to change the sequence of find for the below code. I want to find the last entered row, instead of first entered. Now this code find first entered row.

View 2 Replies
View Related
Oct 21, 2005

I have cells O1 to O300 numbers in random sequences of no more than 10

I need to average the 5 highest numbers in each sequence eg:

O P

1 2 16 (result)

2 20

3 4

4 6

5 14

6 12

7 8

8 10

9 16

10 18

11 blank cell

12 15 9 (result)

13 3

14 6

15 12

16 9

17 blank cell

18 blank cell

19 4 23 (result)

20 16

21 20

22 40

23 8

24 11

25 28

26 blank cell

down to 300 rows, the sequence of numbers can be from 1 to 10 but never

more than 10

I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5})),"

") but that works only for 10 numbers and not for less

View 11 Replies
View Related
Mar 14, 2008

I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.

E.g. if a person is absent for 1 day the formula assigns a value of 1

if a person is off for 3 days in succession the formula assigns a value of 1

View 13 Replies
View Related
Dec 11, 2012

I have two sheets in excel, one contains a list of names and the second is a basic lookup sheet for mail merge purposes.

Basically I want to write a formula in column A (of sheet 2) [current formula is =LabelNames!A2] and in column B the formula is =LabelNames!A3.

I want to drag these formulas down so each one jumps by 2... Ie:

=LabelNames!A2

=LabelNames!A4

=LabelNames!A6

View 4 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
Jul 1, 2014

I want the numbers to increase based on the labels from a list, such as the first john chosen will get a 1 on the first cell on sheet 2, if john is chosen again the 2nd john will get a 2 on the next cell on sheet 2 and so on for the other names.

I tried to use IF(COUNTIF(D:D,D2)=1,LARGE(IF(D:D=D2,E:E)+1),1) but it didn't work out.

View 3 Replies
View Related
Dec 9, 2006

I need a macro, preferably a "sub" that will search a long text input and when it finds the first instance of the letters "GSH" in that order removing all previous text before it. The amount of text characters, letters, and order before the gsh will vary.

For example:

FGHYTIUGSHIYKIOLHY --> GSHIYKIOLHY

View 9 Replies
View Related