In cell A2 I have the date 28/12/11, I need that date to repeat 6 times, then in A8 it should say 01/03/11 6 times and so on , I know this can be done but my brain is on a go slow this afternoon and I can't for the life of me work it out
View 3 RepliesI have values
2
8
9
As so on in my cells a1,a2,a3 as so on
And I want value of column a repeating five times of each cell value in column b
like result
2
2
2
2
2
8
8
8
8
8
9
9
9
9
9
and so on
I'm trying to repeat a macro a set number of times depending on the number a person inputs into cell B3.
Here it is currently:
Columns("C:D").Select
Range("C4").Activate
Selection.Copy
Columns("E:E").Select
Range("E4").Activate
Selection.Insert Shift:=xlToRight
Range("C3:D3").Select
I'd like to be able to repeat a list of names X number of times.
Example: Repeat count = 3
Name1
Name2
Name3
Name4
becomes
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
I have never built or used a macro before and need very clear step by step instructions. I am just assuming that a macro is the answer to what I am looking for but if there is a simplier solution that would be fantastic.
I need to be able to repeat a row of data a specified amount of times.
Example:
3 Yellow James
2 Green Mark
5 Purple Sue
1 Orange David
Needs to look like:
3 Yellow James
3 Yellow James
3 Yellow James
2 Green Mark
2 Green Mark
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
1 Orange David
I'm trying to transpose multiple values in Excel, but I'd also like to repeat row values for columns A through E. The attached file "Raw Data" worksheet shows what I start with, and the "End Result" worksheet shows what I'd like the end result to be.
View 3 Replies View Relatedhow I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
View 10 Replies View Related im trying to create a simple random sample from my data set of size n=100 and then i need to repeat that step a 1000 times to make a new data set that i can transfer over to stata. Everything I have seen on the internet involves generating random numbers which isnt what i want. I need to be able to generate a random sample.
Currently my excel spreadsheet consists of 4 columns with 200 entries in each column.
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.
However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.
In MS-Excel 2007 dates seem to be limited to YYYY/MM/DD style, which I imagine there is a way to alter it into MM/DD/YYYY. I looked into MS Help, and a google search, and either I was using wrong keywords, or not, but I couldn't find anything to simply change date arrangement. If I am being unclear, I am talking about the function '=DATE(YYYY/MM/DD)' is what I want to change.
After I get that fixed, I need there to be a function to reproduce a 3-day work week (Mon, Tue, and Thur). e.g.:
10/05/09 [data] [data] [data]
10/06/09 [data] [data] [data]
10/08/09 [data] [data] [data]
Week 1 [data sum] [data sum] [data sum]
10/12/09
10/13/09
10/15/09
Week 2
10/19/09
10/20/09
10/22/09
............................
I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.
Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.
DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?
View 4 Replies View RelatedI'm trying to use countif or similar to show how many times a given date is between 2 dates (inclusive). Each row of the spreadsheet contains amongst other info, a Start Date and an End Date. If i've got 2 rows like this:
StartDate EndDate
01/01/2009 03/01/2009
02/01/2009 04/01/2009
I'd want to lookup 02/01/2009 and have it return 2 (02/01/09 falls between the dates on row 1 and the dates on row 2). To give some context, this is an export from a database containing staff holiday dates, and I'd like to be able to see how many people will be on holiday on any given date, given the start and end date of all holiday occurances throughout the year.
Book1.xls
Here is some dummy data and explanation with my problem...
I can't use usuall sumproduct because array of date doesn't match with data array... Since names are in alphabeticaly order you can see that Dave is sometime at first, second, third position.. there is more names etc...
I need to see how many times some name appear in last x time...
I have a column that can be found in the example attachment. My primary question is, how would I go about filtering out all of the Excel Dates & Times that do not have a multiple of 5 minutes in the "Timestamp". Can this be done w/ out the use of macros by use of the custom filter? My ultimate goal is averaging every hours worth of data so it would rather be hourly data rather than the 5-minute data given. More specifically I would be averaging the "Flow" data. The example given is actually a very small portion of what I am working with. I actually have a years worth of 5 minute data.
View 6 Replies View RelatedI have a list of product IDs in one col. In another 2, I have date and vendor that each items was sent. Note that each items can be sent several time to different ven for at different date. From this list I would like to build another list that shows me WHEN & WHERE each item was last sent.
I was able to find a formula to return a list of unique item for my product IDs, but get stuck on the next step.
I am recording events on a day to day basis in excel using the first column as the date, second column as start time, and third column as end time. So an entry might look like 1-Jan _ 13:00 _ 13:40, in the three columns across. There will be multiple entries for each date, but the number of entries for each date will vary. Entries may also overlap in times. For example, the next row may read 1-Jan _ 13:10 _ 13:45. Some times there will be gaps such as a third row readng 1-Jan _ 14:15 _ 15:00. What I would like to do is compute the total elapsed time spent on projects for each day, not counting any overlaps. SO even though the total time spent on those three projects for January 1st is 120 minutes, I would like to know how much time during the day is spent doing work, regardless of how many projects are being done at the same time. So I would like excel to be able to tell me that on January 1st , 90 minutes of the day was spent on work. Hopefully this makes sense. I also need it to distinguish between dates so that it can tell me how much time was spent on January 1st. then January 2nd, 3rd and so on. It would be great if this ould be computed as the data is entered. Maybe It could record it on a new sheet with a column for each date and then it lists the time spent on that day in the row below it.
View 9 Replies View RelatedI am looking to total a sheet as follows into a header row - Is it possible?
A - B - C
1)Date - Estimated Time - Actual Time
2)
3)7/27/07 - 2 - 4
4)7/27/07 - 3 - 4
5)7/27/07 - 2 - 5
6)7/28/07 - 2 - 4
7)7/28/07 - 8 - 6
8)7/28/07 - .25 - .4
9)7/28/07 - 1 - .5
I would like B2 to read "7/27/07 - 7hrs, 7/28/07 - 11.25hrs"
and B3 to read "7/27/07 - 13hrs, 7/28/07 - 10.9hrs"
I have a file that samples performance on a cpu. The output shows the sampling date and time in column A. This column changes depending on how long I run the file. No matter how long the column, I only want to pull out the sample date and time 20 times. So if I had data in column A with 300 rows, I want to divide 300 by 20 and pull those 20 numbers and put them in column B in chronological order. Same thing if I had 4000 rows in column A, I only want 20 samples.
View 5 Replies View RelatedI am trying to figure out a difference in hours between date & times.
Cell & Info:
A1 = 07/12/2008
A2 = 02:00:00
A3 = 07/12/2008
A4 = 04:00:00
So in cell A5 i want the difference which is: "02:00:00"
but i dont know how to include the data in the formula....
etc if:
A1 = 07/12/2008
A2 = 01:00:00
A3 = 08/12/2008
A4 = 01:00:00
A5 should be = "24:00:00"..
I need a formula that links to another workbook where it looks in a column for the oldest date which appears more than 10 times.
So if the oldest date was 01/01/2006 but it only appeared twice, it would ignore it, but if there was then 02/02/06 which appeared 11 times, it qould quote that date in a cell in the workbook
I am trying to import a .csv file with several fields that are timestamps. I get the file in Excel, and do Format, Cell, date as mm/dd/yyyy hh:mm.
My results come back as 1/0/1900 12:37:22 AM
My raw .csv field looks like 37:22.4
how I lost my date? I did check my system clock just in case, and it appears to be accurate.
the number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.
I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.
What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.
I have been using this statement
if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))
And it just works for the first 2 values c1, c2 and doesn't fit for the others.
The case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.
Here are some samples:
Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02
So as you can see there are more than one event between one start and end dtimes.
I'm creating a spreadsheet that has:
- budgeted dollar amount
- entry each week for that week's total expense
- total expense to date
Question: how can I hold the value for "total expense to date" so that when the new week's info is added the previous total is not lost?
I have been using this statement.
if(and(c1>=a1:a144,c1
I have call data in date/time 06/07/08 2:00 PM custom format for a 2 month period. I have my regular opening times eg Monday 9-3, Tuesday 10-4. For each call I want to know if it was made during opening times or not.
View 7 Replies View RelatedI have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........