Is there a way (UDF or Macro) to extract from every line(cell) (and put them in the cell to the right) these raw data only the amounts with the Currencies.
PS. (most of the times the amounts mentioned before are the biggest number in every line!)
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
I would like to total numeric amounts based text in another column.
For example, I have a bunch of rows in "column F" with the same text (company name) and in "column I" i have numerics. Is there a way to total "column I" based on the same text of "column F"?
I want to return the 7 letters of text that appears after the word Timer in cell A1. So cell A1 will be a sentence with the word Timer in it somewhere and I want to return the 7 characters after it appears.
My data looks like this - it's a text file that I import into Excel.
What I'm looking for is operations that require welding, as indicated by the "Weld:" in Row 61. If it is a weld operation, I want to know the time (121.705) and the Operator involved (Opr 2 - Row 64).
I'm simply using an IF statement in a loop to extract these specific cells:
Range("N2").Select Do ActiveCell.FormulaR1C1 = "=IF(MID(RC[-13],11,5)=""Weld:"",LEFT(RC[-13],3),"""")" ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -13))
Because I don't know how to write "real" code, it does a similar loop 5 times. Once to pull the sequence number (the first number), then the time, then I have 3 separate columns looking for Operator 1, 2 or 3, respectively. Once those 5 loops are complete there is a pivot table that summarizes the sequences for each operator.
It works fine, but it's slow. Some of the text files have 10,000 lines in them.
I need a macro created, which extracts data from the text file, and displays the low level detail which is on the text file.
I have created a sample of the desired results on a tab called "Low Level Results".
I have already created a macro already which extracts data from the text file, but this gives me a high level view and the results of this is on the tab "High Level Results" ( which you might be able to adapt)
Please find attached two files, Text File, And Excel spreadsheet
I have a worksheet with over 10,000 records. The column that lists where a person is willing to relocate can have up to 60 city/state entries in one cell.
Here is an example of what appears in one cell - this is exactly how it appears:
ASAI Los Angeles (XX , CA DFO Pacific (XX ONLY), CA DFO Pacific Area Analyst Laguna Niguel (XX ONLY), CA SAI Los Angeles (XX ONLY), CA Ldr Los Angeles El Segundo POD (XX ONLY), CA Ldr Los Angeles Long Beach POD (XX ONLY), CA Ldr Los Angeles POD (XX ONLY), CA Senior Ldr (XXXX) Washington (XX ONLY), DC
What I need to do is be able to sort on city and state, so I wanted to be able to extract and separate the city and state. I tried using a find/replace (CTRL J) to enter a semicolon between each entry and thought I could do text to columns to separate, but that doesn't work.
How I could extract this information? Notice that the first entry is missing ) - that is throughout the records.
The fields of each User record may not appear in sequence and some of the fields may not appear in other User record. The file contains thousands of User records.
1. I need to extract the User records (only certain fields) into excel for data analysis and manipulation (fill-in missing value in certain fields).
2. After analysis and manipulation in excel, I need to export the data back to the original text file format again.
I am looking to import data in text file to Excel sheet. Here is the main catch - All the data are not of fixed length and are in multiple lines as in the example below.
Text File: ID001 Dr Dang 01012011 Male USA Married 123
Below is how I expected the data extraction in Excel: ColumnA ColumnB CardID ID001 Name Dr Dang DOB 01012011 Sex Male Country USA Status Married Pin 123
I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
I have a few hundred data/text files to analyze, some a few thousand lines deep. I would like to grab 5, just 5 numbers describing voltage levels. I know the preceding text for each of these in the cell and the following text as well. I just need to delete everything else and keep these 5 values in a column using a script or a macro, and do so for all these files in a specified folder.
This would be 3 weeks of work and the analysis needs to be done ASAP for my research.
Thousands of lines of ancillary data Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
"11/09/07 14:42:20 J13-50 and J6-9, PCU Resistance (XXXXXXX. Ohms) is within 1.000 to 3741." Thousands of lines of ancillary data...........................
want to be able to take a large quantity of data, sort all the like data together, and then quantify the number of each like data. I need the equations to do that.
I am looking for a functional way to break up very large amounts of data. I am looking to break them up by an ID number and then by date. The date function needs to break up data from a hire date to the closest date to a year without going over and proceed to do that for multiple years. I am hoping that the function can just add a blank row between the split data. The file that I have not comatins three years.
I am to the end of my wits - or maybe it's impossible to do the following with formulas?
I have the data like this: Column A: Date (which is basically the date for the beginning of weeks) Column B: Month of the date in Column A Column C: Year of the data in Column A Column D: Weekly data.
Maybe it's because it's Friday night, but I just can't invent how to do the following:
Create a new column E that would contain the monthly sum of ColumnD across all weeks of this month - but entered only against the first week of that month (that is currently in Column A) I.e., in my example it should be: 113 empty empty 201 empty empty empty empty..................
if there was a way I can put it in to some kind of pivot table, because the hardest part for people when they read his list is it's so large it's hard to find data easily.
So this is how he formatted his data... I was wondering what would be the best way to get this list in to a possible pivot table. This is a consilidated example, there are plenty more columns, but this will give you an idea of my problem.
A2007 A2006 A2006 B2006 B2006 B2007 Feb Jan Feb Jan Feb Jan 630 Labor Cost 1000 7500 3000 4500 800 5000 624 Equipment Cost 900 50 40 300 20 1400
Now, the only thing I can think of is to make columns, but then I'd have to recopy all the task names (which there is about 700) for each of the different years (A= Actual, B= Budget, F= Forecast). Is there any other way that you can think of to do this with out making it so complicated? Any help or suggestions would be great. I really want some format that allows you to click the total and it goes to what makes up that total.
I have attached a tiny part of a massive data set I am working on. As you can see in column 2, the data is roughly every 15min for 5 days. The data I am interested in averaging is color coordinated in column 3 (if you scroll down you can see a different color for each day's data set.)
In column 5 the dates are summarized into days as opposed to the 15min breakdown. In column 6 is the problem. How do I get the averages of the relevant data in column 6 in such a way that I can drag the formula down and the next cell will automatically calculate the average for the NEXT day, REGARDLESS of how many temp readings there are, as this data fluctuates from day to day.
I am building automated solutions where graphs source data is based on outcome of formulas. In case of line graphs I use #N/A as result if no data is available or formula results into an error - this way the data point and data label will not be shown in the graph.
However this does not work for bar graphs - with #N/A, #DIV/0, 0 or "" the bar itself is not shown but the data label is shown (as #N/A or 0). How can I set-up my formulas so that if result is 0 or formula is in error that the graph does not display the data label.
Attached excel file shows same data in 2 charts - 1 line chart (=OK) and 1 bar chart (=not ok). The data for chart is pulled from 2 other tabs (week&month) and merged into 1 data source for graphs.
I have data that as 872 columns - each representing a question ID (headers in the first row). I then have 1494 rows of data where each represents 1 unique person. In other words, A2 = Person ID and B2-AGN2=the potential answers to the questions.
What I'd like to do is compact this into 3 columns: "Person ID", "Question", "Answer".
"Person ID" will have duplicate values for each question that is answered. "Question" is the Question Text "Answer" is each answer to each of the questions.
It would be fantastic if Excel has the functionality to ignore null answers and therefore just not even bother populating Question ID when an Answer is blank (e.g. they didn't report an age, so QAge doesn't show up under the new "Question" field), but I have no idea if that's doable.
I have a lot of datasets like this with a varied number of rows and columns, so any way to adjust whatever formula/macro is out there to work for those. I'm terribly new with macros and so I've been having difficulty adapting them if I need to.
I have a problem with my current macro that uses a basic autofilter to auto filter from the parent database to extract the correct rows and then copies the query and pastes it into a new worksheet to further proceed with the macro.
I have run into a problem because my database has become very big and now when I try to autofilter the query and click on copy, an error regarding the data range reference is too complex - use data that can be selected in one contiguous rectangle
I tried a few things such as to autofilter out everything I dont need and hit delete - this does not work either, same result
I got help here previously in which the code deletes All Hidden Rows and this is very time consuming, I have not tested all my methods but it took 15 minutes to delete hidden files for one method and theres roughly 5 in total
I have to end up running this code on the parent worksheet multiple times because I use the parent worksheet to extract different parameters into different worksheets!
I have noticed that if I manually copy the data in smaller blocks, by halving the data seems to work,but I do not know how large of a partition I am limited to copy because my database is very large and the size varies month-to-month so I cannot put a number on the max range. I think if I could get a macro to do it by thirds or preferably quarter range should be safe.
So just to summarize, I am trying to devise a method in which I would auto filter on the active parent sheet "sheet 1" and I would copy the auto-filtered query to "sheet2" instead of copying the whole worksheet in one instance I would like to split the autofitlered query into four equal parts with respect to the range of the worksheet and then to copy the first quarter of the query and paste in sheet 2 and then the second quarter to sheet 2 and so on untill all four quarters are done one after the other, so sheet 2 should be a series of all four parts combined into one series on sheet2
I have a list of people in column A and a list of Cities that they have visited in column B.
I need to check some of the cities they have visited monthly but don't want to check them all.
I have attached a sheet as an example (this has been scaled down).
The number of cities i want to check for each person varies each month depending on how many cities they have visited.
For example, John has visited 16 cities and i want to check 5 of them. I therefore want 5 random cities that he has visited to appear next to his name at the top. The real list of data is massive so this would be really useful if it is possible.
I have looked at rand but i can't get it to randomly give me more than one city, and i don't understand how to get it to give me say 5 cities one month and say 8 cities the next month purely based on a formula from another cell.
I've produced a workbook for my business where we can do automatic quotations based on a simple vlookup formula. We do however quote in a number of different currencies, ie Sterling, US dollars and the Euro. Is there anyway we can pick these conversions up by using data validation to convert the relevant cells or is there an other way?
I am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
I have one worksheet with four columns of data. Column A is a well name, RA-0001, column B is the measured depth of the well from 0 feet to however far down it goes, anywhere from 4000 to 15000 feet, column C is the inclination of the well, column D is the Azimuth.
I have 500 wells from RA-0001 to RA_0500 or so all in this one worksheet, all the wells have varying Measured Depths associated to their well name. I need to create a macro that can separate the wells and either put them in a new worksheet for each well, ie. a worksheet named RA-0001, RA-0002, ..... ect. OR, and this would be nicer, a macro that can actually save all these individual wells as (Formatted Text (Space Delimited)) files with the associated well name.
Here is an example of what it looks like. The columns do not have a subject line to state what information is in each column because I dont need it in that format.
RA-0001 0 0.00 0.00
RA-0001 100 0.91 5.56
[Code] .......
Even just knowing how to create a simple macro that would take all the data from each well so I could manually copy and save them as new files.
I am creating a simple spreadsheet to catalog some magazines. One of the columns shows the cover price which is okay for post 1970 magazines as it is in Pounds Sterling, but is there anyway I can get Excel to recognize Pounds/Shillings/Pence as currency. So put in "8d" for example without setting the field to a text format?
I want to be able to add up the total cost of some items based on their cover price but if I set the required cells to text format I cannot do this automatically using the Sum forumla.
Is it possible to have both Pounds Sterling and Pounds/Sillings/Pence as currencies in the same table or is using a text field for the latter the only option?
A$ SING $ Euro NOK UK US$ 1.07001.25000.74186.10000.62081.0000 A 934.58 B 800.00 C 1,348.07 D 163.93 E 1,610.82 F 1,000.00 G 1,869.16 H 1,600.00 I 2,696.14 J 327.87 K 3,221.65 L 2,000.00
TOTAL 17,572.23
Values in column "H" (US$) have been divided by currency rates in Raw C2~G2 as we need the total in US $
But we also want to know the total amount of each currency used in column "H".
How to put a formula to find the total value of each currency used.
