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.
My colleague and I are writing a udf which uses a "match/ index" combination to return a value to a cell from a named range in another sheet. The cell returns returns the error "value used in the formula is of the wrong data type". When we break down the code into a function which refers directly to the relevant cells, and a sub which returns the value in a message box, it works correctly: here's the code for the function and test sub:
Function MRL(PropertyType, ReturnType, Location, Year2) ....
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
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 RelatedI 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.
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
I need a macro to manipulate some data. The attached example contains more details.
View 2 Replies View RelatedWe 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.
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.
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.
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 RelatedI 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
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.
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?
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
How to rearrange slices of pie chart in descending order without sorting the data?
View 1 Replies View RelatedI want to present a user with a list that they can make a selection from and enter their selection in a cell. Sounds easy but.........
View 9 Replies View RelatedI have a file where cell A1 is the name of the file along with B1 and C1 which provide the versions and variations:-
A1 - Sales Analysis
B1 - Version 2
C1 - Variation C
Is it possible to have a macro driven off a button button which will save the file as (current path to current folder) and then Sales Analysis Version 2 Variation C
Hello all. Can someone please help me save rows to the variable name stored in dtselect4 (which captures the name input by the user on a userform)?
The sheet is created here: ...
First I would like to thank you for taking the time to assist me. My issue is a follows: I want to have 2 dates (beginning and ending) input on one sheet. With these 2 dates entered, I want to generate a date range between them but have it output on a different worksheet. Here is the function that first pulls the date values entered:
View 2 Replies View Related1 - We receive an excel file from our customer (approx 20 different customers in total)
2 - I have an excel template we have to use, which will import into our MIS software. This excel template must remain the same or it wont import into our system. ie (Columns must be in this particular order)
3 - Our customer sends their own spreadsheets in their own formats ie, column in a different order to our excel template.
4 - I need to know if there is a quick way to remap customer columns into my excel template
5 - Im looking for a little application that will look at the customer spreadsheet and our template in the same window and let me link which fields need to map where in my template. When I have completed I can save what ive done and run when needed for this customer.
I just wanted to understand something about some code that was supplied to me on this forum. The code works perfectly, with the exception of one minor issue. The original thread and solution presented is found here
View 2 Replies View RelatedI 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?
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 RelatedHow 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.
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?
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.
Rearrange dataset from columns to rows
However, the solutions do not work if "species " are text instead of numbers:
Parcel
Species1
Species2
Species3
[Code]...
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".
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