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


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

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 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 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

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

Transpose Macro Varying Rows From 1 Column

Aug 22, 2009

I've trawled a few forums for a while now trying to find a macro that matches my specifications, or one I can adapt but so far have been unsuccessful, so here goes:

I have one column of data (col. A) of customer names and addresses each with an empty row in between them. The lines of data relating to each customer vary from 6 rows to 14 rows, then an empty row, then the next customer and so on - for several thousand rows.

I am wanting to create a macro that will transpose each customer to either their own row in sheet2, or to column B and then delete column A at the end.

All I can find is macros for a specific number of rows rather than varying. One way would be to have a code that 'pads' out those customers with fewer than 14 lines by inserting rows to make them 14, and then a code to transpose 15 rows at a time.

View 9 Replies View Related

Transpose Data From Column To Row

Dec 8, 2013

I need to transpose column data (Sheet called "Recpt") into rows (sheet called "Formula")

Please refer to attached excel file,sheet "Formula". I have manually entered formula for 12/1/2013. Need to add formula for the rest of the sheet. Since the data is on every 4th column, I am sure it is feasible to copy the formula by adding 4th columns.

View 3 Replies View Related

Transpose Column Data To A Row?

Aug 19, 2014

I've got a workbook called MyBook1 which has 12 groups of 5 columns representing each calendar month. I am looking to create a macro (one for each month) which will be executed from another workbook. This macro will pull a column of data from a workbook called Data1 and paste into a row in the MyBook1 workbook.

Using the macro recorder, I've got the following code that works great but was trying to shorten the code and get it to work from another workbook but haven't been successful. I believe all that needs to be done is create a variable for the source/destination workbooks then set the selections and do the copy/paste but I'm having a hard time finding example macros which I can learn from.

Working code below for January, for Febuary the Data1 range is D2:D6, and the destination is 5 columns over making it M3. Then just follow the code below as a template.


View 11 Replies View Related

Transpose Data From Column To Row

Aug 10, 2007

The following data is in column A:

Mobile 19171234854
Work 19171234854

Mobile 9171234854
Home 2121234567

Adrian Simpson
Mobile 19171234854

I would like to move the data to rows with the appropriate headers:
Adele9171234854 2121234567
Adrian Simpson19171234854

As you can see, some of the phone numbers start with 1 and some don't. The listings may or may not include mobile, work, and home phones.

View 9 Replies View Related

Transpose Column Of Data

Sep 11, 2007

I am trying to work with a table that came from a web page that looks like this. ( It has about 200 records.)

Record 1
City, State Zip
Record 2
City State Zip

I want the resulting file to look like this

Record1, Name, Address, City State Zip
Record 2, Name, Address, City State Zip

I can use the Copy / Paste/transpose, but it would look like this. Record 1, Name Address, City State Zip, Record 2 Name Address. I could transpose one record at a time, but that would take a long time.

View 2 Replies View Related

Transpose Raw Data To Column Macros

Sep 4, 2009

transpose raw data to column macros ...

View 9 Replies View Related

Macro To Transpose Data?

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

Macro To Transpose Data

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

Macro To Transpose Data

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


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

Want to make below table



[Code] .......

View 2 Replies View Related

Transpose Data Via Macro

Jan 29, 2014

Have been stuck on an interesting issue I currently have data as set out in the table below.

course 1
course 2
course 3
course 4


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.

course 1
course 2


View 3 Replies View Related

Need Macro To Transpose Data (MS OS X)

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

Transpose Inconsistent Data Row Groups In Column

Jul 1, 2008

I have a data set in an Excel spreadsheet entered as one column of data. It is names with addresses, phone numbers, job title, etc. I want to select and transpose each person in the list so when I am done the person's name is in column A, Company name in column B and so on. The problem is the information listed is not the same for each person - so there is a different number of rows for each person. The names are in bold text though, so I need to select from one cell with bold text to the row BEFORE the next row with bold text and transpose the data for each selection. I found this forum by finding an old question here that is similar at Copy based on Bold Paste-transpose provided in that post and it produced no result.

View 4 Replies View Related

Transpose Macro For Erratic Data

Jun 23, 2007

Need macro that will transpose data from rows to columns

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


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

Create A Macro To Transpose Data

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

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 View Related

Offset & Transpose Data Macro

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

Copy Column Data From Multiple Tabs Onto Specified Tab / Transpose / Repeat

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

Sort Of Transpose Code - Put All Data In Column Vertically On Sheet

Sep 23, 2013

I have 2 columns on sheet 1 as below. I need a code to put all the data in column B vertically on sheet 2 as the result shows. Please note all cells data will be off various lengths all seperated by a comma.

Sheet1  AB2BK
1003 CV1173, CV3133BK1004 CV1010, CV1010A, CV13514BK1005 CV1012, CV1257, CV17995BK1006 CV1836, CV506

Result after code has run.

Sheet2  AB1

View 2 Replies View Related

Excel 2007 :: Transpose Data From Column A1 To Multiple Rows

Feb 26, 2014

I need to transpose data from Column A to Row 2 and down.

The data in column A is in sections of 19 rows and then a blank cell and another 19 rows of data contimuously, It is a dynamic range and can contain many thousands of Rows.

The data needs to be transposed from Column A to row 2 (row 1 has the head line for each column) so the 19 lines of data is now spread accross 19 columns in row 2 and the next section from column A is spread accross the 19 columns in row 3 and so on.

My data looks similar to the below. (Test Number 0001 starts in A1)

Test Number 0001






I use Excel 2007

View 6 Replies View Related

Macro Using Transpose Function For Multiple Years Of Data

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:


[Code] ........

What I want the transposed data to look like:

38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25

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

VBA Macro - Formula Extracting Data And Transpose Into Another Sheet

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

Macro To Transpose Data Into Journal Entry Format

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

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

Copyrights 2005-15, All rights reserved