I have Excel 2000.
If I have columns A B C D E how do I copy A and C only (at the same time) and paste them into a new workbook? I get a popup saying it's not possible.
I don't want to copy one column at a time in case I make a mistake and item 1 in column A no longer lines up with item 1 in column C (I'll be performing this at least daily and the results will go on a website)
I have a 'Customer Quote' template form which requires a new number every time it gets saved, this will need to be a 'read only' file.
Can I also have it auto name itself this consecutive number automatically every time it is 'saved as'.
I am trying to simply copy the range of C7:C LastRow and W7:W LastRow
The code below copies all columns C:W to LastRow. I want just column C and column W.
I've tried some other combinations, but it has me stumped.
Code:
'Range("C7:C" & LastRow, "W7:W" & LastRow).Copy
I have to work with a report where columns are sometimes added or removed. However, the columns I need are always there, just not always at the same place. To identify them, I have my macro search for their title (they never change) and enter the column number in a variable (iSID, iPID, iEPN, iEPT, and iOD).
I now need to take these five columns and copy them into another workbook. Usually, I would use something like Range("A:A":"D:D":etc). but now I'm a bit stuck. I wanted to use something like Range(Columns(iSID), Columns(iPID), etc), but I can't seem to be able to write that correctly or something (I get errors like "wrong number of arguments or invalid property assignment"). I guess I could copy the columns one by one using Columns(iSID), but I was wondering if there wouldn't be a more elegant way to do that.
What is the fastest way to delete 2 consecutive columns after skipping 2 columns?
For eg: Column A Column B Column C Column D Column E Column F
I would like to delete column A & B, skip Column C & D, and then delete Column E & F
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.
Is it possible to load non-consecutive columns from a worksheet into an array? This works fine for 1 column:
View 2 Replies View RelatedI 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] ......
I have data in 3 different columns (A, C, and E) to name a few. I want to average each of these columns, but if any of them include zero values, I want to exclude that from my calculation.
Example:
Column A = 10
Column B = 0
Column C = 3
Right now, my "average" formula, is showing the average as 4.33. (average a5, b5, c5)
The real average I'm looking for is 6.5. What is the best way to setup my formula?
I have a worksheet with a series of data in rows. Every 40th row is a sum of the previous forty rows.
I have a second worksheet that in a series of columns uses the 40th row sums from the other sheet. So column 1 uses row 40, column 2 uses row 80 data, etc.
Every time I update this, I create the new column using a copy and insert copied cells and then have to go to each field on the new column that uses this other worksheet to correctly update the formula for the new row. As when I do it this way, if Column 1 cell a1 formula said "worksheet2!A40, then the new column 2 cell b1 says "worksheet2!B40, where I want to make it be worksheet2!A80. I have tried highlighting a series of these corrected columns and dragging the formula over but still get a change in columns vs row from the other sheet.
I am looking to see if there is a way to do something like (in layman's expression):
(first column) worksheet 1, cell a1 = worksheet 2 cell A40
(second column) worksheet 1, cell b1 = worksheet 2 Cell A80
(third column) worksheet 1, cell c1 = worksheet 2 Cell (A80+40)
etc....
I have a table where I need to make a drop down list in two consecutive columns (G and H). The list in column H will depend on the Option chosen in Column H.
Column G
Compensation
Payroll
Column H
COMPENSATION
Savings Box
Update Information General PAYROLL
Payment Inquiry Update Information Severance Pay
As shown above If you select Compensation in G you will have those three options in column H and the same for Payroll.
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....
I am working on two worksheets. On my first worksheet I have one column with consecutive numbering starting from Cell 8.
A8 = 1
A9 = 2
A10 = 3
A11 = 4
A12 = 5
On my second worksheet I want to link to the values in the first worksheet but not in a consecutive manner. Each number will have an interval of 15 cells. As shown below:
D15 = 'link on Sheet1!A8'
D30 = 'link on Sheet1!A9'
D45 = 'link on Sheet1!A10'
D60 = 'link on Sheet1!A11'
D75 = 'link on Sheet1!A12'
I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
I need to count the maximum amount of consecutive crosses that appear in a list. For instance:
x
x
x
x
x
x
x
x
x
the result would be 4, because the highest amount of consecutive crosses in this list is 4 times.
Would anyone mind providing me with a formula for this, please? The list is from C5:C52.
I received the answer on assigning consecutive dates but what can be done to
the formula
=INT($A$1+INT(ROW()-1)/30)
TO:
1. Not include weekends
2. Only assign the date to a certain person's name if there is multiple reps
on the spreadsheet.
I have 3 columns, in column 1 and 2 there will be numbers and I want automatically to get in column 3 the range of numbers between Column 1 and Column 2
Column 1 - 100
Column 2 - 500
Column 3 - 100, 101, 102, ..., 500
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.
i have a list of numbers in a column, and i have sorted it smallest to largest, now i wish to identify which ones are consecutive in the column.
5184730512
5184730547
5184730763
5184730766
5184730767
5184730836
5184731135
5184731136
5184731149
5184731212
5184731222
5184731389
See attachment : totals.xlsx
I'm trying to get a total sum of row 3 only when there is a "1" in each days of the week in the corresponding decimal point in row 2.
Looking at the entire Row 3, cell J3 and R3 have a "1" in consecutive cells so it should be counted as one value (0.25). The same is with cell S3 (0.50), AA3 (0.50) and AI3(0.50) so it should only be counted as one value (0.50). The Consecutive Occurrences cell AP3, should sum up all of the consecutive values, in this case it would be 0.75.
Please note, the colors will not be there, they are only in this example to show the consecutive values.
Is there a formula that can identify all the consecutive intervals that have value below 90%.
View 1 Replies View RelatedI have source data arranged in columns A and B, and in F5 and F6 respectively I'd like to create a formula to return the Null value from column B that's associated with AAA, BBB etc. An example of the results I expect are in F5 and F6.
View 3 Replies View RelatedI have a form that I use often, but numbering is slow because I go in and number the form, print, go back and put in next number, print, etc. Is there a macro or formula that will automatically update the consecutive numbers when I enter or print?
View 13 Replies View RelatedI have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure.
If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc. How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns. I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100
= 100 100 100 *** ***
& *** 100 100 100 ***
& *** *** 100 100 100
= 3
but I need it to equal 1
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
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?
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").
I'm trying to come up with an array formula to count the consecutive declines or increases from the last value in a column, going from bottom to top. The real world application is a list of daily stock prices down a column, and counting from the most recent day, how many consecutive days the price declined or increased.
I got some hints in the following post but can't get it evaluate the resulting logical array in reverse order (which is needed since column addresses are evaluated top to bottom, not bottom to top).
Count consecutive increase in values
Example for consecutive increases leading to last value
49
50
51
52
53
49
55
56
I need a formula to return 2, the number of increases from 49 to 56.
