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:


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


Transpose Multiple Rows Of Data To Single Row?

Jul 24, 2014

I have a set of data that I need to change the "layout" of. I've had similar situations before, but this one is just killing me. Basically, my data is for item pricing. It is represented as

Item, QTY, Price
Item D,Qty1,P1
Item D,Q2,P2
Item D,Q3,P3
Item D,Q4,P4

However, I need it in the following format:

Item DQ1Q2Q3Q4P1P2P3P4

As a note: there is a maximum of 4 Price/QTY breaks, so the script can be hard coded for that. When I tried this, I had it looking at the Item column, finding out how many breaks there are for a specific item and then doing a loop to extract the qty and price to a single row in the format shown above. It worked for the first 2 items, but then the loop got throw off. I will see if I can reproduce the code for that.

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

Transpose Column Range Into Single Cell With Delimiter

May 14, 2008

I want to add a Punctation mark (comma), like this: ,
and also want to add punctation mark (colon), like this: :

In this moment I have below macro:

Public Sub CombineCells
Dim Combined As String
Combined = ""

For Each Cell In Selection
Combined = Combined & Cell.Value & ":"
Next Cell

Selection.Cells(1, 4).Value = Combined
End Sub

the effect shoud be like this:


after transposed:
1-C:D,E:F Etc.

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

1801524, 25
1804214, 16, 18



View 8 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 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:



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

Transpose / Match / Array Column To Rows

Feb 27, 2014

I've tried using a Pivot Table, a Match Array V-Look Up formula I found on here (and can't seem to find anymore) that I altered to fit my cell needs, and even implemented RP-Excel.

This is an example of what I have:



This is an example of what I'm trying to do:



Additional challenges:

The actual workbook is over 4,000 rows long with a lot of various other information in neighboring columns.

The dates represent each time a specific box was filled. Some boxes only filled once, some up to 14 times. I only want up to the first five fills.

Able to try any of the following, or combinations of: Formulas, Pivot Tables, VBA, RP Excel

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

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

Transpose Column Into Unique Rows With Comma Delimited Values

Nov 4, 2013

I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was Need to transpose multiple comma separated values

I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:

Apache Hunting Club




Tiger Branch Hunt Club


71, 72


Big "O" Hunting Club


59, 64, 65, 75, 79, 84

I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.

Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

[Code] ......

How to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.

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

Transpose Multiple Columns For Single Row

Feb 12, 2014



[Code] .....

View 10 Replies View Related

Single Column -> Multiple Rows

Mar 5, 2014

I have a single column of about 1500 points, and I need them to be separated into many rows of 20 numbers.

I have multiple issues:

1) The middle number of each row (10th and 11th) need to be repeated. So a transpose formula can not work all the way through the 20 numbers. Unless I transpose the first ten, then tranpose the second ten. Is there an easier way around this ?

2) The bigger issue is, I would like to write a formula that can manipulate the date to enter in the correct cell.

To illustrate I will use a set of ten numbers:





View 9 Replies View Related

Transposing Multiple Rows To One Single Column

Apr 12, 2005

I frequently have to work with tables set up like this:

A1 A2 A3
B1 B2 B3
C1 C2 C3

All I would like to do is to transpose them into one single column:




View 5 Replies View Related

Data From Rows And Column To One Single Line

Nov 12, 2009

I have to worksheets: Sheet1, Sheet2. In sheet 1, I have some data like this (in first col is 1, second col is a, third col is c etc. ):

1 a b c d
2 e f g h

I need in sheet 2 in range A1(only in this range one big text), something like this: a,c,e,g ........ ( row1-col2,row1-col4,row1-col2,row1-col4 ......) ( separated by ","). The number of rows from sheet 1 is variable. The dataf a,b,c,d,e,f,g,h ... is some random text.

View 4 Replies View Related

Transposing Multiple Rows To Single Column

Feb 16, 2012

I want to transpose multiple rows to single column and I'm dealing with huge data.

For example, I am working with data like this

A1 A2 A3
B1 B2 B3
C1 C2 C3

and I have to transpose to


I'm using following vb code:

Sub ToOneColumn()
Dim i As Long, k As Long, j As Integer
Application.ScreenUpdating = False

[Code] ........

The script terminate without completing the job and shows error as "runtime error '1004': application -defined or object defined error".

View 8 Replies View Related

Delete Certain Range Of Rows In Single Column?

Jun 10, 2014

I need to delete a certain range of Rows in a single column

View 3 Replies View Related

Converting Different Length Rows Into Single Column

Jul 29, 2014

I have a table in excel in which every line has as many values as the days of the month (e.g. 31 values first row, 28 the second..) and it goes from January 1948 to July 2014. So it ends up having around 800 rows.

I need to put all of those into a column, but it gets hard because the rows have different lengths. I have seen in this forum some solutions that apply well when the table is regular, but I'm stuck in trying to get a solution for when it's not. I have tried to record a macro using TRANSPOSE, but then I cannot change the cells into this formula in the editor in order to create a loop.

I also have to consider leap years.

View 4 Replies View Related

Data From Multiple Rows Into Single Column

Oct 19, 2007

I am trying to take data from multiple rows and columns (matrix) and reformat it all to fit in a single column.

It would be great if I could select the cells I want to reformat into the single column as the number of rows and columns containing the original data is not always the same (ie 20x20, 21x35, 56x200) etc...

View 9 Replies View Related

Transpose Data In Single Cell From Horizontal To Vertical

Oct 28, 2011

Currently we are transposing data in multiple cells from horizontal to vertical & vice versa.

But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.

Is there any VBA function or public function to perform the this action?



A 1Dog; Lion; Parrot; Bee; Snail


A 7Dog8Lion9Parrot10Bee


Like wise i will have to do the same action for the following

A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7

View 5 Replies View Related

Check Whether Transpose Ranges Are Equal In Single Cell

Apr 30, 2008

How a single-cell formula to check that 2 transpose arrays are equal.

For example, A1:A5 are {1,2,3,4,5}


B3:B8 are {1,2,3,4,5}

Is there an array formula in C3 for example, that will check (i.e. say TRUE) if corresponding ranges are true i.e. check in this cell that A1=B3, A2=B4,...A5=B8.

View 9 Replies View Related

Consolidate Rows Of Data Based On Single Column?

Aug 16, 2014

I have thousands and thousands of rows of data which look like the spreadsheet screen shot shown in this image:


I want to consolidate them so that they look like the spreadsheet screen shot shown in this image:


How to insert it into a module in the VBA editor.

View 3 Replies View Related

Split Single Column Into Multiple Columns And Rows

Aug 20, 2008

I have 300 rows worth of data that looks similar to this, all organized in one column:

John Q. Smith
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555

As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.

John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555

Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.

View 4 Replies View Related

Copy Multiple Rows Data To Single Column

Oct 29, 2008

transposing and sorting data into multiple columns.

Column 1 for example will have the Parent's Name. Column 2 will have the Children.

If Mark has 3 children, X Y and Z

Jim has two children, A and B

then I want Column 1 to display Mark and Jims Name and column two should display all the children

Here is how I want the data:

Column1 Column 2

Mark X
Mark Y
Mark Z
Jim A
Jim B

Please see attachment.

View 3 Replies View Related

Copy Rows Based On Criteria In Single Column

Jan 30, 2008

I have on sheet1 of my workbook a number of rows of data, from column A through Column G. The Data in column G either contains the text "Yes", or "No".

I need a macro that will look through each row in this sheet, and upon finding a "Yes" in the cell in the column G, will copy that entire row (columns A through G) into a new row in sheet2. Sheet2 currently contains data, so I'd like to move everything down by one row and paste this data at the top (row 6 is the first line of data on this sheet).

I'd then like to go back to sheet 1, and delete the row I've just copied. Then continue though the loop. The loop needs to stop when a blank row is discovered.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved