Changing Data From Rows To Columns
Jul 28, 2006
way to take a long list of the following data and change it from rows to columns?
First name Last name
Address
City, State Zip code
First name Last name
Address
City, State Zip code
That is how the data is currently displayed, spaces inbetween as shown but, long list of names and addresses. There is also an occasional 4th line usually for an address. My instructions are to put data in the following format:
First name Last name,address,city,state,zip code,
View 9 Replies
ADVERTISEMENT
Jan 10, 2013
I am trying to create an excel file for use in a mail merge into Word.
Currently A-Z go along the top view, while 1-100 go along the left side. My understanding is that row (1) will hold all of the mail merge codes (ie, CUSTOMERADDRESS) and that each row below row (1) will be where I type in the data.
if I could enter the data going top to bottom instead of left to right in the sheet. To do that I need to change the view so that (1) A-Z is going along the left column instead of the top row.
View 1 Replies
View Related
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
Aug 31, 2006
I'm working in a workbook with several sheets, the first two collums of each sheet are =to the first sheet. some times I need to insert a line, but when I did that, I need to type again all formulas or drag the previous ones, and format again the cells. is there another way to do this?
View 4 Replies
View Related
Mar 4, 2014
I have 1 sheet called Setup which users enter their character name, their class, and their race, additionally the entire workbook manages data for up to 9 individual characters.
On another sheet called C|R|E, I have tasks listed that are based on each class type and each race type that are available. Currently there are nine classes and four races. Currently on this sheet each class and each race has its own individual data lists and such, and formulas are done in a way that user inputted data can be entered anywhere, but wont count unless the class and race match those selected on the Setup sheet. To keep the sheet short in appearance I used command buttons to hide/unhide classes or races not in use by the user.
What I was wondering is (and this could be hard to describe), can data in one column be changed to reflect data based on another column's focus? I will explain in more detail.
Column B contains the task information for each Class and Race. For example Cells B13-B51 for one class, Burglar. Columns D-L is where users enter data to show if the task is completed or not. In this example lets say the users first character listed on the Setup sheet is a Champion. What I want to do is check the Setup sheet Char 1. Then based on what Char 1 is selected as on the Setup Sheet, C|R|E B13-B51 data is changed to reflect it, IF Column D currently has focus. If Column E gets focus then B13-B51 changes based on the class selected on the setup sheet for character 2. and etc.
If this can be done I can remove the 13 different sections and have it down to 2 sections, one for class, one for race. For now I am using check boxes to hide classes and races they are not currently using, but would like to move all classes into one table and all races into one table to they dont have trouble with columns they have to skip over currently.
This is a sample of how I am currently working on the C|R|E sheet, the full code is much much longer since I am using so many command buttons.
View 1 Replies
View Related
May 13, 2013
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies
View Related
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.
View 5 Replies
View Related
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).
View 1 Replies
View Related
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].....
View 1 Replies
View Related
Feb 12, 2009
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)?
View 3 Replies
View Related
Nov 16, 2009
I am trying to sort out some item numbers with their page numbers.
My file is in the following format:
View 6 Replies
View Related
Apr 28, 2009
The title makes it sound simpler than it is. I have 2 columns of data which I need to transpose.
The difficult bit is that Reference Number 1 may have 3 reasons (so therefore 3 rows) that need transposing into 4 columns (reference number, reason1, reason2, reason3)
Is there a way to do this?
I have attached a spreadsheet with the original table and how I need the end result to look.
View 14 Replies
View Related
May 18, 2014
See attached template I am working on. I want to calculate the number of hours worked over specific dates e.g. between 19-25 May 2014 and 26-31 May 2014 for employee "Bird, Ken" - the result is to be placed in cell D19. Tried sumproduct but without success.
celeste template.xlsx‎
View 2 Replies
View Related
Aug 5, 2008
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
View 6 Replies
View Related
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
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
Mar 5, 2013
I am trying to convert some data...
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
View 1 Replies
View Related
Jul 8, 2013
I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.
The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.
I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.
View 7 Replies
View Related
Sep 24, 2009
I have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs
1000001270a1 Blog Street
1000001270bNorwich
1000001270cNorfolk
1000001270eNRx x99
1000001270gThe Manager
1000001270k01603 555 555
1000001270l01603 555 555
1000001270qManager
1000001270xN
1000001270yN
1000002245aMrs Fredilina blogs
1000002270a10 Blog Street
1000002270bNorwich
etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A),
I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
View 6 Replies
View Related
Oct 15, 2009
I'm using Excel 2007 and I need to extract values from rows from a data dump into columns. Here is sample data (this is dummy data, but the format is the same): ...
View 13 Replies
View Related
Dec 2, 2009
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
View 2 Replies
View Related
Dec 17, 2009
I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...
View 14 Replies
View Related
Dec 3, 2012
I have a single sheet that has 6 fixed columns of data in but the number of rows can vary per user per import. Users can actually insert rows as required. The seventh column has my macro buttons present. It looks and works great for about 20 rows (one screen) full. If the number of rows gets to be more than that and the user needs to scroll to modify those lower rows the macro buttons roll off the top and are not visible to access. Is there a way to keep the macro buttons in the same location (column G) and only have the data (columns A-F) scroll when required by the user? This way the macro buttons are available on the rows beyond the first 20 or so for the user to use, instead of having to scroll back up to use them. I've tried using the split and freeze functions but neither worked.
View 5 Replies
View Related
Jan 15, 2013
I have an Excel sheet that is in rows. I want to separate the data into columns. Each sample is separated by a space in the rows. So sample 1 is A1 is the name and B1 is the actual name. A2 is the description and B2 is the description. Each sample is +or- rows. Sample 1 may be 8 rows but sample 2 may be 6 rows. What type of formula or macro do I use to arrange the samples into columns instead of rows so the data will be like A1 name, A2 description, etc... and A2 will be sample 2 name, and B2 will be sample 2 description, etc...
View 7 Replies
View Related
Jan 12, 2010
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
View 9 Replies
View Related
Feb 15, 2010
I have searched and have not been able to find exactly what I need. I currently do this manually and it is taking too much time.
I start with a file (sheet1) that has data in column A for a location.
Column B has a unique 10 number (each location can have between 1 and 10000). Example:
Location,secnumb
1000001,10052950
1000001,10052960
1000002,10052980
1000002,10052990
1000002,10053000
1000002,10053010
1000002,10053020
1000004,10053530
1000004,10053540
1000004,10053550
I need to get this data into the following format for a mail merge:
Location,secnumb1,secnumb2,secnumb3,secnumb4,secnumb5
1000001,10052950,10052960
1000002,10052980,10052990,10053000,10053010,10053020
1000004,10053530,10053540,10053550
I was thinking I would set up a sheet2 with all the unique location codes, and then do a match between the location code on sheet 2 and sheet1 and use an offset function?
But I am way over my head when I work with vba.
View 9 Replies
View Related
Sep 27, 2006
I have train departure times stored in rows for each date as follows:
Mon02-Oct8.399.069.4210.0910.3611.12
Tue03-Oct8.399.069.42
Each time/date is in its own cell.
I have hundreds of entries like this and what I want to do is restructure them as follows (on a new sheet). For each date and time couple I want the date in one cell and the time in the the cell to the right. Also please note that not all days have the same amount of dept. times and this also needs to be considered.
e.g...........................
View 8 Replies
View Related
Oct 7, 2006
I have an excel file with data that was entered in rows instead of columns and I need to rearrange the data into columns. Here's what my data looks like now, with what should be field names in column one and values in column two:
Company Name : Excel Company
First Name : Jack
Last Name : Smith
Phone : 555-661-6674
Email : ozgrid_rocks@excelgods.com
(this continues in a repeating pattern for thousands of rows)
I need the data like this:
Company Name : First Name : Last Name : Phone : Email
Excel Company : Jack : Smith : 555-# : ozgrid_rocks@url
View 5 Replies
View Related
Jan 5, 2008
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows:
Company Name - 2nd row
Address - 3 row.
In this case, I want to combile them as one entry Separated by a comma
& placed in one column
Eg: For 3rd one in sample,
Haifa Marble & Tile
69 Garfield St
Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it
into one column separated by comma.
View 9 Replies
View Related
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:..............
View 3 Replies
View Related
