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
ADVERTISEMENT
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
Jun 11, 2014
I have a report that lists the # of times an agent in a call center passed or failed their call based on specific guidelines. The report that's generated will not show the failed category if they didn't fail any calls. I've attached what the report looks like. I'm trying to format the report so that in column A I have the name, column B the passed calls and column C the failed calls. Column B and C can be interchanged...doesn't matter too much. If they don't have any failed or passed calls, I'd like a 0 to show up. I've tried messing with filters and INDEX/MATCH functions but can't make any headway.
View 2 Replies
View Related
Feb 23, 2014
Rearrange dataset from columns to rows
However, the solutions do not work if "species " are text instead of numbers:
Parcel
Species1
Species2
Species3
[Code]...
View 4 Replies
View Related
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
Jun 21, 2013
Daily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.
I have the file with various columns, such are.
Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name
The above column order are not constant, daily the order will changes. But we have to make the order as below
Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid
Like this we have so many columns in the file and we used to rearrange as required.
Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.
View 2 Replies
View Related
May 9, 2014
I create a report where in I need to rearrange the columns in a particular manner as required by the client. Any macro which will will work as per following criteria.
Column headers start from A1 onwards (the last column header might vary depending on the creator, I get the report from a different team). The number of rows of data might vary.
For changing the order of columns, the macro should look for a column with the header "Close Date", cut it and insert is before a column with header "Bookings". Then, the macro should look for a column with the header "Description", cut it and insert is before a column with header "Selling BU". The macro needs to then cut two adjacent columns namely "Sector" and "Sub-Area" and insert it before the column with header "Total Bookings".
View 2 Replies
View Related
Apr 19, 2008
I saw a post that needed a solution for moving blocks of cells in order to get fewer rows but more columns. The post is gone but I'd like to think my work wasn't in vain as the algorithm was more difficult than I thought. The problem was wanting make a 5000 X 4 grid into a 1700 X 12 grid, 55 rows and 4 columns at a time. This is sometimes done for visiblity or printing purposes to get more data going across instead of down. An example would be wanting to have more data accross on a print page and the page holds 55 lines of data.
For example, A56:D110 would be moved to E1:H55, A111:D165 to I1:L55, A166:D220 to A56:D110 etc. The code is somewhat generic so different blocks of rows and columns can be specified. To see this code work, enter the number 1 in columns A-D, then fill series -> step by one for a thousand or so rows. Then run the code.
Sub FewerRowsMoreColumns()
'Rearranges blocks of cells so the end result is more columns and fewer rows
Dim RowStop As Long
Dim ColStop As Integer
Dim RowStep As Long
Dim ColStep As Integer
Dim RowOffset As Long
Dim rw As Long
Dim col As Integer
ColStop = 12 'Enter the last column number you want the cells moved to
RowStep = 55 'Enter the number of rows you want to move at one time
ColStep = 4 'Enter the number of data columns you are starting with.....................
View 2 Replies
View Related
Oct 16, 2013
I have a lot of numbers in different columns:
A B C
1 1 2
2 4 6
3 6 9
4 8 .
5 9 .
6
7
8
9
.
.
.
I want to to order them so they leave blank rows if they don't match...like this:
A B C
1 1 2
2
3
4 4
5
6 6 6
7
8 8
9 9 9
.
.
.
View 11 Replies
View Related
Apr 3, 2014
I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp‎
View 2 Replies
View Related
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
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
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
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
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
Oct 18, 2009
I need a macro to manipulate some data. The attached example contains more details.
View 2 Replies
View Related
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
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
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
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
Mar 17, 2013
i have a sheet that contains side-by-side,below two formatted colums which has continuous weeks appearance(named PE sheet). i wanna to rearrange the colums to be a single top-down colums according to continuous weeks(named sheet2.are there any easier and faster techniques?a macro probably or a function? arrange(english).xlsm
View 1 Replies
View Related
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
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
Aug 27, 2013
How to rearrange slices of pie chart in descending order without sorting the data?
View 1 Replies
View Related
Apr 25, 2014
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls
View 5 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
