Convert 1 Row Of Data To 4 Rows And Have It Repeat Every 4 Rows?
Jun 28, 2014I 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 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.
See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
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 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 RelatedHow to highlight rows or data if data repeat in complete row.
View 3 Replies View RelatedI have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
Is 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 student data in the following format
STUD ID, Surname, Forename, Subject
E.g
0001,Smith, Stan,Biology
0001,Smith,Stan,Chemistry
0001,Smith,Stan,Physics
Etc
I would like to display it so instead of there being multiple rows per student there is just 1 row with each subject in a new column
e.g
STUD ID,Surname,Forename, Subject 1, Subject 2, Subject 3
e.g
0001,Smith,Stan, Biology, Chemistry, Physics
Is there any way I can do this quickly for hundreds of students?
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
View 6 Replies View RelatedI am trying to convert some data...
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows:
Company Name - 2nd row
Address - 3 row.
In this case, I want to combile them as one entry Separated by a comma
& placed in one column
Eg: For 3rd one in sample,
Haifa Marble & Tile
69 Garfield St
Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it
into one column separated by comma.
I 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
Solution to convert data columns to rows in excel.
View 4 Replies View RelatedEvery day I collect 2 values. The data is placed in a row, with this scenario:
Day1Val1, Day1Val2, Day2Val1, Day2Val2. etc.
On any specific day, either Val1 or Val2 can be the larger value.
I want 2 rows, with the following scenario:
Row1:
MAX(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
Row2:
MIN(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
This is for a Min/Max (stock) type chart.
Entering the formulas for the first 2 columns and then extending doesn't work because it single steps column numbers instead of double-stepping.
I already have 5 weeks of data, (and 4 rows: 280 cells) so I don't want to painstakingly do it manually.
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 RelatedWhen I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
In the excel sheet bellow that I'm attaching you will see what I'm looking for.
View 8 Replies View RelatedI'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 RelatedI have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
Copy Row A1:X1
select sheet 2
Paste Special > Transpose (fills A1:A24)
repeat for rows 2-365
Tried using this from a previous post, but it takes A1:A24 then B1:B24 and so on and makes them a single column - so it doesn't work unfortunately.
Sub MakeColumn()
Dim rng As Range
Dim LastRowSrc As Long
Dim LastRowDst As Long
Set rng = Worksheets("Sheet1").Range("A1")
[Code] .....
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between, simultaneously keep the numbering in sequence only after the repeats 2 rows in between, and perform the previously mentioned 2 tasks any row in a column. 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
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ] 1
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+1 1
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+2 1
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+3 2
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+4 2
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+5 2
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+6 3
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+7 3
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+8 3
and so on...
I have a 2000+ rows to manually input and hence it is not practical!.
I need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.
View 4 Replies View RelatedI have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
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.
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.