Rearrange And Transpose Data

Oct 18, 2009

I need a macro to manipulate some data. The attached example contains more details.

View 2 Replies


ADVERTISEMENT

Rearrange Table - Transpose Rows To Columns

Nov 2, 2007

I have problems to write a macro to rearrange the table from attachment file sheet1 to sheet2. I have many files that need to transpose rows to columns and with continually years, month, day, and the rest parameters. some to the files many content more then 20 years data.

View 5 Replies View Related

Rearrange The Data

Jan 1, 2010

I've the data like this

Item 1,Item 2,Item 3
Product 1a,Product 1b,Product 1c
Product 2a,Product 2b,Product 2c

And I want to rearrange like this
Product 1a
Product 1b
Product 1c
Product 2a
Product 2b
Product 2c
Product 3a

View 9 Replies View Related

Rearrange Particular Data In Spreadsheet

Jun 11, 2014

Looking to re-arrange particular data in attached SS. Sample has the highlighted data which is then converted to the result SS. Have many rows with variable lengths for each record.

View 5 Replies View Related

Rearrange Data Into A Certain Format

Nov 24, 2007

I am seeking the expertise of you knowledge folks to advise me whether it is possible to create a "script" in Excel to jumble up certain letters in an entry?

Let me explain. What I have are 4 separate columns with data in them. I need to somehow rearrange them into a certain format, like an encryption I suppose.

Here's an example of some data I have :

COL 1 COL 2 COL 3 COL 4
ABCD EFGHIJ KLM NOPQ

What I need to do is jumble those up so they appear in the following format:

COL 1 COL 2 COL 3 COL 4
CDAB GFEHIJ LKM NPOQ

Is it possible to do this in Excel? You're probably wondering why I don't do it manually, but there are over 20,000 records that need jumbling up and obviously that would take a very long time.

View 10 Replies View Related

Rearrange Data Supplied By Others

Aug 28, 2009

I have been supplied a list of data (company name, address, tel no, email ect.) unfortunatly the data is not in the correct order. There is about 300 companies on the sheet.

Is there a way i can rearrange the data, i have attached a file to show the end result i am trying to acheive.

View 10 Replies View Related

How To Rearrange Raw Data Into New Table

Oct 15, 2013

I have a raw data sheet from the CTI in my workplace (sheet 1 in the attached file), the problem with the data is that i can't seem to be able to sort it in a table like i want (like the one in sheet 2 in the file) because of the inconsistency of the data location,i was thinking that there must be some formula (offset?) or macro code that can arrange the data from sheet 1 into the table in sheet 2.

m.xls

View 2 Replies View Related

Copy And Rearrange Data?

Apr 17, 2012

We need to copy data from column A of sheet2 (from A2 to the last row) in sheet3 (from A2 to the last row).

In sheet2 column A
01.03.2012 10:05
02.03.2012 03:5617 h, 51 m, 02 s118.117.65.17510:C4:91:B6:96:B202.03.2012 10:3003.03.2012 01:2214 h, 51 m, 47 s116.100.124.13510:C4:91:B6:96:B203.03.2012 09:1903.03.2012 14:2405 h, 04 m, 56 s99.87.106.22810:C4:91:B6:96:B2

need to be in sheet3

Time INTime OUTDurationIPMac address01.03.2012 10:05
02.03.2012 03:5617 h, 51 m, 02 s118.117.65.17510:C4:91:B6:96:B202.03.2012 10:3003.03.2012 01:2214 h, 51 m, 47 s116.100.124.13510:C4:91:B6:96:B203.03.2012 09:1903.03.2012 14:2405 h, 04 m, 56 s99.87.106.22810:C4:91:B6:96:B2

In sheet2 column A is about 400 row and we need to copy this date every month in sheet3

I wish that every touch of the button to copy data in the first empty cell found, not to overwrite data.

View 4 Replies View Related

Macro To Rearrange Data

Dec 6, 2007

I need to write a macro which rearranges my data for me. The data looks like this;

Data______________Unit_____Jan06_____Feb06_____Mar06___(etc)
Coal consumption___tonnes
Coal energy________GJ
Electricity__________Mwh
Production_________tonnes
etc

This macro will need to be applied to a number of sheets with different time periods and different types of "Data". What I want to do is write a macro that will transform the data to look like this (where xxx is the data entries);

Coal consumption___xxxx____tonnes___Jan06
Coal consumption___xxxx____tonnes___Feb06
Coal consumption___xxxx____tonnes___Mar06
Coal energy________xxxx______GJ____Jan06
Coal energy________xxxx______GJ____Feb06
Coal energy________xxxx______GJ____Mar06
etc.

So that I can easily import it into access. I know basically how to do it, but I can't automate it to apply to a sheet with any stretch of data fields or types of data.

View 9 Replies View Related

Using A Macro To Extract And Rearrange Data?

Sep 6, 2009

I have a big collection of spreadsheets that are all laid out in the same fashion: from A2 down are the names of different individuals (one name per row). Column titles (from B1 right) are the names of unique qualifications that these individuals possess. In the cell where a row and column intersects is the date at which this qualification was awarded. If this doesn’t make total sense, I’ve attached a very basic version of this to the post (Sheet 1) – although the spreadsheets I’m working with have hundreds of individuals and scores of qualifications, rather than just a couple!

This data needs exporting/rearranging into a format that’s recognisable by another piece of software that we use. In this format, the data is split into three columns: the person’s name, a possessed qualification and the date it was awarded. This means that a single individual’s name may have multiple rows, since a different qualification will be present in each row for that person. An example of this layout is shown on Sheet 2 of the attachment.

View 2 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

Rearrange Data From Rows To Columns

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

Rearrange Data In Hierarchy To Table

Jun 6, 2007

How do I use VBA to rearrange the data from the hierarchy format of Table 1 into the flat format of Table 2. See attached file >Data_Belinda_June-5-07.xls

Table 1: Each record has information arranged in a hierarchy format.
Level 1 information is indented by one space on one line; Level 2 information is indented by 2 spaces on the next line, etc.

Table 2: Data from Table 1 have been rearranged into 7 columns. Another column has been added to create a field for the Reference ID.

Reference ID: Two types: i) Created by prefixing with the letters BI , adding the first four letters from column 4 and the first four letters from column 5; ii) As in (i) with the addition of the entire word from column 6.

The report I have may have more than one hundred records and there may be more than ten people within each section.

View 9 Replies View Related

Rearrange Data Report Into Database For Pivot?

Aug 19, 2013

I have a report (roughly 4000 lines) which I need to rearrange for pivot/power-pivot use.

The structure for each row/record is:

col1 geography
col2 area
col3 customer
col4 product
col5 price
col6-65 monthly sales units - 5 years (columns labelled Jan 2009, Feb 2009 .... Dec 2013)

I want to rearrange the data as:

col 1-5 unchanged
col 6 month (data Jan 2009, Feb 2009, etc.)
col 7 sales units

Currently i have 4000 rows/records (each containing 65 fields). Iwant to end up with 4000 x 60 or 240,000 rows/records of 7 fields each. Is this possible through a data import wizard or VBA routine?

View 5 Replies View Related

Excel 2010 :: Macro Or VBA Code To Rearrange Data

Jul 5, 2012

I'm using Excel 2010 and XP. I have data in rows 1 -4 that can not be altered. There are headings in Row 5, B-L & Row 6, F-J.

Data starts in Row 7 and follows the headings. This repeats for several thousand rows. I need a way to move Row 6, F-J and put it on Row 5, M-Q

Row 6, F-J and put it on Row 5, M-Q
Row 8, F-J and put it on Row 7, M-Q
Row 10, F-J and put it on Row 9, M-Q
Row 12, F-J and put it on Row 11, M-Q
....
....
....

Then delete blank rows below Row 5

View 7 Replies View Related

Rearrange Slices Of Pie Chart In Descending Order Without Sorting Data?

Aug 27, 2013

How to rearrange slices of pie chart in descending order without sorting the data?

View 1 Replies View Related

Macro To Transpose Multiple Data In Cell Separated By Commas To Each Data In Column

Jul 15, 2014

I have a table in the format below with about 3500 rows

Column A
Column B

0001
All vehicles, Retirements

0002
All vehicles, Retirements, Addition

0003
All vehicles, Retirements, Addition, Deletion from Y

I would like to change it to the following format:

Column A
Column B

0001
All vehicles

0001
Retirements

0002
All vehicles

0002
Retirements

0002
Addition

0003
All vehicles

0003
Retirements

0003
Addition

0003
Deletion from Y

View 3 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

Transpose Repeated Data With Unique Associated Data Across Columns

Jan 17, 2008

Is it possible to associate a range of cells containing different information based on like values in other cells?

Example:

How it currently appears in spreadsheet

Name Acct#
John Q. Public 12345
John Q. Public 23456
John Q. Public 34567
John Q. Public 45678
Jane Example 11111
Jane Example 22222

Wanted Result

John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
Jane Example 11111 22222
Jane Example 11111 22222

I know how to remove the duplicates afterwords to get one unique record.

View 4 Replies View Related

How To Transpose All Data

Jul 3, 2014

in transposing all data, I have data in the format below:

Material ID | Attribute Name | Attribute Value |
MaterialNo.123 | Color | Red |
MaterialNo.123 | Color | Cherry Red |
MaterialNo.123 | Color | Sunset Red |

I want to transpose it to show:

Color Color Color
MaterialNo.123 | Red | Cherry Red | Sunset Red |

View 2 Replies View Related

Transpose Data From Column To Row

Dec 8, 2013

I need to transpose column data (Sheet called "Recpt") into rows (sheet called "Formula")

Please refer to attached excel file,sheet "Formula". I have manually entered formula for 12/1/2013. Need to add formula for the rest of the sheet. Since the data is on every 4th column, I am sure it is feasible to copy the formula by adding 4th columns.

View 3 Replies View Related

Automate Transpose Of Data?

Feb 13, 2014

AUTOMATE TRANSPOSE 2-13-14.xlsx In the attached file, I am looking to automate the transposing of the date and numbers under each bold number. Data is truck # in bold, the engine oil change date and mileage below. I copied the data from a pivot and need the date and mileage in columns, date on top with mileage below. I can do it with paste special one truck at a time, the big chunk of data is about 2000 rows deep and was hoping the transpose paste special could be automated, I've made a few attempts on how to do it but can't get it.

View 6 Replies View Related

Transpose Data From 1 Sheet To Another

Feb 19, 2014

I am working on a spreadsheet that has (7) sheets or (7) tabs. The First tab is the "Summary Tab" and the 3rd tab is the "Plate" tab. I am working in a range of cells that go up and down on the "Summary Tab" and I need to fill them with the values from the "Plate Tab" that goes side to side or across. I can enter into the top cell on the "Summary Tab": =Plate!B47 and I will get the value that I need. However, I want to copy the formula or fill down and have it pull the values from the cells on the "Plate Tab" Horizontally like this (as I fill down) =Plate!_$47 Where the "_" changes to "C", "D", "E", etc.... but it stays on row 47!!!! How can I do this????

View 5 Replies View Related

Dynamically Transpose Data

Jul 11, 2008

I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.

There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.

View 14 Replies View Related

Transpose Several Columns Of Data

Aug 26, 2009

I'm working on this project that I inherited from another colleague and am at a sticking point. The workbook is used to determine which employees are working on which projects what pct of the time. The workbook was set up using a start and end date (columns e and f) for the projects instead of a column with the month and the correct percentage. I've set up monthly columns, which are now in columns K through AF. I believe that I need to transpose those columns into a row so that I can set up a pivot table.

View 5 Replies View Related

Macro To Transpose Data?

Jun 7, 2014

I need macro to transpose the raw data I have extracted from a Biometrics file into the format I need to compute for work hours.

Sheet 1(raw data)

Sheet 2 ( format i want the data to look like)

View 3 Replies View Related

Transpose Column Data To A Row?

Aug 19, 2014

I've got a workbook called MyBook1 which has 12 groups of 5 columns representing each calendar month. I am looking to create a macro (one for each month) which will be executed from another workbook. This macro will pull a column of data from a workbook called Data1 and paste into a row in the MyBook1 workbook.

Using the macro recorder, I've got the following code that works great but was trying to shorten the code and get it to work from another workbook but haven't been successful. I believe all that needs to be done is create a variable for the source/destination workbooks then set the selections and do the copy/paste but I'm having a hard time finding example macros which I can learn from.

Working code below for January, for Febuary the Data1 range is D2:D6, and the destination is 5 columns over making it M3. Then just follow the code below as a template.

[Code].....

View 11 Replies View Related

Transpose Data Of Different Lengths

Aug 19, 2014

I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally

Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.

Is there any way that a macro could be created to do this.

Performance.xlsx

View 3 Replies View Related

VB Code To Transpose Data

Mar 26, 2014

Please refer to attached file.

I have employee Clock IN - Clock Out as shown in Column A thru C. An employee can have more then 1 Clock In Clock Out as shown for Employee Name Karmen and Haley.

As shown, each employee is separated by "----------------------" and it ends with "REPORT END"

I would like a VB Code to transpose the data as shown in Column G thru H ...

View 2 Replies View Related

Macro To Transpose Data

Feb 9, 2009

I have data that gets dumped from a program into a nasty horizontal format that I need to get transposed into a verticle format. As it stands now, the info is reported with hourly data spread accross rows. I need the hourly data in one column. See attached sheet for an example of what I need done with the data. I'm looking for a macro to take my "original" sheet and create my "new" sheet. Note: a macro that can do this would save me days of time.

View 4 Replies View Related







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