Transpose Data From Rows To Columns, Export To A Tab Delimited File
Mar 12, 2003
I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.
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:
GA-AG-00010-A-2013 Apache Hunting Club
709
36
GA-AG-00020-A-2013
Tiger Branch Hunt Club
1596
71, 72
GA-AG-00030-A-2013
Big "O" Hunting Club
3058
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.
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
I'm trying to accomplish is to take an Excel file that contains one column of data consisting of up the maximum number of rows of data (numbers formatted as text? "000000000") and export the data to a text (.txt) file 1000 rows at a time. I would also like the code to allow me to name the .txt files in succession, for example, Pg01, Pg02, Pg03, etc.
The files are going to be used to query a system that will only accept text input 1000 items at a time.
I run the following code to ensure the data is formatted consistently:
Sub a_VerifyDataForInput() ' Start at Cell A1 Range("A1").Select ' Select Column A Columns("A:A").Select ' Format data in Column A Selection.NumberFormat = "000000000" ' Replace all "|" (whatever you call this thing ... pipe???) Selection.Replace What:="|", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False ' Replace all "-" (dashes) Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ ........................
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...
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.
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:
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
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...
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
I am working with 300k records and would like to export to statistics software that accepts special character delimited. I would like to use the character | for the delimitaed process because looking through the excel file i see there are commas and extra tabs in some cells so using those characters will screw up my data.
I need to summarize some data from multiple files but I can't seem to have the user select the file and import it.
It's a ERA(electronic remittance advice) 835 file that will import with the "Import Text File" routine. I've added the "GetOpenFileName" to it but apparently have not appropriately adjusted my "QueryTable.Add" routine.
I removed the .Refresh Backgroundquery:=False line as this was causing an error and this not SQL data.
The desired files have names like this: C123456.835.EDIPROCESSERPROCEEDED
My simpleton code reads:
Sub IMPORT_835() ' IMPORT_835 Macro Dim InputFile As Variant
InputFile = Application.GetOpenFilename(Title:="Choose your file", _ FileFilter:="All Files (*.*), *.*")
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).
I have a software package that requires serial number effectively data to be entered in a particular format.
As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic.
The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,
The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.
I would like the data to end up in two separate columns as shown in example 2.
Example 1 (Starting format) * indicates space - indicates a range, this needs to be separated into two separate columns
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.
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
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
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.
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?
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.
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.
I have a quoting application for damaged vehicles, this application is able to export all quotes and information relating to quotes into an excel sheet. the export file arranges the jobs in rows with each value in a separate cell i.e.
Job #|| Name || number || Parts $ || Total $ 1453 || Mike K || 1234567 || 123.00 || 222.00 1533 || John C || 4353456 || 234.00 || 2342.00
Etc
Each job is listed on a separate row as it exports the records from an access database. once the information is exported I need to open another workbook and type in a job number into a cell and have excel automatically reference the relevant job and information relating to that job (all info in the same row) and fill in all the details from the other workbook into the new workbook. Example: the exported file contains 200 jobs, of those 200 i require 20 to view for one week. I have the job numbers of the jobs which I need to get information for. i open up another workbook and type in the 20 job numbers in separate rows and excel fills in the rest by referencing the other worksheet.