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.
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.
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.
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.
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);
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...
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
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 .... .... ....
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.
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?
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 need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.
I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.
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.
Is there a quick Macro I could use to rearrange text in a cell. For example our organisation list all the departments in an unconsistant way and it makes finding the departments hard using A-Z. For example some department will say "Department of ...", "Dept of ...", Division of ..." or "Div of ...". (Obviously excluding the quotes).
What I wanted to know is this. If you highlight the range you wish to work with, could you use a message box to ask you what text you wish to edit and then automatically search the range and make the changes.
For example search for "Dept of " (note I would need to keep the space after of otherwise I would have leading spaces) so that "Dept of ABC" becomes: "ABC, Dept of" and say "Division of XYZ" becomes "XYZ, Division of"
I have some 100k+ cells with values as "ID_code - Value_1 - Value_2 - Value_3". Lets say this is sequence A.
"ID_code" is a fixed 6 digit value. "Name_1", "Name_2" and "Name_3" are variables and they also vary in length, but they never contain a sign "-". Sign "-" is only used as separator between these four values.
Now, VBA code that would rearrange all the values in a selected column from the one as listed above to a different sequence B, for instant:
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.....................
