Reformatting Data Across Columns Down Rows

Reformatting Source Data

Dec 22, 2008

I need to translate my Source Data into a new format as follows: ...

I have a global dataset where column A represents longitude, B represents latitude, C is a spinup value which can be ignored (even deleted) and columns D to DH are runoff values for the years 1901 to 2002. The rows extend down to 6510 (including the column headers, 6509 without). I've been trying to create a reformatted table based on this data, where longitude values are listed along row 1 and latitude values are listed in column A, with the corresponding runoff values for each year correctly repositioned into the spreadsheet according to this new 'format'; there will be 102 grids created (one for each year). The longitude values are currently not ordered and will need to be sorted (low to high) into position along row 1, without causing the runoff data to become unassigned to its associated longitude and latitude coordinates.

The second part is to then perform a linear interpolation of the reformatted data for each year's table so that the data is again reformatted' from 1.5-degrees to a 5-minute spatial resolution. This involves creating tables for each runoff year as before, but this time creating new values via application of a linear interpolation between each adjacent pair of existing values (in both the x and y direction - as the data are to be imagined as being on a geographical grid with coordinates). I've currently been trying to achieve this using Fortran, but have been getting nowhere as I'm completely new to this programming language, so I thought I'd try using Excel as I'm more familiar with this, although not sufficiently so to solve this alone!

View 7 Replies View Related

Reformatting Data In A Table Into Headers

Jan 24, 2009

View 9 Replies View Related

Macro For Reformatting Citrix Data

Jan 9, 2007

I have a report presented in the format such as the one in the attached file.

Current Format.xls

Is there a macro that copies and pastes the data from the original report to a new worksheet with the format such as in the attached file below.

New Format.xls

The reason why i would rather not use Pivot Tables for this purpose is because the pivottable is limiting me from expanding the report to include other looked up data with the AutoFilter option.

View 4 Replies View Related

Reformatting Data - Subtotaling Brings Numbers Down To One Line

Apr 2, 2013

I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....

Pic Attached: Excel problem pic.JPG

View 8 Replies View Related

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.

View 5 Replies View Related

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

View 1 Replies View Related

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].....

View 1 Replies View Related

Reformatting Addresses

Apr 21, 2007

I have a few hundred addresses in excel in the following format

NameAddress1Address2Address3AreaCityPin

Is it possible for me to change that into

Namee
Address1
Address2
Address3
Area
City-Pin

View 10 Replies View Related

Reformatting Numbers

Apr 6, 2009

I have some data which is exported into excel by a package I can't amend. The information comes into excel in the format

00:00:04 (representing hh:mm:ss) but if I look at the formatting of the cell it says the formatting is general. I want to be able to cut and paste this into another spreadsheet and then apply conditional formatting to colour the text if the value is above 00:00:09, but try as I might, I can't! When I cut and paste (even using paste special) it still retains its previous format, and I still can't amend it

Any suggestions?

View 6 Replies View Related

Date Reformatting

Apr 16, 2008

I have several non-consistent/non-sequential columns with supposed dates formatted as:

09.04.08 (day/month/year). I'd like a macro where a popup window will allow me to type in columns to execute on (ie. a,g,m,t) & then it will go through & change the dates to REAL date format 04/09/2008 (month/day/year).

If this easy enuf that any of you gurus could write a few lines?

View 9 Replies View Related

Transpose Or Convert Rows Of Data Into Columns Of Data:

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

Reformatting Matrix Into Single Column?

May 27, 2014

I have a matrix that looks like this:

1
2
3

4
5
6

7
8
9

10
11
12

And would like to rearrange this data to:

1
2
3
4
5
6
7
8
9
10
11
12

In actuality it's a 110000 x 3 matrix that should become a 330000 x 1 column.

View 6 Replies View Related

Checking For Blank Cell When Reformatting

Sep 29, 2006

I want to reformat some data, arranged as follows, and ignore blank cells

Date1 Time1 blank Time2
Date2 blank Time3

to
Date1 Time2
Date1 Time2
Date2 Time3

Code being used, currently writes out lines where a Date exists, but Time cell is blank, what do I need to modify in the code below, to ensure blank times are not written out

Sub test()
counter = 2
For i = 1 To Range("A65536").End(xlUp).Row
For j = 2 To Cells(i, 256).End(xlToLeft).Column
Sheets("Sheet2").Cells(counter, 1).Value = Cells(i, 1).Value
Sheets("Sheet2").Cells(counter, 2).Value = Cells(i, j).Value
counter = counter + 1
Next j
Next i
End Sub

View 3 Replies View Related

Rows To Columns With A1 Data

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

Transposing Columns Of Data Into Rows Of Data

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

Using Sumproduct With Data In Rows And Columns

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

Convert Columns To Rows With DATA

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

Data In Columns To Rows (not Transpose)

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

Getting Data From Columns To Rows Without Using Transpose

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

Convert Several Rows Of Data To 2 Columns?

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

Transposing Data From Rows To Columns

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

Moving Data From Columns To Rows

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

Extract Data From Rows Into Columns

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

Convert Data From Columns To Rows

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

Transposing 25,000 Rows Of Data Into Columns

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

Only Want 6 Of 7 Columns Of Data Rows To Scroll?

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

How To Rearrange Data Into Columns If It Is In Rows

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

Converting Data Into Rows And Columns

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