Reformat Column Data To Rows

Apr 18, 2006

I'm trying to take spreadsheet data in columns and reformat into rows. See attachment. Basically i want to duplicate columns A thru E by row specific and then take columns F thru K in pairs to match up with the duplicated rows corresponding to the specific rows. So every row currently will become 3 rows (A thru E) with Columns F and G (old F & G row 1, H & I row 2, J & K row 3)

View 4 Replies


ADVERTISEMENT

Reformat Text In Single Column To Rows Using Delimiter

Jun 10, 2008

I am trying to convert text to rows but in the same column Take the following example:

Column A:
1-2-3
ab-cde-fg
54-ty-12345
the
waht i am looking is convert it to the following format: (the - is the delimiter)
Column A

1
2
3
ab
cde
fg
54
ty
12345

i have found the following VBA code on those forums but i don't know how to edit it to suit my needs

Sub Txt_To_Rows()
Dim arrText() As String
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Dim j As Integer
Dim x As Integer
Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
j = 2...........................

View 2 Replies View Related

Reformat Single Column Data For PivotTable

Jun 18, 2008

I have a worksheet of data collected from on online list of names and addresses all in one column. I'd like to pivot the data so I can sort it, etc. Unfortunately, the entries are not the same length (meaning some have five rows some have six or seven, etc. I have created an only file so everyone can see the issue.

a href=[url]

View 4 Replies View Related

Macro To Reformat Numbers In A Column

Oct 16, 2008

I have a worksheet that has a column of numbers entered as 1234567. The person that input these numbers should have done this: 123-4-567. Is there a macro that could do this automatically so I don't have to go into 1150 cells and manually change the format of each of them?

View 2 Replies View Related

Reformat Table Layout To Repeat Rows Based On Cell Values

Jun 11, 2008

I can export inventory data from my Point of Sale system that looks like this:

I need to manipulate it to look like this:

To do so I think I need a macro(?) to:

Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.

This would allow me to print labels for every item in my inventory.

View 3 Replies View Related

VBA / Find Column Header And Reformat (date) Values In Columns

Jun 28, 2013

I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).

Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.

Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer

[code]....

View 3 Replies View Related

Move And Reformat Data

May 25, 2009

I copy data from word document to Excel and it will look like this:

Name
Address1
Address2
phone
fax
e-mail
website

I have like 500 sets of this above data.
When I copy & past to Excel, it is all populated into Column A.
But I wish to put Name in Column A
Address1 in Column B
Address2 in Column C
etc.

View 9 Replies View Related

Reformat Downloaded Data

Sep 30, 2006

when i download the data to excel it is downloaded in some format i cannot use.

View 6 Replies View Related

Reformat Citrix Data

Jan 8, 2007

Im currently working with some data output from citrix and am finding that the system generated reports are not very user friendly. The format of the reports is as below (example only contains five products. The real report contains 500+ product IDs).

System Report Format.xls

Can anyone suggest a macro that copies and pastes data from the system generated report to a diff worksheet with a format such as attached:

New Format.xls

The challenge is that each of the product codes in the system generated report have two rows of data (recommended orders & open orders). Will it be possible to have the macro to SUM up the values in the same period of time in the new worksheet under the new format?

Additional Info:

1) Number of product codes changes monthly

View 9 Replies View Related

Macro To Reformat Data

Feb 20, 2007

In Cell A1 I have a company name which I wish to keep in cell B1 I have company address data I wish to keep and in cell B2 I have more address data which I wish to move to cell C1. Once this cells data has been moved I need to delete the two empty rows and then perform the same process on the next chunk of company data which is in the same format as the above and so until the end of the entries - sometimes potentially there could be hundreds of entries. I have attached an example of my problem - in the 'Original' worksheet is data in the form that I recieve and need to re-format. In the 'Fixed' worksheet is data in the format which I would like to have a macro to change it.

View 2 Replies View Related

Reformat Data Layout

May 21, 2007

I have a worksheet that has similar data but some maybe different lengths. What im trying to do is to be able to have them all in line so where the diagram no. is on the worksheet they are all lined up. On the excel sheet attached I want TURN NO in column J to line up with turn no in Column A each time.

View 2 Replies View Related

Reformat Tax Parcel Data For Report

Dec 13, 2013

I need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.

View 2 Replies View Related

Merge Worksheet And Reformat Data?

Apr 25, 2014

I have 447 worksheets in my workbook, I need to consolidate them into one sheet and transpose the data so that it goes into across rather than down. I really only need data in rows 19 to 40 but the data in 29, 30, 31 A to I needs to be in one cell.

View 5 Replies View Related

Macro: Reformat Layout Of Data

Nov 16, 2006

I would like to state how fantastic a resource this forum has been to me as a beginner. So keep up the good work! The issue I have is that my spreadsheet contains one column which shares two types of data: Component Type and Tag Number. What I would like the script to do is:

1. create a new column
2. move the Tag Number data from its current column into the newly created column
3. ensure that the data is still on the same row as it was previously

One thing to bear in mind is that the tag numbers consists of various formats (spaces/no spaces), but always begin and end with brackets - "(XTU U-532-934)". I have included and example of the current state of the data and what I would like to look like after.

View 3 Replies View Related

Reformat Badly Laid Out Data

Dec 1, 2007

I have a sheet that has multiple records of the same recurring format (see attached .xls) from which I must extract data from the same groups of cells in each record (highlighted in yellow for example purposes) and export to a columnar format in another sheet. The only variable with the format is where we get into the amount of people on the plane and thus the line may shift down one or two spaces. I would like to just pull the data for the same person's name from each record as well. What is the easiest way to go about doing this?

View 9 Replies View Related

VBA Code To Copy Data And Reformat It Between Two Workbooks

Jun 16, 2014

I'm trying to write code in Airport1.xlsm to allow me to create a macro to automatically copy data from Airport-Data.xlsm and reformat it to what is needed in Airport1.xlsm.

Basically I need the code to take each airport in column A of Airport-Data and where there is a non-zero value in rows column C to G I need it to clear the appropriate column in Airport1.xlsm and add a 1 to the appropriate box.

I got stuck thinking about the nested For Next Cell in range procedure and how to execute it with a search for the correct row in Airport1.xlsm.

View 14 Replies View Related

Looping Macro To Reformat Data From One Sheet To Another

Mar 17, 2009

I'm using Excel 2003, version 11 and am trying to reformat data from one sheet to another. What I would like to do is copy the rows for each team from the Input sheet (Input tab) and and use them to build rows on the Output sheet (Output tab) for each possible combination of players.

In my attached example, a three person team has three combinations and a five player team has ten combinations and so on. I know this is probably a loop within a loop but I can't seem to get the right combination. Not all teams are full yet so the number of players on a team will vary. I need macro code to make the input sheet look the output sheet.

View 3 Replies View Related

Reformat Table Layout Containing Repeated Data

Sep 7, 2006

Rows 1 to 12 of the attached .jpg file shows the format that I receive from a University. However, in order to check enrolments, etc., I would prefer it to be in the format as shown on rows 15 to 19, that is, one complete student record per row.

As you can see, not all students are studying the same number of subjects and the subject mix is also different.

View 4 Replies View Related

Reformat Data For Pivot Table Source

May 31, 2007

My fixed asset software will not allow me to show multiple individual months of acquisitions. I can export each month. This will create a separate spreadsheet for each month, but the problem is that each spreadsheet may contain the same asset, but additions to that asset.

Spreadsheet 1
....Description............................Dept...................January acquisition
.....Building____________________Plant______________1,000,000

Spreadsheet 2
.....Description..........................Dept...................February acquisition
......Building___________________Plant______________1,000,000
.....Welder____________________Metal_________________5,000

What I want is this

Spreadsheet 3
.....Description.........................Dept..................January Acq..........February Acq
......Building__________________Plant____________1,000,000__________1,000,000
......Welder___________________Metal_________________________________5,000

View 4 Replies View Related

Index / Match Formula That Effectively Reformat Data

May 10, 2013

I need a formula that effectively reformats data.

Original data:

Country
Name
2010
2011
2012

A
John
5
6
7

B
James
3
4
5

Into this format:

Country
Name
Year
Value

A
John
2010
5

[Code] ...........

View 5 Replies View Related

Copying Only Rows With Data In First Column - Skipping Rows With Blank First Column

May 12, 2014

I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.

I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.

View 4 Replies View Related

Reformat Time Data Into Decimal And Remove Excess Characters From Field

Feb 17, 2014

I have obtained the following data which I need to put into a spreadsheet to import into an accounts program. I can't change the way I get the data which is as follow

Hrs worked Rate
05:55:00£30.00/Hour
07:40:00£21.00/Hour
05:45:00£30.00/Hour

What I need to have is 5.92 30.00
7.67 21.00
5.75 30.00 ie the time format in decimal and loose all the unnecessary symbols etc for the rate

View 7 Replies View Related

How To Count A Column If It Matches Data From Another Column In Seperate Rows

Feb 6, 2009

I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.

Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.

View 14 Replies View Related

Transpose Rows Keeping Heading In Column A And Data In Column B

Mar 2, 2011

I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example

ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10

View 7 Replies View Related

Adding Column Minus Rows That Have Data In Another Column

Feb 15, 2007

I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.

Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...

i'm not even sure if this makes sense lol... let me use example

on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6

in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.

View 9 Replies View Related

Rows To Column Data

Dec 11, 2006

Our Excel spreadsheet organizes names, addresses, and the amount of money per account in the following manner:

Column A Column B Column C ....Column X
Row 1:"Account Name" "Name of Company" "Money in Accout", etc.
Row 2: "Address of company"
Row 3: "PO box"
Row 4: "More information or State and Zip Code"
Row 5:"Account Name" "Name of Company" "Money in Accout", etc.
Where the "Name of COmpany", "PO Box", "More information or state and Zip code" are all located in Column B.

We have anywhere from twenty to a thousand or so names organized like this going down a spreadsheet. In addition, the number of rows underneath a particular Company varies.

So, I was wondering if anybody knows if a VBA program could be written where the code would move the cells that are put under the name of the company (i.e., Row 2, Column B; Row 3 Column B) so they'd appear only on row 1? Ideally, this program would know when to start putting the information on, for example, row 1 or row 5 because there is an account name in Column B.

View 9 Replies View Related

Convert Column Data To Rows?

Apr 20, 2014

I student data in the following format

STUD ID, Surname, Forename, Subject
E.g
0001,Smith, Stan,Biology
0001,Smith,Stan,Chemistry
0001,Smith,Stan,Physics

Etc

I would like to display it so instead of there being multiple rows per student there is just 1 row with each subject in a new column

e.g

STUD ID,Surname,Forename, Subject 1, Subject 2, Subject 3

e.g

0001,Smith,Stan, Biology, Chemistry, Physics

Is there any way I can do this quickly for hundreds of students?

View 12 Replies View Related

Save Column Data To Rows

Apr 30, 2014

How to do this ? keep a running record in rows...

A---H I J K
1 4-4 8 2 6
2 8-5 6 1 4
3 2-8 3 2 5
4 6
5

6 5
7 6
8 1
9 4
10

11 8
12 3
13 2
14 5

View 9 Replies View Related

Aligning Rows Of Data So All Of Same Value Is In Same Column?

Apr 23, 2013

I have a data set with raw data from an online survey with >2000 rows of data, with each row varying in width from 2 columns to 4 columns wide of data, located in columns A through D. Each row has the value 2 in it as well as some additional values on either the right or left side (the location of the value 2 varies -- so it's not always in the same column). Picture attached of some example data for clarification:

Picture 1.png

I want to figure out if there is a function that can align all of the 2's in the same column -- let's say Column C -- while maintaining the same data that was to the right or left of the 2 (up to two values on either side) before all of the 2's were aligned in the same column.

I could certainly manually align the central column by copying and pasting repeatedly, but that would take an incredibly long time -- and I am sure that there is a better way to do this. I can use a VLOOKUP function to align all of the 2's in a column as well as everything to the right, but I have tried using the INDEX and MATCH functions to do a VLOOKUP to the left, but this doesn't seem to be working because the source column (where the number 2 is) varies by row, so it doesn't match in the same way that a VLOOKUP function does.

View 5 Replies View Related

VBA Getting Data (5 Column To Multiple Rows)

Feb 22, 2014

figure it out thus problem with macro,

before/sample data :
name
address
gender
activity
code

[Code]...

after macro :

george
g
t
jas

[Code]...

it's possible with macro??

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved