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


Macro: Transpose 1 Column Into 3 Columns

Aug 24, 2006

I am trying to transpose 1 column into 3 columns:




Here is the code I have:...................

View 4 Replies View Related

Vb Code Macro Needed To Transpose Data From Columns To Rows

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

Transpose Data With Macro And Add One Column

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

MACRO To Copy And Transpose Data In A Column

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

Macro To Transpose Multiple Data In Cell Separated By Commas To Each Data In Column

Jul 15, 2014

I have a table in the format below with about 3500 rows

Column A
Column B

All vehicles, Retirements

All vehicles, Retirements, Addition

All vehicles, Retirements, Addition, Deletion from Y

I would like to change it to the following format:

Column A
Column B

All vehicles


All vehicles



All vehicles



Deletion from Y

View 3 Replies View Related

Creating Macro To Transpose Column Data To Rows

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.


[Code] ..........

View 6 Replies View Related

Transpose Each Row Of X Columns Into Single Column

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

View 7 Replies View Related

How To Transpose A (table) Column Into Multiple Columns

Jul 10, 2012

This is a sample of the attached workbook.

Current Phase
Start Date
Planned Finish


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:



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

Transpose Column To Rows - Referencing Columns By Number

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


View 1 Replies View Related

Do Not Show A Macro's Steps

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

1st Steps To Learning VBA/Macro's

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

Excel 2007 :: Macro To Transpose Two Columns

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

Add Time Delay Between Macro Steps

Jan 15, 2009

ActiveCell.FormulaR1C1 = "3.1"
ActiveCell.FormulaR1C1 = "3.2"
ActiveCell.FormulaR1C1 = "3.3"
ActiveCell.FormulaR1C1 = "3.4"
ActiveCell.FormulaR1C1 = "3.5"
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:

ActiveCell.FormulaR1C1 = "3.1"
ActiveCell.FormulaR1C1 = "3.2"

View 4 Replies View Related

Transpose Several Columns Of Data

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

Macro To Copy / Transpose And Paste Columns To Rows

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

Changing Existing Macro To Transpose Rows To Columns

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.

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

Macro Search For Number With Variable Steps

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

Data In Columns To Rows (not Transpose)

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

Getting Data From Columns To Rows Without Using Transpose

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

Macro To Recognize Content Between Two Fixed Values And Transpose To Columns?

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

Extract Data In Rows And Transpose To Columns?

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:


View 2 Replies View Related

How To Transpose / Move Data From Columns To Rows

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.

Contact: Toine Kets
Managing Director
Str. Tache Ionescu 3, Et. 5, Apt. 10


View 7 Replies View Related

Layout/Transpose Data Rows Into Columns

Jun 17, 2008

I have a following table:

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?

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

Transpose Multiple Columns To Rows Based On Match Via Macro Or Formula

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

Transpose Or Convert Rows Of Data Into Columns Of Data:

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

Transpose Repeated Data With Unique Associated Data Across Columns

Jan 17, 2008

Is it possible to associate a range of cells containing different information based on like values in other cells?


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

Selective Transpose Multiple Rows Of Data To Columns

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
visit 1
visit 2
visit 3
visit 4
visit 5


I'd like to extract the data into a new table on another worksheet that looks like this:

study id
visit #
Test Name


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

Transpose Rows Keeping Heading In Column A And Data In Column B

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

Transpose Data From Rows To Columns, Export To A Tab Delimited File

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

Copyrights 2005-15, All rights reserved