Attached is the excel file which I compressed.
I need turn the data on sheet1 into the data on sheet2?
The data on the sheet1 always varies everyday so there's no exact data will remain on the same column or row on the following day.
I've been doing this manually by copying and pasting it.
And it takes me around 1 hour if its the end of the month.
Is there any formula to write digits convert into word ?
Eg: - Can 1020 Convert into "One thousand twenty" with the help of any formula ?
I've posted an example workbook that has sheet1 as how the data comes and sheet2 as what I need it to look like (through some kind of automatic process). I really don't have a clue as to how to get this done, and I'm guessing some VBA is going to be required.
View 3 Replies View RelatedI have entered a lot of functions mainly VLOOKUPs. I want to write VBA codes for the functions without manually writing them. Is there a way in which I could get the manually written functions converted to codes automatically?
I have a lot of .txt files that needed to be converted to .csv file format. Right now I am doing it manually using MS Excel i.e. File > Open. For each file, I need to specify the length of each fields one by one, so it is quite an effort for me.
Is there any way to automate this process using MS Excel or any other existing programs?
I have a dir with over 150.000 files in it, especially pdf, doc and xls. Now I'm looking for a script or program that will automatically search the entire directory and look for old xls files, open them and save them in a new(er) version. I'm working with a software that needs to use these XLS files in my directory but it can't use the old xls-versions. It's an impossible job to search by hand for the old versions and put them in a new(er) version.
View 11 Replies View RelatedI have attached a sample workbook. The workbook already has a macro that can send an email after working out if the date is overdue. I have put a button on the screen, but I would like to remove the button and automate it, I got the initial macro from,( whoever initially put it together) but I have since made some changes to it regarding how the message displays etc. i, 15 or i, 16 in the macro means, There isn't anything in column i and I can't work out the relevance of it.
View 1 Replies View RelatedI'm looking for a formula that may automatically convert date into single digit e.g today is Jan 03, 2012 & after adding (1+0+3+2+0+1+2) it will come out 9 ....
View 6 Replies View RelatedIs there a way to find cells that have a date in them that has a text digit with a 2 digit year to 'xx07' rather than having to click on the error and selecting it manually.
it's playing havoc when I'm pulling some data from another workbook so converting en masse would sort the problem out!
Anyone know how to do this?
I have a Table with MB and GB numbers.
I copy and Paste these into Excel, What I need, is for Excel to Automatically convert the MB into GB by /1024
I tried something like this
=REPLACE(A1,FIND("MB",A1),9,"(But can I enter a Maths here)")
Is there any way that I can have a column of cells change the entered 4 digit to a time format? E.g. when somebody enters 1212 in a cell it will automatically convert the entered numbers to 12:12
View 9 Replies View Related1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.
the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies View RelatedHere's a sample of the document I have, the original has several thousand entries, so figured for the sample I'd limit it. The raw data is on sheet 1, and sheet 2 is what I want to have to more easily manipulate the data. I started doing it manually, but I'm sure there's a far better method that I just don't know about yet. sample.xlsx
For those that don't want to download the document:
My data is like this:
ID1 Field1
ID1 Field2
ID1 Field3
ID2 Field1
ID2 Field2
ID2 Field3
ID3 Field1
etc.
What I want is this:
ID1 ID2
Field1 Field1
Field2 Field2
Field3 Field3
etc.
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112
Value 2345678910111213
And get it like:
Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
I have a data which contains:
A B
1 Name: A
2 Address: NY
3 Gender:Male
4 Cell Numbers: 123
[Code].....
I know that this has been asked a million times, but I need the new table in a new sheet (which is sheet 2 and also I skip the Cell Phone numbers data).
I have a sheet with data 7 columns wide and up to 1500 rows long. Below are a few lines from the sheet. The data from the sheet is grouped into "months" as shown in the header down the first column, "Show June, 2013..." and "Show July, 2013...", and so on. Is there a way I can make Excel re-align the data into ONE long row of data for each month? Each new month row must be "triggered" by the text (like the word "Show") because the actual cell number of where the new month begins may change with new updates. I want to do this because I plan to refresh the data once at the end of each day from the web and apply the new data to the "next" row of data. So eventually, I can have a history of "Last" values I can chart for each "strike" for each month.
CALLS
PUTS
Show June, 2013 Options Hide June, 2013 Options
Symbol
StrikePrice
Vol
Last
Last
Vol
Symbol
quote
15.00
2.00
14.25
0.02
22.00
quote
[Code] ........
I'm trying to figure out how to copy raw data from one file(emailed to me) and paste it to my existing file "File b" into a table "tbl a"(to make it dynamic). From another table "tbl b" on another sheet within in "File b" I want to auto populate "tbl b" with all the records from "tbl a" but not all columns from the records. To make it more difficult, I want to edit some of the data and the headings between the two tables are not the same. Example below.
Ship-to-name
Product Name
Date Shipped
Customer Group
ABC-Atlanta
Advil (Ibuprofen) 800MG
11-15-2013
Wholesaler
[code].....
Notice column b has different heading and the data need to be modified. Column d is not needed at all.
Also this need to happen when the raw data is copied into "tbl a" and again all records need to be copied over with changes.
This also needs to be done outside of VBA if possible using just formulas and possibly filtering.
I'm using MS Excel 2013
I need to convert a column of data which will have repeated values into a column that turns that data into data with all unique values. Below is an example.
Column1Column2
AppleApple_1
OrangeOrange_1
PearPear_1
AppleApple_2
PearPear_2
PearPear_3
AppleApple_3
OrangeOrange_2
OrangeOrange_3
I had a solution until I wanted to turn this range into a list. The solution was.
=If(ISBLANK(A2),"",A2&"_"& COUNTIF($A$2:A2,A2))
When this data is in the list the <A2> in the range stops incrimenting from row to row and simply turns into the last row of the list. I have an example spread sheet upon request.
I have exported a text file from a universe database and need to import it into excel.
Below is an eg of one section. The text file contains approx 257 of these, all one below the other.
Between each set of dotted lines is one user profile (not data all shown in eg) .
Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user.
A simple import places all data into one very long column.
I need a way to place each user in there own column.
I have user names as column headings the labels of the values below as row headings. I cannot change this.
Manually entering the data took a very long time and i need to update this workbook regularly.
In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon.
This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user.
Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column.
The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated.
Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book.
Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated.
One EG user profile (not shown, but all the colons line up) ....
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
View 2 Replies View RelatedI want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies View RelatedI have two column (A and B) text data in worksheet1. If I type same text of column A (of worksheet1) in worksheet 2 column A, how can I retrieve data from worksheet 1 to 2 from same row.
View 1 Replies View RelatedIn the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies View RelatedI am not sure how to go about creating a spreadsheet that does this:
I have a number much like a stock market index which is in cell A1 for instance, and this data changes every second,
And I wish to create my spreadsheet to automatically make a record of this change value and populate it at the last available blank row, so ultimately, I have a full history of how this number changes during the course of time.
5450 in cell A1
a second later, it changes to 5430
and so I want to populate 5430 to A2
and then another second, it changes from 5430 to 5470 and so i want to populate 5470 to A3 and so forth....
does anyone know how to do it?
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
I am doing a literature review and at the moment I have a table where I am listing all articles and ticking of their "theme", "theories used" and "methodology" like this:
How_do_I_get_from-this.jpg
Now I need to map the theories onto the articles, like this:
to_this.jpg
How do I accomplish this? (in an automated manner, obviously)
I hope the pictures are sufficiently self-explanatory, it is kind of hard to explain what I am trying to achieve otherwise. Note that ticking boxes is accomplished by changing the font to wingdings 2 and using uppercase P for checking a box. I know there are real checkboxes for this purpose, but I am generating the table from imported data and can't find another way to program excel to tick boxes if a condition evaluates to true.
Go to this link to download the original file: [URL] ....