Rearrange Columns In Particular Manner In A Table
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
ADVERTISEMENT
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
Apr 5, 2008
I have searched numerous times for the answer I need, but no joy yet.
I have a spread sheet that has one column (call it 1) of data with a single entry each of 100, 200, 300, 400, 500, and so on.
I have a second column (call it 2) that has multiple entries of 100, 200, 300, 400, 500, and so on, and I want to sort so that the rows wind up with the numbers in column 1 all sorted in an accending order, but I want all the same numbers in column 2 sorted to "list" next to the same number before the next number sorts. See below for a small example of what I hope to see once it sorts.
100
100
100
100
200
200
200
200
300
300
300
300
Auto Merged Post Until 24 Hrs Passes;Unfortunately the example didn't post correctly..perhaps this will work but note that the x's are in column 1 and don't exist.
100
xxx100
xxx100
xxx100
200
xxx200
xxx200
xxx200
300
xxx300
xxx300
xxx300
View 5 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
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
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
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
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
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
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
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
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
Nov 16, 2007
Assuming i copied a list of cells that are vertical [column format], how do i paste it such that it will become horizontal [horizontal format]
View 11 Replies
View Related
Mar 13, 2014
I have sets of data... like so:
A word text blablabla
B word text blablabla
A dog cat france
B dog cat france
B this that other
A this that other
A tiger dragon monkey
B tiger dragon monkey
My source data I can't get to sort alternating on columns A and B, and I would like the data to alternate A-B-A-B-A-B while retaining the pairings of data in the adjacent fields. Is this possible?
View 1 Replies
View Related
Apr 17, 2014
I have a 1640 line database in Excel to sort in a specific manner and create a booklet in Publisher for a Family History group.So here is the data as it appears at the moment;
SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH
DATE 2
Date 3
SURNAME - FIRST NAME - DATE 1 - F SURNAME - F NAME - PARISH
DATE 2
DATE 3
So A to Z sort Column A BUT retain DATE 1-3 in the finished state,and same for next 400 groups.
View 4 Replies
View Related
Sep 11, 2009
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
Jun 27, 2007
I need to Match to columns of one table to another two columns of another table, both tables are on the same worksheet.
One table has 500 rows more that should not be, I need to find out which of these 500 rows should be deleted, so the two tables should be same. So the batch number and corresponding amount should tie to another tables batch number and amount.
View 9 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
Jul 31, 2009
I have a column of numbers that are "dated" like this:
90421
This number represents the date: April 21 2009
Is there a way to rearrange the numbers in the cell so I can then format as a date that looks like this: 4/21/2009?
View 11 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 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
Oct 17, 2012
How do I turn cell contents 20121015 into 15/10/2012 and as a date format.
The original cell value starts out as below as a filename that contains the days date:
C:FixedHoldings Report20121015 - holdings Main.xls
I then use this to turn it into 20121015:
Code:
Range("D16").Value = Mid(Range("D16"), 25, 8)
The value will always be in a similar format but the dates will change.
View 9 Replies
View Related
Mar 13, 2014
I used this code to sort sheets,
Code:
Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
[code].....
now if my sheets name is like this 001_Tommy, 002_Alex, 003_John, 004_Daniel, 005_Alan,......., and I want to sort sheets name after the underscore "_", arranged like this, 005_Alan, 002_Alex, 004_Daniel, 003_John, 001_Tommy,......, how to do this?
View 2 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
