Transpose Data From Column To Row

Aug 10, 2007

The following data is in column A:

Adam
Mobile 19171234854
Work 19171234854

Adele
Mobile 9171234854
Home 2121234567

Adrian Simpson
Mobile 19171234854

I would like to move the data to rows with the appropriate headers:
NameMobileWorkHome
Adam1917123485419171234854
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


ADVERTISEMENT

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

[Code].....

View 11 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
Name
Address
City, State Zip
Record 2
Name
Address
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

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

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

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

Transpose Raw Data To Column Macros

Sep 4, 2009

transpose raw data to column macros ...

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

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

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.

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

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

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

21-Feb-2014

Kettel

Office

Demo

[code]....

I use Excel 2007

View 6 Replies View Related

Transpose Multi-Column Groups/Repeats Based On 1 Column

Mar 19, 2009

I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.

This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)

View 6 Replies View Related

Text To Column With Single Column Transpose?

Aug 13, 2014

I have a file that I need to do a text to column separated by comma and then transpose results to a single column. See exaple below

File
AreaZone
0886518
1801315
1801413
1801524, 25
1804214, 16, 18

Results:

AreaZone
0886518
1801315
1801413
1801524
1801525
1804214
1804216
1804218

View 8 Replies View Related

Transpose Every Other Row To Column

Aug 26, 2008

I have a sheet with a layout similar to the following:

Network Location | Visits
Company A | (empty cell)
% Change | 8%
Company B | (empty cell)
% Change | 5%

Is there a simple way (I'm using Excel 2007) to make it appear like so:

Network Location | % Change
Company A | 8%
Company B | 5%

View 2 Replies View Related

Transpose Column

Jan 26, 2007

Is there a way to transpose or swap a column or row of data. e.g. A column of numbers going from 1 - 10, swap them around so it goes 10 - 1 in the same place?

View 7 Replies View Related

Convert Column To Row Without Using Transpose

Jan 31, 2009

i am looking to convert data in columns to rows without using transpose but by formula. and i can drag it across to the right to keep getting new data.

i see posts mention about using functions like index, match? or other functions to do it?

i have attached the excel.

View 6 Replies View Related

Transpose Range From Column To Row?

Aug 20, 2013

I have code below that I need to take a copied range and Transpose the range from a column to a row.

Code:
Sub Search_Notes_Main()
Application.ScreenUpdating = False
Sheets("Analyst Main Template").Select

[Code].....

View 1 Replies View Related

Transpose The Values In A Column

Jun 17, 2008

i want to transpose the values in a column to a row and when i update the values in the column it should be transposed to the next blank row..

View 10 Replies View Related

Transpose 1 Column Based On Another

Nov 3, 2009

I have a spreadsheet with the following columns

No Of Transactions
Total Revenue
LID
OURSUB
SubCount
Name
Phone
Phone2
Email
Phone3
Phone4
Phone5
Phone6
Phone7
Broker
Bcode

There are 28,000 records in the spreadsheet.

The column LID contains the ID of the client. Each client can have transactions in multiple subs ("oursub" column). The spreadsheet is sorted by the LID column.

What I need is for the "oursubs" values to be transposed column headings for each unique LID. E.g. if "LID" 12345 has 50 records with 25 different "oursubs". I want one row with LID "12345" and 25 columns with the names of the 25 "oursubs".

I am attaching two sample spreadsheets. The spreadsheet "LID_Source.xls" contains sample source data and the spreadsheet "LID_result" shows the result that I want.

View 9 Replies View Related

Transpose / Transform A Table To One Column?

Jul 10, 2013

i have a data as below

abc1
def2
ghi3
jkl4

and i need the data as below in one column

a
b
c
1
d
e
f
2
g
h
i
3
...........

needed in this regard either macro or formula

View 5 Replies View Related

Transpose Formulas From A Line To A Column

Jul 27, 2006

How do I transpose formulas from a line to a colllumn?

View 12 Replies View Related

Transpose Rows To Single Column

Feb 23, 2012

I'm trying to do something which I can't manage with traditional formulas and a macro might be required.

I have the following table:

Code:

Header1Header2Header3Header4Header5
1.00 6.00 11.0016.0021.00
2.00 7.00 12.0017.0022.00
3.00 8.00 13.0018.0023.00
4.00 9.00 14.0019.0024.00
5.00 10.0015.0020.0025.00

What I would need to do is take all column values and transpose is to rows, copying the header for every set, like:

ColumnA ColumnB
Header1 1.00
Header2 6.00
Header3 11.00
Header4 16.00
Header5 21.00

[Code] ...

View 8 Replies View Related

VBA / Transpose Each Row To Return One Single Column

May 11, 2012

What macro would convert this:

Excel 2007AB112234Sheet2

To this:

Excel 2007A51627384Sheet2

I need a macro please that works with a table of any size.

View 6 Replies View Related

Transpose Each Row Every 5 Rows (of 15 Cells) Into One Column

Oct 19, 2013

I am using Microsoft Office Excel 2007 and Windows 7. I have a lot of data (1-4 thousands of rows) where each column represents a year for 15 years. Every set of 5 rows are different data for a company and the values for every year of each data are under the representative column.

I want to transpose the values for every year, of one data at a time, into one column. Then do the same for the next data.

For Example I have something like this:

1998
1999
2000
2001

[Code]....

If there is a way to do this for one data (i.e. data1) it will be easy to do it for the other 4. I also know how to create a macro from the Developer Tab if it is easier to create a vba code. A formula of course is ok too. I tried for hours to find a way to do this but I couldn't...

View 9 Replies View Related

Convert Or Transpose One Column Into Many Rows

Nov 15, 2006

I have a huge list, all in one column:

A1 1. Aarvark Inn
A2 Region: 3
A3 Unit: B
A4 2. Avalon Home
A5 Region: 6
A6 Unit: A

I want to make it so that every three items becomes a row. So that my data is like this, with the number and name being column A, the Region being column B, and the Unit being column C in the worksheet.

1. Aarvark Inn Region: 3 Unit: B
2. Avalon Home Region: 6 Unit: A

View 9 Replies View Related

Transpose Rows At Every Occurence Of A Value In Column

Apr 8, 2008

Transpose rows at every occurence of a value in column ...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved