Macro To Transpose Data And Repeat Itself
Nov 27, 2008
I have spreadsheet which consists of two columns of data.
Column A contains a gerneric heading/group and Column B details some results for each heading/group; these results cover between 16 to 40 rows.
Basically, I need to transpose each heading/group's rows of results data into the same row as the heading/group row; then delete the rows where the results data was copied from; then I want to go to the next heading/group in Column A and repeat the process - transposing the results into a row.
I have written a simple macro which seems to work intermittingly - one which doesn't repeat or loop though. Sometimes I get the Runtime Error 1004 and sometimes the macro overwrites the transposed row if the results are listed over 16 rows.
My draft macro code is as follows:
Sub Transpose()
'
' Transpose Macro
' Macro recorded 27/11/2008 by Hunter
'
' Keyboard Shortcut: Ctrl+p............
View 9 Replies
ADVERTISEMENT
Mar 7, 2013
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6
My recorded Macro:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select
[code]....
View 5 Replies
View Related
Mar 25, 2014
1. The idea is that the macro will start on sheet1, look at column "Jon", copy the values down to the last active cell as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B8 and B9. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
2. The macro will then go to sheet2, look at column "Jon", copy the values to the last active as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B18 and B19. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
3. The macro would then save the "Jon" tab as a separate pdf in a specified location.
4. The process would then repeat for "Mike" and "Paul". Each day the number of columns can fluctuate, so it may be 3 one day (Jon, Mike, Paul) and 8 the next. The number of rows also may vary from day to day, and column to column.
I have attached a workbook that contains the data for the scenario above as well as the output (Jon, Mike, Paul tabs). Ideally there will not be new tabs that remain for Jon, Mike, Paul, just a default tab (e.g. "output" tab) that would receive the transposed data from the columns for Jon, Mike, Paul. I put all 3 in the workbook so you could see how each of them would output.
View 4 Replies
View Related
Sep 12, 2009
I have over 300 lines of data that I need to transpose from columns to rows and I am not sure the best way to do it.
I can do it manually 125 times using paste special and transpose and get the results I need but I am thinking there must be a better way.
Example spread sheet inclosed. Column A is what I have - Column C-F is what I need.
View 8 Replies
View Related
Aug 27, 2010
I was wondering if there was an array formula or some VBA code which can transpose/repeat values based on a count. Below is an example of what I mean:
Name Count Output Tod 4 Tod Bill 3 Tod Sue 5 Tod
Tod
Bill
Bill
Bill
Sue
Sue
Sue
Sue
Sue
View 6 Replies
View Related
Jun 7, 2014
I need macro to transpose the raw data I have extracted from a Biometrics file into the format I need to compute for work hours.
Sheet 1(raw data)
Sheet 2 ( format i want the data to look like)
View 3 Replies
View Related
Feb 9, 2009
I have data that gets dumped from a program into a nasty horizontal format that I need to get transposed into a verticle format. As it stands now, the info is reported with hourly data spread accross rows. I need the hourly data in one column. See attached sheet for an example of what I need done with the data. I'm looking for a macro to take my "original" sheet and create my "new" sheet. Note: a macro that can do this would save me days of time.
View 4 Replies
View Related
Oct 14, 2013
I want to transpose my data. Please see excel file. The original data is of 5 persons (in column) and working hours are recorded over the 35 days (day 1 to day 35 in rows). There are 35 entries for each persons.
I want to do a longitudinal analysis. So I want to transpose data in such a way that each person is repeated 35 time one column, with day in next column (1-35), and working hours are give in front;
For detail see attached excel sheet.
Original table
ID
D1
D2
D3
D4
D5
D6
D7
[Code] ...........
Want to make below table
ID
Day
Hour
1
1
2
[Code] .......
View 2 Replies
View Related
Jan 29, 2014
Have been stuck on an interesting issue I currently have data as set out in the table below.
name
ID
course 1
course 2
course 3
course 4
[Code]....
I need the course list to be restructured such as this below. I have tried using a vlookup with extra bits and a pivot table but cannot achieve what i am after, was thinking maybe a macro that could transpose each row as it drops down but not sure where to begin on that one.
steve
course 1
100
steve
course 2
94
[Code]...
View 3 Replies
View Related
Nov 5, 2008
I have excel file that have the following columns: Sample ID, Analyte Name, Concentration, RSD. These columns are filled down.
I would like the analyte names should be the column headings. Then the rows with the sample ID, Concentration, and RSD - these rows are filled in with elemental concentrations, and rsds associated with those concentrations.
It is not quite a simple transpose, the rows should start over every time the sample name changes. I will be very grateful for any help with this and would gladly send a file to anyone who would help me. Sometimes the number of analytes I sample changes, so if anyone has any ideas on how to write a macro that can do this by recognizing when the analyte name repeats itself (to know when to start a new set of rows).
View 9 Replies
View Related
Jul 15, 2014
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
View 3 Replies
View Related
Feb 18, 2014
I got to transpose the data from sheet "Data" into new sheet "Actual". Now One row has been added on the top (R1) to categorize the whole data in terms of Major and minor.
modify the macro which add one column (K) with header as "Error type" in sheet "Actual" and then display the above two parameters.
I want exact type of data in sheet "Actual" from raw data sheet "Data"
View 3 Replies
View Related
Jun 23, 2007
Need macro that will transpose data from rows to columns
TAN KOON TECK
64 JALAN SEMBILANG DUA KAW 7
OFF JALAN TELOK PULAI
**blank**
**blank**
41100
KLANG
**blank**
next entry
There are blank rows in between the address, and 1 blank row between new entry.
Furthermore, the blank rows between the address are not consistent
e.g.
AZAKI B ISHAK
PT 26 PEKAN JELAWAT
**blank**
**blank**
**blank**
16070
JELAWAT
I am wondering if there is a code that could execute a macro to transpose such data automatically instead of me transposing it one by one
View 9 Replies
View Related
Jan 28, 2008
I have a spreadsheet, with over 9000 line items. And to give you guys an idea, these items are products that we carry at our stores (convinience stores). Most of our items are sold to the customer by eaches or unit, but we do carry beverage items which can be sold several ways (single can, 6pk, 12pk, 24pk etc). Anyway, on the spreadsheet, these sell units(single, 6pk, 12pk etc) have their own line, per each sell unit. I want them to be all in one line. Heres what it looks like:
I want columns F, and G's values be transposed to the first line, after the RET column. Like this:
View 9 Replies
View Related
May 2, 2007
I am in need of perhaps a code that will allow me to offset the data which is highlighted in yellow to something that is similar to the data which is highlighted in green. The logic behind that is i do multiple loans that begin at various months of the year and must be presented into the business plan accordingly.
In this example "spreadsheet included" i chose payment 1 as January but some loans dont begin payment for 2 or more months. Hence the expense and hence the numbers located in the "Control Box" will then be able to offset the data down the number of rows that i specify in the control box at the upper left most of the screen into the section that is highligted in green respectively.
Once the data has been shifted i then need to transpose the data to every two columns in the far most payment schedule which includes the principle "prin" and the interest "int" in the respective months broken down. I need the ability to offset this data a maximum of 11 months as 12 would push the loan into a different year.
I have 15 simultaneous loans so i would need a macro that can do this for me. I am a beginner coding but very familar in excel and a formula would not work as it would be over written each time. I am thinking of if statements but that would be a pain in the rear.
I would then need to repeat this macro for the next 14 loans but in this case i would need to do it four more times and then i would need to do the same procedure for the interest side of the loan.
View 11 Replies
View Related
Oct 5, 2011
Im trying to create a Macro which can do the following: Go to the selected cell when Macro is run, keep going down cell by cell until it finds a blank cell, select the values, copy them and paste them by transposing into the cell next to where it started from in a row, carry on doing this procedure and pasting the transposed values next to the first non blank value until it gets to the end of this column.
View 4 Replies
View Related
Jan 25, 2013
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.
View 3 Replies
View Related
Jul 6, 2014
I need to create a macro in excel that will take data for a particular year in vertical rows and transpose it to horizontal rows.
What the data originally looks like:
380204198401014.72
380204198402016.34
380204198403015.66
380204198404013.71
380204198405013.86
380204198406013.51
[Code] ........
What I want the transposed data to look like:
StationYearJanFebMarAprMayJunJulAugSepOctNovDec
38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25
38020419853.194.211.021.292.372.416.082.500.143.196.122.04
I have never written a macro before and have tons of records (over 40,000) so using this will save me a lot of time.
View 5 Replies
View Related
Feb 3, 2014
Extract data from one sheet to another sheet then transpose automatically.
I have attached the work book of what I'm trying to accomplish.
View 14 Replies
View Related
May 4, 2012
Transaction #Transaction DescriptionAccountDate (XX/XX/XX)DesciptionABCDEFG10/24/2015Cash Held(3404.00)3404.00 0.00 0.00 50.00 0.00 (50.00)10/25/20152Cash not Held0.00 (9707.00)9707.00 0.00 (60.00)60.00 0.00 10/26/2015Cash Held0.00 (7402.00)0.00 7402.00 0.00 0.00 0.00 10/27/20154Transfer to Bank 0.04 0.00 (50.04)0.00 50.00 0.00 0.00 10/28/20155Transfer of Cash0.00 4208.00 0.00 (4208.00)0.00 0.00 0.00
I am trying to make a macro that will turn transpose the above data into the below data.
DateDescriptionAccountAmount10/24/2015Cash HeldA-340410/24/2015Cash HeldB340410/24/2015Cash HeldE5010/24/2015Cash HeldG-5010/25/2015#2 Cash not HeldB-970710/25/2015#2 Cash not HeldC970710/25/2015#2 Cash not HeldE-6010/25/2015#2 Cash not HeldF6010/26/2015Cash HeldE-740210/26/2015Cash HeldG7402
Basically each non-zero number in the accounts (A through G) need to have their own row with the account name from their appropriate column and the date and description in their appropriate row. So as you can see each account A,B,E,and G get the amount -3404, 3404, 50, and -50 in their own row and get the appropriate descipion of 'Cash Held' and Date '10/24/2015'. Then the macro would move onto the next row and do the same thing.
Also if there is a way to tack in front of the description the transaction # (if there is one) ie. #2 Cash not Held. I really don't even know where to start.
View 9 Replies
View Related
May 22, 2012
How to create a macro to transpose rainfall data (in a column) into rows.
I have over 7200 years of data so using paste special will be very slow.
I have attached a sample of the spreadsheet below.
YearMonthRainfallYEARJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
11105.5841126.1212131.72131412.0541514.15451646.6716179.75571824.4838193.
25291105.931011131.1781111231.943122161.0321322109.9142324.751152416.501162512.
164172613.222182787.3719285.5202931.944212104.019222111.1642321221.049243117.
[Code] ..........
View 6 Replies
View Related
Oct 14, 2008
i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies
View Related
Aug 22, 2008
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.
View 9 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Mar 20, 2012
I am trying to repeat this macro until the last row of data present in column "D".
Sub inserttext()
'
' inserttext Macro
'
'
Range("D2").Select
ActiveCell = "Test Description:" & Chr(10) & "" & Chr(10) & "Expected Results: ERROR level fires stating " & ActiveCell
Range("D3").Select
End Sub
View 4 Replies
View Related
Feb 5, 2008
I have a workbook with 4 worksheets. I have a macro ( Cntrl + U ) that runs a series of steps and prints the results with it ending on the beginning page. This works well but I want it to repeat the macro until it reaches a blank cell on the starting page.
View 9 Replies
View Related
Aug 29, 2009
can I add to a macro that will repeat that macro on every subsequent worksheet in the workbook.
View 4 Replies
View Related
Apr 19, 2014
How to repeat excel Macro?
View 11 Replies
View Related
Feb 6, 2014
I am trying to do a vlookup within a macro.
However this always shows the first result in the first row but the rest of the rows just show #REF.
I want it to look up 100's or rows and shows either the result or N/A
Once I create the macro this is what the VBA is telling me: [Code] ......
View 3 Replies
View Related
Mar 9, 2014
I have many set of numbers in column A and Coulmn B each set separated by space.I need to repeat each set to n number of times.
View 14 Replies
View Related
