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

[Code]....

View 1 Replies


ADVERTISEMENT

VB - Transpose Variable Number Of Columns Into Inserted Rows

Jul 23, 2012

I have a sheet with a company name in column A, and a list of comma separated values in column B. I can easily convert column B to multiple columns with the Text to Columns function, but then I'd like to insert each individual value in a new row in column B.

So, at the moment I have data that looks like this:

Company A
Value 1, Value 2, Value 3, Value 4, Value 5

Company B

Company C
Value 1, Value 2, Value 3

[Code].....

View 6 Replies View Related

Transpose (switch) Rows Into Rows And Columns Into Columns?

Jul 28, 2013

i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).

View 14 Replies View Related

Transpose From Rows To Columns

Jun 10, 2014

A code to transpose from rows to columns.

But why doesn't it transpose like the sheet 'How it should be' but all in one row?

HTML Code: 

Sub MG10Jun34()
Dim Rng As Range
Dim Dn As Range
Dim c As Long
Application.ScreenUpdating = False

[Code] .......

View 2 Replies View Related

Transpose Rows To Columns?

Sep 26, 2013

I have 19 rows of data spanning 184 columns, and I'm attempting to transpose them to be 19 columns and 184 rows, which would normally be easy. The problem is I need to be able to transpose the numbers while still keeping the formulas on a separate worksheet linking/connecting to the correct cells. I almost need to be able to cut/special paste - transpose, but that's not possible... I don't think.

View 3 Replies View Related

Transpose Columns To Rows

Nov 5, 2011

I have some sales history data for products which I need in one row. Each product has 4 rows x 12 columns (months) of data under each other. I need the 48 months of data in one row per product. A sample of the type of data I have is below (product A) along with the expected result. In the real data there would be many products not just one as per my example.

Sheet1 *ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW1
Sample Data

[Code] .......

View 6 Replies View Related

Transpose Columns Into Rows

May 17, 2013

how transpose my 4 Colunas to 1 Row

Before:

Data1
Data2
Data3
Data4

[Code]...

View 7 Replies View Related

Transpose From Rows To Columns

Dec 18, 2007

how do i transpose from rows to columns
i have 545 names with addresses
Sheet1 GH221name23 adress line124 adress line225 phone26 fax272name28 adress line129 adress line230 phone31 fax323name33 adress line134 adress line235 phone36 fax37 city384name39 fax40 city Excel tables to the web >> Excel Jeanie HTML 4

selecting each block of name+address is tedious --how can i do it in one go
also the number of rows varies in each block -- at places there are 4 rows at other places 3 rows and so on

View 9 Replies View Related

Transpose Rows To Columns

Oct 3, 2006

I have some summary results stated in row format by GL at the row header. I would like the GL to be the column header and the unit to be the row header. I tried using the index function but keep receiving #VALUE! error. Sample attached.

View 3 Replies View Related

Transpose Columns To Rows

Nov 17, 2006

refer to the attached file. I have series of continuous data which is numbered from 1 to 100. They are arranged in vertical order. Due to some application, i need to convert them into another sheet so that they can be viewed horizontally. I used HLOOKUP to obtain the data from sheet 1. Now i have only 17 groups of data and i still have 83 more data to be input in the future. Is there any VBA code that can help me "extend" the equation in sheet2 so that whenever i key in the data in sheet1, the corresponding data will be generated automatically in sheet2?

View 3 Replies View Related

Transpose Columns And Rows

Jun 25, 2007

transposing columns and rows in an Excel worksheet. This data is a table of part numbers requested vs. received over several different days. Currently, the data is sorted by date across the columns, and each date is split into two columns for requested and received. The rows are labeled with the individual part numbers.

I would like to format the data so that the part numbers appear as the column label, and each part number label is split into two columns for requested and received. Also, the date would then signify the date of each value in the table. I have attempted to use the transpose command, but this only gets me half-way there - still struggling with the splitting of the columns. I can drag a simple "= CELL" command, but the existing data is too cumbersome to build this. I thought a macro might be more efficient.

The file attached is a snap-shot of the data, but will display the formatting challenge I have. The worksheet "Given Format" is the current, while the "Desired Format" is the desired.

View 4 Replies View Related

Transpose From Columns To Rows

Aug 8, 2007

I want to transpose the phone number and website over where the name is? I know I can do a copy and paste special and transpose but then I have do it individuallly and I have over thousands rows.I have attached an example.

View 3 Replies View Related

Transpose Multiple Columns Into Rows?

Nov 20, 2013

I have following information. I need to transpose the columns into rows

C
10001

V
1000

V
1001

V
1002

C
10002

V
1001

V
1003

C
10003

V
1001

V
1003

V
1004

The expected result should be like
10001 1000 1001 1002
10002 1001 1003
10003 1001 1003 1004

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

Transpose Values In Columns To Rows

Jun 23, 2014

I am having months JAN TO DEC In sheet 1 in columns A1,B1,C1, and so on

I want to transpose them to Rows in sheet 2 , how do i this using transpose function in vba code.

View 3 Replies View Related

Transpose Duplicate Rows To Columns

Mar 19, 2009

I have Excel 2007 and a two column lexicon in the following format:

word1 ; word_a
word1 ; word_b
word2 ; word_c
word2 ; word_d
word2 ; word_e
word3 ; word_f

would it somehow be possible to transpose it to:

word1 ; word_a ; word_b
word2 ; word_c ; word_d ; word_e
word3 ; word_f

View 3 Replies View Related

Move & Transpose Columns To Rows

Aug 16, 2008

We have to copy from lot of Java webpages

1. Data of single column 3 rows of data to First blank cell in 'C' column (say C6)

2. Then Transpose it across to the same row into 3 columns (from same C6 to d6 & e6)

Here I tried some VBA but needs how to put it across....

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

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

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.

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

[Code]....

View 7 Replies View Related

TRANSPOSE Rows To Columns - Account Types

Apr 23, 2014

I have a huge file with names, addresses and account types & numbers. However, there's a separate row for each account type instead of listing the person's name once and indicating all of their account types on that row as well in separate columns. I've seen several post regarding different Macro codes to use; however, none work for my project since I have several columns that need to be compared beginning on A2:K2 to A24628:K24628. I have attached a small example for your review.

Transpose Needed.xlsx

View 2 Replies View Related

Rearrange Table - Transpose Rows To Columns

Nov 2, 2007

I have problems to write a macro to rearrange the table from attachment file sheet1 to sheet2. I have many files that need to transpose rows to columns and with continually years, month, day, and the rest parameters. some to the files many content more then 20 years data.

View 5 Replies View Related

Stuck Moving Columns To Rows - Transpose

Nov 2, 2011

I have following data on spreadsheet, which I need to import into other place but I would need to reorganise rows and columns first.

Product1-Sep2-Sep3-Sep4-Sep
SKU1112345
SKU22212131415
SKU3339876
SKU4444444

Basically I would like to have this data in 3 columns like this:

DateSKUOrders
1-SepSKU1112
1-SepSKU22212
1-SepSKU3339
1-SepSKU4444
2-SepSKU1113
2-SepSKU22213
2-SepSKU3338
2-SepSKU4444
3-SepSKU1114
3-SepSKU22214
3-SepSKU3337
3-SepSKU4444
4-SepSKU1115
4-SepSKU22215
4-SepSKU3336
4-SepSKU4444

I tried transpose etc. and not able to figure it out. Is this something that can only be accomplished with VBA?

View 2 Replies View Related

Transpose Horizontal Rows To Vertical Columns

Oct 13, 2008

Acct #11019145036954564872011019125101521103192103103904205851103192415102


The file is broken down by revenue codes on the first row of the account and then units on the second row. (450= Revenue Code and 1=Units associated with that revenue code) Then a new account number starts and it is revenue codes on the first first row and units on the second row.

I want the file to look like this:

Acct #Rev CodesUnits1101914501110191390311019168021101917203110191110411031939431103193921511031954510110319648

View 11 Replies View Related

Transpose Columns To Rows Based On Titles

Jul 9, 2007

I have a set of data which in one column, includes 500 countries, each followed by a list of 43 entries. I want to transpose the data into rows, so that the countries are all in one column, and the 43 entries are in rows as well..

View 7 Replies View Related

Layout/Transpose Data Rows Into Columns

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

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

Rows To Columns Not Using Paste Special Or Transpose Function

Jul 10, 2012

I need a formula to transpose rows to columns of a large table. The transpose array formula is not working for me. I know there is a column and row function formula to do this, but I can't remember it.

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

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







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