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 am creating a graph which charts the most recent 3 years of YTD data, and right beside it, the past 13 months of monthly data. The data comes from two other worksheets and is brought in with an Index/Match formula, but to create the chart without having to go and update the data series every month the data to chart (in row 46 to 49 of attached worksheet) needs to change each month according to data in rows 2 & 3, and when the Year changes, in rows 24 & 25. I am trying to do this an offset, and/or vlookup, and/or counta functions.
I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2) Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2) If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided Total KM run Basic Amount Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
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.
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?
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'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 have DR and CR entries which are laid out. in the traditional T format (under the Ledger tab). I would like to have it formatted to the modern ledger type where there are no left and right but rather laid out in 1 page. The required layout is shown in the Ledger attachment
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?
