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 RepliesI'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.
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
In the excel sheet bellow that I'm attaching you will see what I'm looking for.
View 8 Replies View RelatedIn 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 Replies View RelatedI 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
how 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.
Basically on the file below whenever there is data in column "type" I want excel to copy the "name" and "account" next to it. The whole file has about 80,000 rows. Spacing is not always one blank row between accounts. I am using excel 2007.
View 5 Replies View RelatedThe following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
I have a excel table in Cell B2:C2 to B10:C10 in sheet1. I need to copy this table "x" number of times as specifiedin A2, with an row offset (gap) of 10 rows starting D2 in sheet2.
But somehow, only the first row of the data is getting copied?
I have the following macro: .....
I have a table which contains a list of branches eg;
01
02
04
06
I then have a spreadsheet which contains a number of columns and rows.
I want to create a new spreadsheet in the first column it will contain the branch from the table above then the rows and columns in the above spreadsheet
The rows and columns would then repeat for each of the other branches in the table. IE if I have 4 branches in my table and 100 rows in my spreadsheet my new spreadsheet would contain 400 rows
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.
I frequently use a pivot table to extract summaries from a large data set and then use the pivot table output itself as a data table for further analysis. Where a data value in field 1 has many corresponding values in field 2, the field 1 value appears only in the first line and the field 1 cells for the remainer of the rows below appear as blank.
Currently, I copy the pivot table and paste values elsewhere and populate the blank cells with the value above in order to get a complete data set. I would like to find out if there is any way the pivot table can be configured so that where a field 1 value has multiple corresponding items in field 2, each of the cells in field 1 would repeat the relevant value.
I have raw data stored in one row which I would like to break up into four individual rows.
I cannot get the formula to repeat and I don't want to manually do it.
I have 3 columns and 200 rows. The first column is for Name of product. 2nd column for number and 3rd for $value of number of products. Have 200 rows. Have done a sort and checked. 2 sets of rows are for the same product name. I want to know if it is possible to add up values in 2nd and 3rd column, 2 rows at a time , then 4th and 5th column and so forth, by some formula rather than doing it manually?
View 4 Replies View RelatedI have a pivot table in Excel 2003 which summarises one field (subjective) then totals at the change of a second field (cost centre). I want to show the cost centre label on each line of subjective. In a standard pivot table it only shows the cost centre with the first subjective in each group.
View 2 Replies View RelatedIs it possible to repeat rows on top of every page but one? Specifically, I have a seven page spreadsheet and I don't want the rows to repeat on the seventh page.
View 6 Replies View RelatedI have a spreadsheet that contains traffic count data for a two lane road. The data is by direction, by hour, 24 hours a day, 365 days a year so the data is basically 2X 365 = 730 rows of data.
I need to simply add the two directions together for each hour of each day, basically add two rows together, drop down to the next two rows and add them together, repeat.
I need the result as stand alone data on another spreadsheet so the Data, Subtotals operation provided by Excel won’t work for me but something similar that puts the data on another spreadsheet would work.
I also tried to use a formula in the new spreadsheet that added two cells in two rows on the original spreadsheet together and then repeated that process a few times and then tried to drag the formula down but couldn’t get it to repeat correctly.
This macro is recorded, but I need it to start on row 2 and repeat down to row 3000. What changes do I need to make.
I have a list of rows with Yes and No check boxes. Columns "T" and "U" contain the output of these checkboxes (i.e. "TRUE" or "FALSE"). At the end I have a button which I'd like to run this code. Essentially, I want to code to go through row by row and do the following:
1. Check to see if both boxes are blank or if both boxes are checked - and if so set the background color to yellow
2. Otherwise set the background color to clear
I have written the following code which accomplishes this task for Row 9. Is there an easy way to repeat this process for rows 10-15 without copying all the text and changing the row numbers?
Then, any way, say "If all the rows (i.e. 9-15) have clear background colors (which would mean that they all 'passed' the test of having exactly one and only one box checked in the row), then run another macro which I have written"?
Rem Check to see if neither box is checked or if both boxes are checked and sets background color to yellow, otherwise sets background color to clear.
If (Range("T9").Text = "") And (Range("U9").Text = "") Or (Range("T9").Text = "TRUE") And (Range("U9").Text = "TRUE") Or
(Range("T9").Text = "FALSE") And (Range("U9").Text = "FALSE") Then
Range("B9", ("G9")).Interior.ColorIndex = 6
Else: Range("B9:G9").Interior.ColorIndex = 0
End If
I am a VB novice and I cannot seem to figure out how to loop a macro. So here is what I have...
Two worksheets, Sheet1 and Sheet2. I want to copy data starting from the second row of Sheet2 and then past it into specific cells on Sheet1. Then I want excel to print the the page. I want this process to repeat itself for all the rows in Sheet2. I can easily enough record a macro to do this process (which I have), but I cannot figure out how to easily loop it. If someone can help I have to print this log daily to meet federal guidelines. Thanks a million everyone!
An example would be to copy cell A2 on Sheet2, then paste in cell D4 of Sheet1, print and repeat for the next row.
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between and simultaneously keep the numbering in sequence only after the repeats 2 rows in between . So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.
At Present:
X4 1
X5 2
X6 3
X7 4
X8 5
X9 6
X10 7
I need to have
X4 1
X5 1
X6 1
X7 2
X8 2
X9 2
X10 3
X11 3
X12 3
and so on
I have a 2000+ rows to manually input and hence it is not practical!.
I have data in rows like this
A B C
1 x y z
2 x y z
3 x y z
4 x y z
5 x y z
6 x y z
7 x y z
8 x y z
9 x y z
10 x y z
11 x y z
12 x y z
What I am looking for is that I want to repeat the rows after every third row in another column. For eg
A B C D E F G H I
1 x y z x y z x y z
2 x y z x y z x y z
3 x y z x y z x y z
how to get it to fill down. Basically depending on the contents of column D, a formula (which also needs to fill with the rows) is copied from sheet 2 in to column K.
Code:
Sub Copy()
With Sheets("sheet1")
typ = .Cells(Rows, Count, "D").End(x1Up).Row
[Code]...
That's what I have. I am totally stuck. the formula needs to relate to column G and fill down too. this is the formula
HTML Code:
=IF($G2=2,$G2*90,IF(AND($G2<4,$G2>2),"$270.00",IF($G2>6,360+(($G2-6)*50),IF(AND($G2>=4,$G2
My F4 key (which I use constantly) works for every "repeat" function except for inserting rows or columns. I can repeat every other option EXCEPT inserting rows/columns. I just had Office 2007 uninstalled and had Office 2003 put back on my PC. My IT group can't figure it out.
View 3 Replies View RelatedI currently have a spreadsheet with 4 columns that represent Surface corridinates and Bottom Hole Corridnates.
Surface X, Surface Y, BH X, BH Y
We need to get the Bottom Hole XY to be directly under the Surface XY.
Attached is some sample Data and What I mocked up for a temp solution is in VBA Module2
RunAll it will complete 399 rows of data.
In Module1 I have the starting Code that I modeled off of a macro that I started to get the base code.
I just need this to loop till it has no more data to copy. and not use module2 at all.
I commented everything for my personal benifit and to help me understand the steps. also know its a monster right now but it does most of what I need it to do.
Sub CopyPasteBHXYs()
'Wrote Based on recorded macro
'Selects second data row and inserts a row above
Rows("4:4").Select
Selection.Insert Shift:=xlDown
'How to make it loop till the last data is reached?
End Sub
Side Note I'm finally taking a Excel VBA course Apr 8th and 9th.