I am trying to produce a list of numbers (preferred Sheets Function and not VBA).
Every group should be of 5 Identical Numbers - starting with 2 and step of 2.
(An Example is shown in the attached picture).
I would like to produce that list with the help of ROW() and all available build-in Functions.
If possible, I will appreciate very much some explanation as for the principal of producing such a list when, for inatance, the step is 3 , starting from 3, while every group includes only 4 identical numbers and the first cell, to present the first value, is A11 and down to A26 - presented here in Horizontal layout:
(3,3,3,3,6,6,6,6,9,9,9,9,12,12,12,12)
I have written a macro, big cheers as it's my first, and it even works as I wanted it to.
Trouble is when you run it, it shows you all the steps it is making. Is there any way to stop this and purely for the end result to appear? It may well also have something to do with my PC needing more RAM to make it faster. Ever since upgrading (?) to 2007, my PC feels like it is stuck in quicksand.
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this?? Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select ActiveCell.FormulaR1C1 = "3.1" [CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW] Range("A1").Select ActiveCell.FormulaR1C1 = "3.2" [CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
I have 2 columns of data with 7,000 rows that I am trying to manipulate in the following way. The first column contains a list of car manufacturers (not really my example, but involves the same concept). The second column contains a list of different models of cars. For instance,
Ford | Taurus Chevrolet | Avalanche Saturn | Sky Ford | Explorer Honda | Accord Ford | Taurus
What I want is a list of 2 columns with UNIQUE car manufacturers in first column with a list of UNIQUE car models (comma delimited) in second column. So for this example, the first row could read " Ford | Taurus,Explorer". I don't want Taurus to appear twice. I got a lot of help from JBeaucaire from this post. I just can't add the ability to remove car models from the list also. Thanks in advance for all the help!
I have an excel spreadsheet that I am trying to format. Each time the spreadsheet is of the same format but with differing amount of numbers. I.e. sometimes it will be a set of 3, the next time might be 6.
I want to write a macro that will find a set piece of text, move it offset(-1,2) and then find the next one.
I am looking for a macro that works like the ASAP Utility (Transpose data from one column to several columns in steps). To elaborate the work done by the macro it should transpose the values in a column to the number of steps that is user defined (Using InputBox) that is if there are 103 values in the column and the user enters the number of steps as 24 then the macro should transpose the data up to 24 columns and the rest in the next row up to 24 columns and so on unless the complete data is transposed.
For more clarity refer the attached excel sheet or the "Transpose data from one column to several columns in steps" utility of ASAP Utility.
I have the onerous task of dividing up a series of steps in one cell into multiple cells. I tried the text to columns utility, but it only delimits once, not multiple times. Below is a data example:
"1) Load App 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) click on subcategory 'Administration'"
"1) Load App 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) Click on subcategory 'Administration' . 5) Click on any Organization name from 'Organization' section in the list. Or click on 'Add Organization' icon in section 'Organization' . 6) Validate field 'Parent Organization' in 'New Organization' or 'Edit Organization' form" "1) Load Documoto. 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) Click on subcategory 'Administration' . 5) Click on any Organization name from 'Organization' section in the list. Or click on 'Add Organization' icon in section 'Organization' . 6) Validate field 'Tenant ERP Accessible' in 'New Organization' or 'Edit Organization' form"
1) Load App 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) Click on subcategory 'Administration' . 5) Click on any Organization name from 'Organization' section in the list. Or click on 'Add Organization' icon in section 'Organization' . 6) Validate field 'DMZ license key expiration period ' by click on checkbox or by 'Uncheck' checkbox"
I have a project with over 600 steps which we are trying to track when each part progresses by having 52 weekly columns. Their are 8 users which we have allocated a colour so when they complete one of the steps they select which step they have completed and which colour they are. This works well but I would like to put the date that the change was made in a separate cell in that row.
My question is can this be done with a formula as I am in favour of doing this as soon as someone changes a cell in the week range. If not I will need to nut through a VBA sub routine.
I know that this formaul does not work but it is along the lines of what I am testing for: =if(sum(T55:BM55)>" ",Date(),0) where if any of the cells in the range T55 to BM55 have any value or letter in them then put todays date in cell L55 else do nothing.
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
how to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000 Jung Joe $700 White Jon $100 Jones Jim $200 Jones Jan $300 Jung Joe $800 White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount Jung Joe $800 Jung Joe $700 Jones Jim $1000 Jones Jim $200 Jones Jan $300 White Jon $200 White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).
The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)
I would like to know if there is a way to Auto number a text. I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down. Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on
is possible to add a hyperlinked TOC (Table of contents) within a spead sheet (Excel 2003) like it is possible to do in Word. Also how do I go about sequential numbering of rows with sub numbering as shown below? Where if i add a row between 1.2.2 and 2 it would be 1.3.
I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...
1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.
2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?
3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?
I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150.
So in the end it would go from: (sorry for the periods.. extra spacing didn't work!) A........B................C.......D AAA...123 Street...City...<blank> BBB...456 Street...City...<blank> CCC...789 Street...City...<blank>
I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it
I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.
I'm trying to make a sequential resultlist starting with nr 1, 2, 3, etc under the column: Rank ? This should be part of a macro, so autofill is not an option... As you can see, the number of rows are different from each group, and starts with nr 1 for every group. (Some formatting became all wrong posting this.........
i have formulas in a range L5:L15 which sometimes return some value and sometimes zero. i want to give them auto numbers in column M in a way that it should only count the cell which has some value. suppose formula in L5 returns some value, L6 also then L7 & L8 have no value(but formula persists), cell L9, L10, L11 has values then L12 has no value L13, L14 has value and L15 has no value (but it has formula in it) values in these cells changes and some goes to zero and some return values. now i want to give them Auto Numbers in a way that cells with some value should only be considered.
I have a spreadsheet, there is large number of items entered. Now I want to numbering of this Items. There is approximate 250 items that are start with A, there numbering will be 1-250 then start with B, there will be numbering 1- something like this, then start with C....
I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once.
