How do I duplicate the values to say 4 times in a column?
So I have in a column,
dog
cat
bird
fish
I want to duplicate the above to look like this below,
dog
dog
dog
dog
cat
cat
cat
cat
bird
bird
bird
bird
fish
fish
fish
fish
I'll need to do this for 500 unique values 12 times each, so is there a formula to do this easily?
If I have a spreadsheet with 52 weeks or 365 days worth of data on it, is there a way (without using the hide function) to display dates of a certain range? For example, I only want to see the data from week 30 - week 40. Is there some kind of macro out there that I could maybe enter start date and end date in to a 2 cells (start date & finish date) and it will automatically filter it for me?
View 9 Replies View RelatedI am interested in selecting cells in alternating rows as follows:
12 Red Happy
00 Unique descriptor
13 Grn Sad
03 Unique descriptor
14 Yel Happy
02 Unique descriptor
I am interested in selecting all the "12, 13, etc", "Red, Grn, ..." and "Happy, Sad..." for analysis, but want to skip all the rows containing the 0- numbers and the descriptors for them. How do I quickly select only the alternating cells in a column within a selection?
I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.
I am trying to create a drop down list from a named range that has duplicate names listed. I cannot delete any of the duplicate rows. Is there an easy way to create the list with each name represented only once?
View 14 Replies View RelatedOn some occasions the part number has several to collect but the output from the system we use will output this as a sinle line and a quantity of 2 for instance. I would like a macro to indentify all of the parts with a quantity more than 1 and insert a duplicate line with that quantity.
On the attachement sheet 1 is an example of how the data may look, sheet 2 is how I would like it to be after the macro has been run (but within the same sheet)
I have a worksheet with two columns and a few hundred lines. One has titles and the other integers indicating how many times the title needs to be copied to another worksheet or text file.
Existing Worksheet (Input)
TitleA1
TitleB6
TitleC4
TitleD3
Desired Output (Worksheet or Text File) [The entire row, including the integer could be copied as well, if it is easier.]
TitleA
TitleB
TitleB
TitleB
TitleB
TitleB
TitleB
TitleC
TitleC
TitleC
TitleC
TitleD
TitleD
TitleD
I need a VBA/macro very similar to several I have seen posted here. I have a worksheet with two columns and a few hundred lines. One has titles and the other integers indicating how many times the title needs to be copied to another worksheet or text file.
Existing Worksheet (Input)
TitleA1
TitleB6
TitleC4
TitleD3
Desired Output (Worksheet or Text File) [The entire row, including the integer could be copied as well, if it is easier.]
TitleA
TitleB
TitleB
TitleB
TitleB
TitleB
TitleB..............
I am building an excel folder with 200+ work sheets in it. there is a master sheet which i collect the rest of the data for the other sheets. My problem is, is that i want to be able to change the sheets quickly on the master sheet when i copy and paste or drag down. The formula that i am using in the cell on the master sheet is =SUMIF(sheet18!$B$4:$B$30,"rd1",sheet18!$E$4:$E$30)
Is there a way to quickly change the sheet from 18 to 19 to 20 and so on, or do i have to go into each formula and change it manual??
Any quick way of deleting all named ranges within a given workbook? I can loop over them, but it takes forever. (I've got about 20,000 names in total).
Turning the normal things off doesn't do much good either (e.g. auto-calculating, events, etc)
I have a single column with references on sheet 1, on sheet 2 I have a single named cell 'FormRef' which contents change (references). Active cell is in sheet one column A (column with references). If I use the FIND function dropdown and enter the '=FormRef ', Excel to quote "cannot find data".
Like a shortened version of vlookup I think. I want the active cell to go to the cell that has the contents of the single named cell in
sheet 2
I currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)
View 7 Replies View RelatedIs there a way to quickly format a cell so that it acts as though it is just a number and can still be used in a formula but also have a label? For instance I would like to be able to type in "10cm" and have the cell equal 10 and have it formatted to "General cm".
I realize that this is possible with the cell formatting menu, and can be applied to lots of cells at once, my question is can this be done somehow by just typing into the cell so that I can enter lots of cells that may have different labels without having to go into the cell format menu for each one.
I've built a simple inventory tracking system, and decided a reporting feature would be nice. There are four categories that are entered when inventory is removed...Date, Employee Name, Item Description, Location, and quantity.
Four my reporting purposes I'm only concerned with Date, Employee name and Item Description.
I've been able to write code that does what I want using a multiple Cases and a For loop once the case is identified. However, the more data there is the longer this takes...so I decided to stretch myself and try my hand at arrays (first time really working with arrays), but I'm having trouble figuring out exactly what I need to do.
Here is what I think the steps need to be.
1. Store my data (the categories above) which are located in the Check Out sheet
2. Go through the arrayed data to find exact matches based on my search criteria (here is where the Cases come in)
3. Pull out only that data and write the information to a "Report" Sheet
4. Export that sheet to PDF (this part I already have)
Below is a copy of what my current "working" code looks like (I should mention that the search selections are made from a userform this is what the Cases are deciphering between which ones are blank etc...), also most of my variables are instantiated as Public variables within a Public_Variables module also below.
[Code] ....
And the Public Variables...
[Code] ....
When I type an 11 into my textbox (in my userform), I believe that the moment I type the first 1, the "event" kicks in. Is there anyway that I can have the change even "wait" until I am finished typing in all the digits of my number?
Code:
Private Sub TextBox1_Change()
Dim prime As Integer, divisor As Integer, currentcol As Integer
Dim faccount As Integer, currentresidue As Long, currentfactor As Long
Dim i As Integer, j As Integer
prime = TextBox1.Value
TextBox2.Value = prime - 1
'Write divisors across and phi of the divisors across at the bottom
Spreadsheet1.Range("c2").Value = 1
[code].....
I want to clear the range A10:IV65536 or all the cells containning numbers under the row A10.
Right now it takes for ever to clear it mannually because excel recalculates everything... i have over 100 000 data and id like to find a way to delete them quickly.
Is there a way to clear a range without excel recalculating everything???
If not the best way i think would be to clear the last columns first all the way to the first columns.
I am using Excel 2003 to run a workbook that contains 60 separate worksheets. To make it easier for the techs to navigate the workbook I created a block of 60 autoshapes, (5 blocks high x 12 blocks long) each block hyperlinked to each worksheet and labeled with the worksheet name. I then placed one of these blocks at the top of every worksheet. A tech can click on the block and it takes him right to the page he is looking for, pretty straight forward. It works very well, but as you can guess this is a lot of autoshapes to be stuck in one workbook (3600) and whenever you make major changes to the workbook than the blocks become a factor, and if you resize the columns or add one, or change a worksheet name... I thought about just creating 60 custom buttons and sticking them on a custom tool bar - but I found trying to create a button with a number on it like "34" (for "worksheet 34") was pretty tough in the button editor.
way to create a set of hyperlink buttons that will allow you to quickly navigate through 60 worksheets?
I am currently doing an excel database of students application. I want a macro that creates a duplicate of a template sheet and renaming it based on the name of the student. Meaning once a name appears in the name cell, a sheet of that particular name is automatically created. At the same time, i want all the data regarding the applying student to appear the newly created sheet as shown in the attached file.
View 4 Replies View RelatedWhat is the quickest/best way to find out exactly what is causing a Type 13 Mismatch error?
Debugging with F8 is taking forever as I have to go through loops that go through 6000+ rows each...
I have formula that looks for information from another sheet (sheet name "ValidationLists")in Column A (list "billto") and returns all the occurances in my current worksheet (including the 10 columns next in the array). What it looks for is based on whatever value is currenty in Cell A2.
Example of formula is as follows:
{=IF(ISERROR(INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,ROW(billto)),
ROW(1:1))-1,1)),"",INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(1:1))-1,1))}
Basically I want to return everything that matches cell A2 of my current sheet from sheet list "billto" in a different sheet but I need the adjacent cells as well. So one cell below this would read the following (Just the Row Changes):
{=IF(ISERROR(INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(2:2))-1,1)),"",INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(2:2))-1,1))}
And the column B next to the original formula reads the following (just returning one more column from the array):
{=IF(ISERROR(INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,ROW(billto)),
ROW(1:1))-1,2)),"",INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(1:1))-1,2))}
It is working exactly how I want it to. The problem is it is taking 2-3 minutes. I can just get a list of rows with the value in A2 from List "billto" very quickly but then how do I get the entire row to appear in my current sheet quickly?
I am trying to find out how to quickly remove do not mail names from our mailing lists. We have several lists and our do not mail list is huge(280) so it's getting very time consuming to clean the lists. I'm assuming I can copy the do not mail list names to the current mailing list and then remove duplicates..but I want to remove both duplicates when i do that. My understanding of the remove duplicates feature and unique values is already a bit foggy.
View 6 Replies View RelatedSo I've got Sheet 1 with say
____A___B___C
1___m___i___c
2___r___o___s
3___o___f___t
I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet
and I need this to create a new sheet and do that everytime the button is pressed.....
Let`s assume that I have numerous "Serial Number" in "Sheet1" : http://i40.tinypic.com/2iqbspt.jpg
Now I want to create some cards in "Sheet2" that each card have a serial number that related serial number there is in "Sheet1" : http://i43.tinypic.com/34fz690.jpg. Need easy solution for doing it while when I drag one card to copying it then related serial number in sheet1 would be appear in required field. I mean first card has first serial number of sheet1 and second card has second serial number of sheet 1 ; but I want to doing it quickly with dragging.
I need to select and filter all highlighted data quickly in excel 2007.
View 1 Replies View RelatedI have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?
View 1 Replies View RelatedI'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
Everyone always want to get rid of duplicate rows.
Is it possible to create duplicate rows of all the existing rows?
I have a sheet where I input 8 columns of data from an email template which is sent to me throughout the day. I enter data in one row per email along the 8 columns. eg A1 to H1 then A2 to H2 etc.
Column C has the entry as a job number '123456'.
I need to be able to see if the same job number appears more than once.
In one week I have input 135 rows of data and have spotted 3 occasions where the same job number has been called in.
Any way I can set up a seperate sheet which will search and show duplicated rows from sheet 1.
I have a worksheet change event that prevents a person from entering a positive number in 2 different cells. If they enter a number in one cell, the other locks, and vice versa. The code is below.
View 4 Replies View RelatedIs there a formula I can use in B2 whereby if the value in A2 is identical to the value in any other cell in column A, then return the value "duplicate" in B2?
View 2 Replies View Related