Im currently working with some data output from citrix and am finding that the system generated reports are not very user friendly. The format of the reports is as below (example only contains five products. The real report contains 500+ product IDs).
System Report Format.xls
Can anyone suggest a macro that copies and pastes data from the system generated report to a diff worksheet with a format such as attached:
New Format.xls
The challenge is that each of the product codes in the system generated report have two rows of data (recommended orders & open orders). Will it be possible to have the macro to SUM up the values in the same period of time in the new worksheet under the new format?
I have a report presented in the format such as the one in the attached file.
Current Format.xls
Is there a macro that copies and pastes the data from the original report to a new worksheet with the format such as in the attached file below.
New Format.xls
The reason why i would rather not use Pivot Tables for this purpose is because the pivottable is limiting me from expanding the report to include other looked up data with the AutoFilter option.
I copy data from word document to Excel and it will look like this:
Name Address1 Address2 phone fax e-mail website
I have like 500 sets of this above data. When I copy & past to Excel, it is all populated into Column A. But I wish to put Name in Column A Address1 in Column B Address2 in Column C etc.
In Cell A1 I have a company name which I wish to keep in cell B1 I have company address data I wish to keep and in cell B2 I have more address data which I wish to move to cell C1. Once this cells data has been moved I need to delete the two empty rows and then perform the same process on the next chunk of company data which is in the same format as the above and so until the end of the entries - sometimes potentially there could be hundreds of entries. I have attached an example of my problem - in the 'Original' worksheet is data in the form that I recieve and need to re-format. In the 'Fixed' worksheet is data in the format which I would like to have a macro to change it.
I have a worksheet that has similar data but some maybe different lengths. What im trying to do is to be able to have them all in line so where the diagram no. is on the worksheet they are all lined up. On the excel sheet attached I want TURN NO in column J to line up with turn no in Column A each time.
I need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.
I have 447 worksheets in my workbook, I need to consolidate them into one sheet and transpose the data so that it goes into across rather than down. I really only need data in rows 19 to 40 but the data in 29, 30, 31 A to I needs to be in one cell.
I'm trying to take spreadsheet data in columns and reformat into rows. See attachment. Basically i want to duplicate columns A thru E by row specific and then take columns F thru K in pairs to match up with the duplicated rows corresponding to the specific rows. So every row currently will become 3 rows (A thru E) with Columns F and G (old F & G row 1, H & I row 2, J & K row 3)
I would like to state how fantastic a resource this forum has been to me as a beginner. So keep up the good work! The issue I have is that my spreadsheet contains one column which shares two types of data: Component Type and Tag Number. What I would like the script to do is:
1. create a new column 2. move the Tag Number data from its current column into the newly created column 3. ensure that the data is still on the same row as it was previously
One thing to bear in mind is that the tag numbers consists of various formats (spaces/no spaces), but always begin and end with brackets - "(XTU U-532-934)". I have included and example of the current state of the data and what I would like to look like after.
I have a sheet that has multiple records of the same recurring format (see attached .xls) from which I must extract data from the same groups of cells in each record (highlighted in yellow for example purposes) and export to a columnar format in another sheet. The only variable with the format is where we get into the amount of people on the plane and thus the line may shift down one or two spaces. I would like to just pull the data for the same person's name from each record as well. What is the easiest way to go about doing this?
I'm trying to write code in Airport1.xlsm to allow me to create a macro to automatically copy data from Airport-Data.xlsm and reformat it to what is needed in Airport1.xlsm.
Basically I need the code to take each airport in column A of Airport-Data and where there is a non-zero value in rows column C to G I need it to clear the appropriate column in Airport1.xlsm and add a 1 to the appropriate box.
I got stuck thinking about the nested For Next Cell in range procedure and how to execute it with a search for the correct row in Airport1.xlsm.
I'm using Excel 2003, version 11 and am trying to reformat data from one sheet to another. What I would like to do is copy the rows for each team from the Input sheet (Input tab) and and use them to build rows on the Output sheet (Output tab) for each possible combination of players.
In my attached example, a three person team has three combinations and a five player team has ten combinations and so on. I know this is probably a loop within a loop but I can't seem to get the right combination. Not all teams are full yet so the number of players on a team will vary. I need macro code to make the input sheet look the output sheet.
Rows 1 to 12 of the attached .jpg file shows the format that I receive from a University. However, in order to check enrolments, etc., I would prefer it to be in the format as shown on rows 15 to 19, that is, one complete student record per row.
As you can see, not all students are studying the same number of subjects and the subject mix is also different.
My fixed asset software will not allow me to show multiple individual months of acquisitions. I can export each month. This will create a separate spreadsheet for each month, but the problem is that each spreadsheet may contain the same asset, but additions to that asset.
I have a worksheet of data collected from on online list of names and addresses all in one column. I'd like to pivot the data so I can sort it, etc. Unfortunately, the entries are not the same length (meaning some have five rows some have six or seven, etc. I have created an only file so everyone can see the issue.
I have obtained the following data which I need to put into a spreadsheet to import into an accounts program. I can't change the way I get the data which is as follow
Hrs worked Rate 05:55:00£30.00/Hour 07:40:00£21.00/Hour 05:45:00£30.00/Hour
What I need to have is 5.92 30.00 7.67 21.00 5.75 30.00 ie the time format in decimal and loose all the unnecessary symbols etc for the rate
I m trying to use a variable that is declared as a range in a formula. GroupCategory is the name of the sheet its getting the data from. I want to use myRange instead of actually putting in the range. I know there's something wrong where I highlighted in red.
Sub AAmacro() Dim myRange As range Lastrow = Cells(Rows.Count, 1).End(xlUp).Row Set myRange = Range(Cells(2, 1), Cells(Lastrow - 1, 1)) Sheets("sheet1").Select Range("L2").Formula = "= SUM(INDEX(GroupCategory '!' & myRange,,MATCH(C1,GroupCategory!$A$1:$ AI$1,0)))" End Sub
I have a fairly simple problem that my typical method of recording a macro and editing it isn't working for. What I'd like to be able to do is point a macro to a certain spreadsheet, and have it reformat the text of that sheet into a new workbook. I get new sheets from a customer each week, so the name of the sheet isn't constant. The process isn't too difficult to do by hand, but the sheet I'm trying to reformat is typically 30 lines or so of data, which is cumbersome to do manually line-by-line.
I've attached a sheet with a sample of what the data starts out looking like and what it should look like in the end. My goal is to take a single row of data and turn it into a check sheet so that there's a check box between each part number and each process. The number of parts can be anywhere from 6 to 17, but the number and types of processes is always the same.
I've attached a small sample of an excel file I created from a CSV. The date has defaulted to American format and the time is, as you can see, messy. How can I reformat so that I can have British date format and 24 hour clock?
I have a spread sheet that I'm using to organize data from a web based database. I have a formula that shortens a document number (i.e. 93400034) to a julian date (i.e. 9340). My problem is that when I copy and paste the 2010 document numbers (i.e. 00240057) into the spreadsheet excel automatically changes the number to 240057. I've tried to format the cell to read it as text, but everytime I paste into the cell excel changes it back to general and takes off the leading zeros. Is there a fix for this.
How should i reformat the date and time picker? at this moments, it shows the date in the format of mm/dd/yyyy but the format that i want is dd/mm/yyyy.
I have a worksheet that has a column of numbers entered as 1234567. The person that input these numbers should have done this: 123-4-567. Is there a macro that could do this automatically so I don't have to go into 1150 cells and manually change the format of each of them?
I am trying to figure out a problem. So far without any results. As an Intern at a company, every week I receive a .txt which I have to import and reformat in Excel. Every week this means several hours of work, so I decided to see if this process could get automated. Or at least partially. I was wondering if any of you could give me a hint how to do this, or where to start. Attached you will find the raw .txt data and the format it should get in Excel.
I have a spreadsheet where I extract the last 3 digits of a five digit string by using the "=Right(...)" command.
Column A 71500 Column B =right(column a,3) Column C 500 Column D = lookup(Column C,toc_desc) Column E #N/A (if it worked, this would read Labor)
I want to take the result of that command (Column C) and use the "Lookup(...) command to translate type of cost as a name rather than the three digit number (Column D). Excel doesn't recognize the result of the "=Right(...)" command. I am getting #N/A as a result. (Column E). How can I get Excel to read that number and give me the results I need?
I have no VBA knowledge but need to write a macro to look at each worksheet in a workbook , search for a cell with " Total" text, then format the cell upper and to the right as underline.
I tried to record "find" but nothing happened. Is there some existing code I could adapt?
I got some vb code from these forums to use with excel. But, I'm not sure how to run the code in excel. Here is the link to the topic:
Move Text Layout To Different Format
Basically, I too need to move text to other cells within excel. The code listed is exactly what I need, but I can't figure out how to run it in excel. I'm not an excel expert or vb expert obviously.
I have created a simple address book in Excel 2007, but it is not reader-friendly: My header row in Sheet1 consists of cells labeled “Category”, “Company Name”, “Street Address”, “City, State, Zip”, “Contact Person”, “Office Number”, “Cell Number” so the data for each company is listed in a single row. I would like to see if there’s a way that I can setup the workbook such that the data from Sheet1 automatically reformats into a directory-like format in Sheet2. Ie:
Company Name1 Contact Person1 Street Address1 Office Number1 City, State, Zip1 Cell Number1
Company Name2 Contact Person2 Street Address2 Office Number2 City, State, Zip2 Cell Number2
etc
Also, I would like to figure out a way to filter by the Category column, so that Sheet2 contains all items on Sheet 1 that are labeled Category A, Sheet3 contains all items on Sheet 1 that are labeled Category B, etc.
