Macro To Transpose Data From One Column To Several Columns In Steps?
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
ADVERTISEMENT
Aug 24, 2006
I am trying to transpose 1 column into 3 columns:
Example:
1
2
3
4
5
6
7
8
9
ect...
into
123
456
789
ect...
Here is the code I have:...................
View 4 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
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
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
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
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
Apr 27, 2008
I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached)
Raw data in columns A,B,C,D,E as below
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
Converted should be
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2
View 7 Replies
View Related
Jul 10, 2012
This is a sample of the attached workbook.
Project
Task
Current Phase
Start Date
Planned Finish
Status
[Code]...
What I am trying to do is break up the "Phase" column into several columns, where each phase would have its own column. Something like this:
Phases
->
->
->
->
->
Project
Task
01.
02.
[Code]...
It is very important that the output of the data goes on a new sheet, and that the records maintain integrity.
View 3 Replies
View Related
Jun 19, 2012
I'm trying to create a macro that transposes data from columns to rows.
My source data is laid out so Column A and B contain item identifiers, and then the header for Column C to Column S contain dates (March, April, May, etc) and the row data below contains quantities for each month. This is on Sheet1.
I need my end data (on Sheet2) to have the item detail in Column A and B, the quantity in Column C and the Date in Column D. If there is a date that has no quantity then it should be skipped.
Sample Source Data (Pipes added for clarity, they aren't in the actual data)
Part |Description |June 4 |June 11 |June 18
A | PartA | 5 | | 12
(Please note June 11 has no quantity)
Sample Destination Data:
A | Part A | 5 | June 4
A | Part A | 12| June 18
Below is the start of the code, obviously I have a ways to go before it's fully functional but hopefully you get the idea how I'm trying to attempt this. Where I'm having issues right now is referencing columns by number. Is there a way to do this so I can do Column = Column + 1 to advance columns to the right? Or any better way to address this all together?
HTML Code:
Sub ConvertToRows()
Dim ReviewRow, ReviewRowEnd, PasteRow, ColumnNumber As Integer
ReviewRow = 2
[Code]....
View 1 Replies
View Related
Jul 15, 2009
I have written a macro, big cheers as it's my first, and it even works as I wanted it to.
Trouble is when you run it, it shows you all the steps it is making. Is there any way to stop this and purely for the end result to appear? It may well also have something to do with my PC needing more RAM to make it faster. Ever since upgrading (?) to 2007, my PC feels like it is stuck in quicksand.
View 5 Replies
View Related
Feb 21, 2010
Need advice/opinions of where to go 1st to learn all about VBA/Macro's from a novice point of view.
View 3 Replies
View Related
May 30, 2012
I have a file with 2 columns (A=headers, B=values x 38K rows) and need to transpose them to rows.
I found code to transpose a specified number of rows in a column (i.e. every 5th row in the column)...
BUT my columns have variable numbers of data points, because in the exported file any head with a null value was excluded.
So I need to start new rows with a specific text value of "Username" occurs in column A.
View 2 Replies
View Related
Jan 15, 2009
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.3"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.4"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.5"
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.6"
etc . . .
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this??
Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.1"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
Range("A1").Select
ActiveCell.FormulaR1C1 = "3.2"
[CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
View 4 Replies
View Related
Aug 26, 2009
I'm working on this project that I inherited from another colleague and am at a sticking point. The workbook is used to determine which employees are working on which projects what pct of the time. The workbook was set up using a start and end date (columns e and f) for the projects instead of a column with the month and the correct percentage. I've set up monthly columns, which are now in columns K through AF. I believe that I need to transpose those columns into a row so that I can set up a pivot table.
View 5 Replies
View Related
May 1, 2013
I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.
View 1 Replies
View Related
Feb 22, 2008
I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
View 9 Replies
View Related
Apr 8, 2009
I have an excel spreadsheet that I am trying to format. Each time the spreadsheet is of the same format but with differing amount of numbers. I.e. sometimes it will be a set of 3, the next time might be 6.
I want to write a macro that will find a set piece of text, move it offset(-1,2) and then find the next one.
For example....
View 14 Replies
View Related
Nov 19, 2008
I am trying to clean up some data, which is organized horizantally AND in rows, as per the attached screenshot.
The fact that there is data horizontally across seven week days and in rows for the weeks of the year makes it impossible for me to use the simple TRANSPOSE feature, of which I am aware...
I have quite a couple of worksheets I need to apply this operation on, so manual work is the worst solution...
View 13 Replies
View Related
Mar 26, 2009
I have an excel spreadsheet which contains data for customers and the last date they were seen at an appointment (along with various other bits of info).
The way the data is exported from my database package means that each customer has one row per appointment, i.e. row 1 contains john smith, 01/01/2009, row 2 contains john smith, 03/03/2009, row 3 contains john smith 01/04/2009, row 4 contains joe bloggs 12/02/2009, row 5 contains joe bloggs 27/03/2009. Some customers may have 4 or 5 appointments listed whereas another customer may only have one. I need to get the appointments all onto one row per customer so that I can calculate the number of days between appointments. I have tried to use transpose, but with 8000 rows it takes forever.
View 4 Replies
View Related
Oct 26, 2012
I have a long (190,000) list of customer data, all in Column A (unfortunately with blank rows among it, but working now to fix that).
Down the column, individual customers are bookended by a "adf" and a "/adf". (these have open and close brackets like HTML code, but I cannot reproduce them in this forum).
For each customer, I need to find the rows that begin with (brackets spelled out since I do not know how to show them):
1. [open bracket] vehicle status
2. name part=3D"first"[close bracket]
3. [open bracket]name part=3D"last"[close bracket]
4. [open bracket]email[close bracket]
5.[open bracket]phone time=3D
6.[open bracket] name part=3D
And transpose only those rows it into columns.There is a dynamic number of rows for each customer, so there's no way to simply count and transpose, as the columns would all be mis-entered.Somehow it needs to recognize those 6 row items, and transpose those values only, with the and the only telltale of a start and finish of a specific customer.
EDIT: How about a macro to delete all rows except those that contain those partial values above?
View 3 Replies
View Related
Feb 13, 2014
I am struggling with an Excel Database, to make it "cleaner". Here is my problem. My database looks something like that:
Column A Column B Column C ... Column H
ID Age Date of Birth ... Language
00001 14 01/01/2000 ... English
00001 14 01/01/2000 ... French
00002 14 01/01/2000 ... English
00003 14 01/01/2000 ... French
00003 14 01/01/2000 ... German
00001 14 01/01/2000 ... Spanish
Basically, Columns B & C will never change for the same ID, but columns G, H and others contain data that is different from one row to the other. What i wwould like to do is having unique values in Rows, with Languages displayed in Columns. The database would look like this:
Column A Column B Column C ... Column H Column I Column J
ID Age Date of Birth ... Language 1 Language 2 Language 3
00001 14 01/01/2000 ... English French Spanish
00002 14 01/01/2000 ... English
00003 14 01/01/2000 ... French German
The challenge is that I would need this to be done with formulas only, not using any kind of code. Deleting duplicates manually after "cleaning" the database should'nt be a problem. I tried a formula found on this forum, but i couldn't manage to make it work. The formula looked like this:
{=IFERROR(INDEX($C$4:$C$8;SMALL(IF(FREQUENCY(MATCH($C$4:$C$8;$C$4:$C$8;0);MATCH($C$4:$C$8;$C$4:$C$8;0));ROW($C$4:$C$8)-ROW($C$4)+1);ROWS(C$16:C17)));"")}
View 2 Replies
View Related
May 18, 2013
So, I have a column with data on rows as follows below. I need to arrange the data below in such a way that I have on first column the company name, second column the contact person and so on depending on what data is found (tel, e-mail, website).
The data is on rows and I have separated each company/group of data with a row between them. There are in total aprox 200 companies/groups of data that I need to arrange as explained above.
I have tried with transpose but I have to manually do it 200 times. I have tried with an indirect formula but the companies/groups of data do no have same amount of info/rows e.g. some lack the phone number or other data.
if there is a method to save the time and not arrange them manually.
Actinote
Contact: Toine Kets
Managing Director
Str. Tache Ionescu 3, Et. 5, Apt. 10
[Code]....
View 7 Replies
View Related
Jun 17, 2008
I have a following table:
A B C
1 City Name List
2 NY Peter 11; 23; 12; 11; 14
3 Toronto John 24; 25; 87
How can I, in a separate worksheet, create a following table?
A B C
1 City Name List
2 NY Peter 11
3 NY Peter 23
4 NY Peter 12
5 NY Peter 11
6 NY Peter 14
7 Toronto John 24
8 Toronto John 25
9 Toronto John 87
View 4 Replies
View Related
Jul 10, 2008
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
View 9 Replies
View Related
Feb 12, 2009
When 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)?
View 3 Replies
View Related
Jan 17, 2008
Is it possible to associate a range of cells containing different information based on like values in other cells?
Example:
How it currently appears in spreadsheet
Name Acct#
John Q. Public 12345
John Q. Public 23456
John Q. Public 34567
John Q. Public 45678
Jane Example 11111
Jane Example 22222
Wanted Result
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
Jane Example 11111 22222
Jane Example 11111 22222
I know how to remove the duplicates afterwords to get one unique record.
View 4 Replies
View Related
Jul 21, 2013
How to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:
study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5
[Code]....
I'd like to extract the data into a new table on another worksheet that looks like this:
Date
provider
study id
visit #
7/21/13
Test Name
10001
[Code]...
This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...
View 14 Replies
View Related
Mar 2, 2011
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
View 7 Replies
View Related
Mar 12, 2003
I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.
There are no spaces between entries.
View 9 Replies
View Related
