# Transform Rows Into Columns?

Jan 20, 2003

I have a spreadsheet which has data in the format of

Name
Address
City
State
Zip
Phone

This appears in the form of rows. How do I make the data in a table meaning,,,

Name Address City State Zip Phone.

ADVERTISEMENT

## Transform 1000 Rows X 50 Columns Table Into 2columns

Mar 16, 2009

Am working with a spreadsheet of about 1000 rows and 50 columns which I need to transform into 2 columns and as many rows as needed. I have attached a simplified example of a 3x3 spreadsheet so you can see what I mean. I have already managed to get my result using Filtering and some copy and paste but I am struggling to now fully automate the solution.

## Transform Thousands Of Columns To Single Row?

Jan 7, 2014

how do I transform thousands of groups of columns (each group has 6 columns) into A Single row (placed into another sheet) using Macro?

Since my data is large, so efficiency is also important.

## Transform Column A To Rows

Jan 24, 2009

I need a macro that will transform numbers in Column A to rows. i.e.
The (6) astericks will determine the start of the next row.
Transformed to:
******
0
-23
700
-12
701
0
698
12
699
******
100
-50
670
-25
688
0
600
12
700

Transformed to:
0>>> -23 -12 0 12
>>>> 700 701 698 699
100>>-50 -25 0 12
>>>>>670 688 600 700

## Transform 12,50 In 12.50

Feb 17, 2010

In a workbook some of the numbers I have to use are in format: 12,50. How can I modify the formula so it transforms it to 12.50 (so that excel knows this is a number)? I am getting the values with:

## Transform Text To Number?

Mar 13, 2014

I need to transform many text (that Looks like numbers) in to numbers. The thing is that, there is an option to search for errors in the "formulas" menu, but I want a code in vba that I can run.

so in conclusion the thing is:

1. look for text that "looks like" numbers

2. turn them into numbers

## Transform-column-into-a-row Macro

Jun 10, 2008

there is a ONE column containing data. it looks like 7 rows of content, 2 blanks, 7 rows of contant, 3 blanks etc. there can be more than 1500 rows in this document.

i am looking for a macro which would copy these 7 VERTICAL rows in 1 column and transform them into a NEW HORIZONTAL ROW IN 7 COLUMNS.

step by step it would be like:
- copy 7 rows in 1 column
- transform it into 1 row, 7 colums and put it ONE ROW ABOVE THE ORIGINAL
- delete original 7 rows in 1 column

i did macro by recording the whole action but it works only for first repeat. it does not work then because the macro keeps the row number and rewrites the content. i would need a macro which works independently of the row number.

## Transform / Replace Command..

Apr 23, 2009

Is there a ATransform or replace command in Excel. i have a value of 04/23/09 which is a date and would like to replace all "/" with "-' thereby making the value 04-23-09. I could examine each character value within a cell but this would just make a long coding and would like to know if there's a shorter way to do this?.

## Transform One Spreadsheet Data Into Another

May 30, 2014

I have a spreadsheet of multiple entries per person. I need to calculate the total per person and have only one entry per person of different character variables within one column. For example:

Jon Smith Fair

Jon Smith Assignment

Jon Smith Fair

Nancy Drew Info

Nancy Drew Info

Rachael Smith Notes

Turned into this:

Name
Fair
Assignment
Info
Notes

Jon Smith
2
1
0
0

[Code] ........

I can already calculate a 1 or 0 for each row with =SUM(IF(A2:A3442=A2, IF(D2="Fair"))). Which creates a 1 or 0 in each row labeled for each entry but I do not know how to create a new spreadsheet with just one entry per person with the totals for each column.

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

## Transform Text String Into Formula

Aug 23, 2013

How can I transform a text string into a formula. ='F1'!\$C\$2

This appears as a text and I want to convert it to a formula exactly like it's written. I made this formula like this because I need to get the value from 1000 sheets. So I use the formula to change the sheet number automatically.

But now I can not convert the text into a real formula.

I know that I could do it with a macro but I prefer to do it without using a macro.

## Transform Table Data With A Formula

Mar 21, 2014

I have a table with data and I want to transform the data in table 1 to the format in table 2 with a formula. I want to change the table format since I have data with 12 000 rows in table 2 format. I haven't been able to find any formula that works, so I will try to explain my problem

Table 1: How the data looks like

Account number
Account Name
Description
2013-01
2013-02
2013-03
2013-04
2013-05

4010
Bank
Sales
0
10
0
5
0

[Code] ........

Table 2: How I want the data to be transformed

Account number
Account Name
Description
Month
Amount

4010
Bank
Sales
2013-02
10

[Code] ........

1. The same accounts are used several times, but with different description or months.
2. All "0" values are eliminated in table 2.

My first thought was to use a pivot table, but I can't use it on sheet 1 since each month become a separate value.

Maybe there are some setting in pivot tables that makes it work, but I would prefer a formula to solve the problem.

## Transform Series Of 7 Numbers In 0 - 4 Scores

Apr 5, 2014

I have a series of raws with 7 numbers in each raw; sums may vary a lot from raw to raw, and also differences between one number and the other in a same raw.

Now, i need to obtain a much simpler distribution of scores, so reducing differences.

I would need to transform all numbers into a 0-4 score distribution, according to differences between numbers in a same raw. It's like a kind of rank score.

I want to obtain in each raw:

- 1-2 numbers with score = 4 (if three or more numbers have similar values: no 4 scores)
- a maximum of 3-4 numbers with score 2-3 (according to differences between these numbers but also also according to the 4 scores already assigned)
- all other scores = 1 if the numbers are >0
- all other scores = 0 if the numbers are 0

example: |14|23|3|0|45|0|10| => |2|3|1|0|4|0|2|

Would it be possible to do with Excel?

## Transform One File From 2000 To 2007

Jan 8, 2010

I would like to know if there's an easy way to transform an excel file from 2000 version to 2007 version or if I have to redo completely my file

## Transform Dates Format And Filter

May 24, 2007

I have a sheet with data imported of another software. In the column "G" are all the dates, and the problem is how can i change the format, because, some of them are "mm/dd/yyyy" and the rest are "mm/dd/yyyy".

Is there a way to change it to the same format? maybe there are some dates like "11/02/2007" that are "02/11/2007" (i don't know how change it with code), and others that the difference between formats is logically clear. I want to get the format "dd/mm/yyyy".

After this i want to apply a filter between two dates, for example, (in dd/mm/yyyy) from01/05/2007 to 30/08/2007.

I've tried different ways, but i'm not able to transform the format of the dates to the same format, although i've read your help and other posts that talk about this.

## Transform Cells With Visible Values (formula To Value)

Mar 11, 2014

I want my macro to transform the cells whom values are visible into pure value no formula "behind them". I have simple multiplication formulas in those cells. If The result of a multiplication is 0 and I format that cells to not show me that 0 with "0.000;-0.000;;@" the macro still deletes the formula from that cell.

[Code] .....

## Macro To Transform Formulas Into Values Across Multiple Tabs

Feb 7, 2014

I have never written a macro and when I record one I usually have trouble with the relative references.

I am trying to write a macro to transform formulas into values every month.

I want to transform formulas:

- across a range of tabs: each tab is exactly alike and is named page-1 to page 25
- on a different column every month (same column across all tabs)
- on the same rows: L168 to L227 and L266 to L277 (same rows across all tabs)

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

## Excel 2007 :: Modify / Transform Daily To Weekly Or Monthly Data

Nov 14, 2013

I have an excel 2007 script that downloads daily stock data and prices. I do analysis and graph the data.

I would like to keep the download the same, but modify my analysis so as to obtain weekly and/or monthly data.

I am sure this is commonly done, and is not rocket science, but so far I am baffled as to the procedure / algorithm to do this.

## Moving Data From Rows To Columns And Delete Repeated Rows

Apr 16, 2014

removing duplicate rows and move other data frm rows to columns.xlsx.

I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.

see the desired result tab in the sheet to get an idea of what I am looking for as the end result.

Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.

## Getting Rows To Columns Macro Skipping Blank Rows

Dec 28, 2011

I have this in Column A, with about 120 entries:

Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1

Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2

Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3

I want this:

Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3

all the way down.

I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.

## Move Rows Of Data From Several Columns But Not All Columns?

Dec 18, 2013

Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).

## Turning 64 Rows And 18 Columns Into One Row With 1152 Columns

Aug 10, 2008

I have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).

In other words, my individual data files look something like this:

Participant 1 (title of data file)

Trial/var1/var2/var3
1 55 43 65
2 54 43 56
3 33 23 56

and I want to compile all the data into a single file that looks like this:
Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ...
1 55 43 65 54 43
2 . . .

I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.

## Aligning Rows Of Different Columns With Unequal Rows?

Feb 11, 2013

How to align two columns that sometimes have rows that are unequal in number. The data is sourced from a web page that has a table layout and the table lays out the one to many values this way in the table. My data looks like this:

Table ...Term
t1 ..... term1
t2 ......term2
..........term2.a
..........term2.b
..........term2.c
t3 ......term3
..........term3.b

The editor removes leading spaces. In the column Table there are only three rows but in column Term there are multiple rows per single row from column Table. So one row t2 for column Table has three rows in column Term. Are these cells merged and can I in a mcro detect if the cells are merged? How is the addressing of the merged cells in a VBA macro?

## Remove Empty Rows Based On Range Of Columns If Columns Are All Empty (no Data) Delete

Oct 24, 2012

Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.

VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range

[Code].....

## Columns Into Rows And Rows Into A Column

Jun 12, 2007

have a large dataset where I want to move some data from rows into columns and other data from columns into rows. I have enclosed an example. It is indeed only an example as the original has many more rows and colunms. What I want to do is to move the the years (columns) into rows and move Product (row) into columns. The example shows how it currently looks and how I want it to look.

## Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)

Jan 15, 2010

I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............

## Rows To Columns Or Columns To Row

Nov 13, 2006

I wrote a short VBA to change known data from rows to column and columns to row.

Data is like this:

Hello Hello Hello
Hello Hello Hello

I can chang it to Hello .... Hello
or
Hello..
.
.
.Hello
I need to figure out a way to do this when data is not fixed - ie:

Hello Hello Hello Hello Hello
Hello Hello Hello
Hello Hello Hello
Hello

or

Hello Hello Hello Hello Hello Hello
Hello Hello Hello
Hello Hello Hello

Can you offer suggestions on what commands I can use to find the begining and ending of data, so that no matter what format it is in I can change it to one row or one column?

## Certain Columns To Rows

Dec 14, 2008

I have 20,000 columns of data that I need turned into 4 rows.

I know about copying and paste special (transpose), however, the data is in groups of 4 columns

Example:

1. Name
2. Street
3. City, State
4. Number
5. Name
6. Street
7. City, State
8. Number

and I need it to look like
Name Street City, State Number

So is there anyway that I can make it turn columns into rows by groups of 4?

## Rows 2 Columns

Feb 13, 2008

I have huge data like below.

Parent Site:A
Child Site:A
Child Site:B
Child Site:C
Parent Site:B
Child Site:A
Child Site:B
Child Site:C
Child Site:D
Parent Site:z1
Parent Site:1

I am looking for a macro which can convert above data to columns as per below.

******** ******************** ************************************************************************>Microsoft Excel - r2c.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1Parent SiteChild SiteChild SiteChild SiteChild SiteChild Site2AABC  3BABCD 4Z1     5112510156XA1Z33 720A    Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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