I have a spreadsheet that has many columns I need them to be stacked into rows to import them into a publishing program but I don't know how to accomplish this automatically. right now all I know how to do is insert rows and drag and drop the info which is very time consuming.
I have attached a sample of the sheet. A2 through A12 is how I would like all of the data to appear. However A14 through G14 is how it looks now.
excel to convert the data from columns into rows as mentioned below
current excel data is
deptemp1salemp2sal2emp3sal3
HRAbc123bcv231sjn987
ITsddds9876ds999dsb123
Expected data
Deptempsal
HRAbc123
bcv231
sjn987
ITsdds9876
ds999
dsb123
There are close to 10000 records like this is there any formula/function to change this data.
I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?
Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
BLANK ROW
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe
I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?
So
1
2
3
4
5
6
7
8
9
ETC
ETC
becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....
How can I convert a table from rows to columns as shown below??? Thanks for the feedback...
Current file with:
Unit_no work_order_date
1234 1/1/2005
1234 6/10/2006
1234 10/1/2007
4321 2/3/2004
4321 5/12/2005
Change to:
Unit_no work_order_date work_order_date2
1234 1/1/2005 6/10/2006
1234 6/10/2006 10/1/2007
4321 2/3/2004 5/12/2005
Will be working with data to sum data between the two work order dates...
I am trying to convert
Excel 2010
A
B
C
D
[Code].....
I am working on putting together a very large spreadsheet covering multiple data sets over multiple states/years. I am trying to convert the data that I have in one spreadsheet (that is arranged like the example below) and make it so that I can paste the data into another spreadsheet as one single row: i.e, 1,651 would follow in the column to the right in the same row as 6.4 and so on. Right now I am having to copy and paste row by row and it is going to take me years.
3,484
2,179
62.5
2,053
58.9
126
5.8
5.2
6.4
[Code] .......
I have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.
The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.
Data1,Data2,Data3,Data4...................Data1000,Data1001,Data1002 etc...
I have tried:
Sub Split_Delimeter()
Dim Words() As String
Words = Split(Cells(2, 4), ",")
MsgBox "String contains " & (UBound(Words) + 1) & " words"
End Sub
I have a set of letters in a single row DISATAGFOORCBORDQFINFCOBBKIEHDSMCNRSKARDFCJSLCQCECFVSBCBOYKPVYKC. I want to distibute them across 5 columns such that each letter occupies one cell. I might want to specify a different column width later on so the solution should be flexible.
View 5 Replies View RelatedMy problem: I have downloaded national economic data from IMF with several countries for several years. I need several variables and must extract them individually so I a need a clever solution to my problem.
I currently have data that is sorted like below. The "-" indicates a numerical gdp value for the given country for the given year.;
(FYI I could not get the "-" to align beneath 2001-2004 and year respectively.)
Country 2001 2002 2003 2004
x - - - -
y - - - -
z - - - -
The identifier here is country, and the data is as such structured as cross sectional data, with each yearly account of gdp being a variable. Now, I need this data as panel data, organized as below:
Country year gdp
x 2001 -
x 2002 -
x 2003 -
x 2004 -
I sort of say, need to "flip it". As I have 180 countries and 11 years, and need to do this with 2-3 variables, I need either a formula or a VBA to run.
I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.
Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6
Ideally the row will look like.
Example - Sheet "Status WS"
Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date
There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.
I have a excel spreadsheet that is setup as follow:Johnson, Ashley0916BF00001U9/16/2009Test1POSITIVE0916BF00002R9/16/2009Test1POSITIVE0916BF00001U9/16/2009Test2NP0916BF00002R9/16/2009Test2NP0916BF00001U9/16/2009Test3NP0916BF00002R9/16/2009Test3NPBlack, Tom0517BF00012R5/17/2009Test1POSITIVE0527BF00003R5/27/2009Test1NEGATIVE0601BF00009R6/1/2009Test1NEGATIVE0517BF00012R5/17/2009Test2NP0527BF00003R5/27/2009Test2NEGATIVE0601BF00009R6/1/2009Test2NP0517BF00012R5/17/2009Test3NP0527BF00003R5/27/2009Test3NP0601BF00009R6/1/2009Test3NP
First column is in goups: First row of each group contains customer's name then followed by test codes. Test codes are usually repeated three times per customer but they have different names (listed in third column as Test1, Test2 and Test3) and different results (listed in 4th column as POSITIVE, NEGATIVE or NP).
What I want to do is to convert this data into a way that each row has One customer name and the columns in front of each customer lists Tests results and dates. If a customer has multiple test codes, then one row to be created for each test code.
Customer NameTest 1 DateTest 1 ResultTest2 DateTest2 ResultTest3 DateTest3 ResultJohnson, Ashley9/16/2009POSITIVE9/16/2009NP9/16/2009NPJohnson, Ashley9/16/2009POSITIVE9/16/2009NP9/16/2009NPBlack, Tom5/17/2009POSITIVE5/17/2009NP5/17/2009NPBlack, Tom5/27/2009NEGATIVE5/27/2009NEGATIVE5/27/2009NPBlack, Tom6/1/2009NEGATIVE6/1/2009NP6/1/2009NP
I have a set of training information in the rows, with dates under it which maps back to names of employee in the left hand side. How do I convert that to Name of Employee Training attended dates expiry date.
View 8 Replies View RelatedI have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:
{=INDEX(INFO!$A$1:$B$300, SMALL(IF($A$1=INFO!$A$1:$A$300, ROW(INFO!$A$1:$A$300)-MIN(ROW(INFO!$A$1:$A$300))+1, ""), ROW(B1)),COLUMN(B1))}
That got me started. I am willing to use a macro. Here is some sample data:
Table 1 has a sample of the info:
IDENTIFIER
CUSTOMER INFO
2000
111111
[Code].....
I have a spreadsheet in one column contains multiple sets of data separated by commas see example 1.
I need convert the data to rows, each set into a new row see example 2.
Example 1: PS-8800-C,PS-8800-D,PS-8800-076,PS-8800-077,PS-8800-077A,PS-8800-078,2"-SC-4057-A-40H-GT
Example 2:
PS-8800-C (Row 1)
PS-8800-D (Row 2)
PS-8800-076 (Row 3)
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.
I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
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 Relatedreformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.
The attached example shows before and after.
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.
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.
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).
View 1 Replies View RelatedI 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.
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?
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].....
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.
View 2 Replies View RelatedI 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:..............
View 3 Replies View RelatedI have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.
The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.
If the 1st digit in the Category is a 1 = Water.
If the 1st digit in the Category is a 2 = Waste.
There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows
I have data in this format
SPID
Core
Category
Service Category
100000000151
100000000
151
1
[code].....
and I need to convert it into this format.
Core spid
Water
Waste
100000020
100000020101
100000020251
I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows