# Create Sequence With Formula Starting In A1?

Apr 19, 2013How 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 RepliesHow 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 RepliesADVERTISEMENT

I'm using the VBA code below for a piece of code.

[Code] .....

However, I want to use this same function in another place, without the limit set on the cells that will be cleared.

What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.

Something like:

[Code] .....

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 View RelatedCreating 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.

i wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....

View 9 Replies View RelatedI have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.

View 9 Replies View Related 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]....

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

I need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.

View 4 Replies View RelatedI 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

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)

Can weeknum work with the week starting on saturday instead of sunday or monday?

if not, what are my options?

I have a string of letters in cell column L20 through L3019.

The letters are A, B, C, and R . No other letters.

I wanted to find out if it is possible to remove any duplicates when starting in cell L20 going downward.

For example:

L20 = B

L21 = A

L22 = A

L23 = B

L24 = C

L25 = C

L26 = C

L27 = C

L28 = B

L29 = R

L30 = A

L31 = C

L32 = C

L33 = C

L34 = R

L35 = R

L36 = B

L37 = B

In the range L20 through L37 all the letters have appeared so my goal is to create a formula where the outcome is BACR. Starting with the first letter B in cell L20

Cell range L20 through L37. Looking for the next letter which would be either A,C, or R. Since we already used the letter B. In this case it's the letter A

ABCR

Cell range L20 through L37. Looking for the next letter which is the C or R. In this case it's the letter C

CBRA

Finally range L20 through L37. Looking for the letter R which is in cell L29.

RACB

I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).

I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.

A B

1 150

2 200

3 250 50

4 120

5 260

6 157

I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.

View 5 Replies View RelatedI'm working on a stacked bar graph based on the below info...

Min

Mid

Max

N1

$21.00

$30.00

$38.00

[code]....

I'm trying to adjust the starting point so that it does not start at 0.

For example...

N1 - $21.00 - $38.00

N2 - $26.00 - $45.00

N3 - $30.00 - $53.00

is there a way to do this?

1) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?

2) How do I get the options from the userform to the worksheet?

3) How do I put an input box on a user form?

I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.

View 9 Replies View RelatedI have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.

Sub PO_Flash_Report_Detail() ....

I have a code that print previews a certain amount of rows that I define. This basically says print preview the first 10 lines down.

Can the code be modified so that i can select a start cell. So instead of always starting at A1 I could select say start at G1?

Is there a built-in function in Excel to sum subsequent numbers, starting from 1?

If I give for example 5 as input value to the function, it should return 1+2+3+4+5 = 15.

If I give 3 -> it should return 1+2+3 = 6 and so on.

im trying to learn abit of vba and have put some macros together however cannot figure out where to start for the following

when the workbook opens it checks sheet2 : c10 ="Y" then sheet3! is displayed. If sheet2 : c10 = "N" then opens normally.

I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.

However, I can't seem to build a macro that will run from whatever cell I am in.

Everytime and try it just goes from the cell I was in when I recorded the macro.

For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.

I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"

I'm trying to display payroll numbers (eg 0001234), but the zeros are not displayed. How do I get this to happen?

I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.

In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,

Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following

Cells(J6)*1.04)*2080/12

If False then

ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following

Cells(J6)*43.33)+((J6*1.04)*129.99

If False then

ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following

Cells(J6)*86.66)+((J6*1.04)*86.66

If False then

ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following

Cells(J6)*129.99)+((J6*1.04)*43.33

If False then

ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following

Cells(J6)*1.04)*2080/12, but put this into column P

If row 6 Column O if the month is greater then 1 then I need the formula to do the following,

=J6*2080/12

What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.

In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.

I want to be able to create formulas where the output can act as the input.

For example, I tried to write a simple proof of concept script where you can convert between pounds and kilograms. So if you type in a value for pounds, it'll update the kilograms cell. And if you type in a value for kilograms - it'll update the pounds cell.

This is what I came up with:

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

I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:

I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)

=IF(ISNA(MATCH("N06*";Sheet1!AI:AI;0));"error";INDIRECT(ADDRESS(MATCH("N06*";Sheet1!AI:AI;0)*1;1;1;T RUE;"Sheet1")))

I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.

To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.

I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!$A$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.

View 7 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved