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.
There are no spaces between entries.
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.
[URL]
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:
{=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)));"")}
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]....
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
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...
i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View RelatedWhen 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.
View 9 Replies View RelatedI 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 (*.*), *.*")
[Code] ........
No data is imported, Why?
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 RelatedI 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
2252*2254*2256*2257*2259*2272*2274-2276*2278*2280*2282*2284*2286-2641*2643-2681*2683-2712*2714-2717*2719*2721*2724*2726*2727*2729* 2733*2735 *2738*2739*2746
Example 2 (Finished format ready to be paste into software package
2252
2254
2256
2257
2259
2272
2274 2276...............................
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] .......
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 RelatedI 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] .......
how transpose my 4 Colunas to 1 Row
Before:
Data1
Data2
Data3
Data4
[Code]...
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.
View 3 Replies View Relatedrefer 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 Relatedtransposing 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.
View 3 Replies View RelatedI 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.
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
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.
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
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....