I have a large worksheet with a unit name column, category, brigade, etc.
Everytime I get the data, I have to make the same changes. Is it possible for me to automate the process so that for example if the name column contains 'CID' or 'AMC MGD' delete those rows, if it contains 'ENG' change the brigade column for that row to '92ENG' etc.?
I need to transfer a large amount data (~2,5 million lines of data) from one spreadsheet to another while making sure the destination file is kept in a certain format.
Spreadsheet "oxno" is the file that needs filling out with data provided to me in spreadsheet "ox"
The value that is the matching criteria will be the ITS account number (Field B in "ox" and field A in "oxno") and the currency (Field B in "oxno" and field G in "ox")
So I want a macro to look in "oxno" and use field the values of field "a" & "b" and search for the them in "ox" in fields "B" & "G". Once it finds a match it should copy the value of field "I" in "ox" and paste into field "n" on "oxno".
The next data to be copied should be what goes into field "O" in "oxno" this data is derived from field "L" or "M" but only when its of numerical value with one non numerical field in it (usually a "/"). Last value that I need copied over is Field "T" on "oxno" this is derived from field "J" or "M".
I realise the potential conflict because two sets of queries will be looking to gather values from field "M" on "ox". That is why I am hoping when we look for value for field "O" in "oxno" we only look for numbers with one non numerical field in it (usually a "/") .
After finishing writing this up, I realise how wordy this has become appologies for that, but hopefully someone will be able to understand what I am looking to achieve and will be able to suggest a solution.
I have around 200 files, all named from 001.xls to 200.xls, all containing the same format, the same number of colums, but a different number of lines.
Now I have the great task to actually open all these files and copy each files data into one file and start analyzing it. Well, since I personally have no interest to open, copy and paste 200 hundred times, I am wondering if there is any faster way to let excel automatically copy the data into this master file?
I am thinking about somehing like *='001.xls'Sheet2!B2:E20*
One thing is that each file never has more than 20 entries, thus i would only need excel to import the from line 2 to 20 from a certain file and then do same thing all over again with the next file.
trying to insert rows into certain sections of a large data set.
Currently, there are over 25k rows of data and I need to insert individual blank rows between certain lines. The process is too daunting to try and do it individually (CTRL+Click row number) so I need to find a way to do it based on a specific number in one of the columns.
I can insert individual rows after filtering the data to only show the rows that need blank sets under them
I've included a sample to illustrate my point. There needs to be a blank row before every row with a "1" in the "count" column.
Data Sample.xls
In need of a macro to change the look of the attached example spreadsheet.
View 3 Replies View RelatedI have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I have a Pivot table which is created by running a macro with formula calculated columns.
Q1. when i add another column in data sheet, and change the source manually, i get a error in calculated columns
Q2. I need to add the new column in data sheet to the row labels of the existing pivot sheet. with change formula calculated columns.
Q3. can we have any macro to this ?
I have an application that updates the text string in A1 every x minutes (time may vary). I'm trying to develop a toutine that will trigger a Macro/call a function/subroutine once only on each change of data (and then not trigger again until the next change...and must only trigger on change of data in this specific cell). So the timeline looks like this
start A1 = "Apples", Fire macro once
start + 10mins A1 = "Oranges", Fire Macro Once again
start + 25mins A1 = "Pears", Fire Macro Once again
etc.
Triggering the macro is no problem but constraining to one triggering is proving a problem (for me anyway) and is really holding up my application progress.
I have a table with 6 columns (with headers A -F) and 10 rows. There are values in each cell. I want to take a value from each of the 6 columns and concatinate them. I would like some code that would generate every possible resulting string and I would like the possiblities set out in one column.
Apart from the fact that it might be tricky enough to produce the code , by my calculations there will be 10 to the power of 6 possibilities and as far as I know there are only 65000 rows in my version of Excel. If they had to be put into 2 or 3 columns I would not mind
I have the below spreadsheet and require to format it depending on what appears in column AE and column N.
example if in AE it says AAA and the figure in column N is >=-1 and Microsoft Excel - Belgium - Jupiler League.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAB3AC3AD3AE3AB4AC4AD4AE4AB5AC5AD5AE5AB6AC6AD6AE6AB7AC7AD7AE7AB8AC8AD8AE8=
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE2DateHomeAwayFTHGFTAGFTRHTHGHTAGHTRHDA51GoalsHDA17GoalsHDA7GoalsWHHWHDWHA51177*303/08/2007MechelenAnderlecht01A00DA-1.02A-2.18A-1.715.53.51.534.54.54.5AAA404/08/2007CharleroiRoeselare11D10H*****H2.00H3.291.623.45-1-1-1HH504/08/2007Club*BruggeBergen21H11DH2.65H2.47H2.001.3647.50.360.360.36HHH604/08/2007DenderGerminal12A00D***************2.883.22.2-1-1-1*
704/08/2007FC*BrusselsWesterlo01A00DA0.76D0.94H1.712.23.22.881.2-1-1ADH804/08/2007GenkCercle*Brugge31H31HH2.31H2.24D1.001.3348.50.330.33-1HHD2007-2008*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How can I put in a formula which will look at the contents of cell A1 and B1 and then if the contents of cell A1 match you put in the contents of cell B1 into B2. I know this sounds confusing so let me explain cell A1's contents are 1 and B2 has the amount of $312.50 so if cell A2 is also a 1 then I need to put in the amount of $312.50 into B2, same would go for B4 since A4 matches the contents of A3 I need to put $625.10 into B4....
View 9 Replies View RelatedI have a file with thousands of records and on the name field, I have case like this:
1 "John Dough"
2 "John Dough "
As a result, when I pivot the data, I see duplicate lines for the same name. How can I eliminate the extra spaces at the end (row 2 and 3 above)?
I have 4 employees by 52 weeks tabs in a workbook. The first 4 tabs are as follows:
Pat 010810
Dan 010810
Max 010810
Ann 010810
I would like some method (maybe VBA?) that I can rename the subsequent tabs, having the same names but the date with a seven day increment; i.e.the next tab would show Ann 011510, etc.
I don't know how to code, but can get to the VBA page and copy really well.
I would like to do a mass paste of this formula: =VLOOKUP(A23,A2:C9, 2)
With the only variable that changes along the way (A24, then A25, etc). But as I paste, (A2:C9), the table array, keeps changing as well, to A3:C10, A4:C11, etc.
How can I prevent this and only have A23 change as I paste?
I have a list of 2000 hyperlinks (http) that I need to download, I'm working on a corporate network so I can't just use a mass download client. Cany anyone give me some guidance creating a macro to save the target of the hyperlink as a file then move on to the next hyperlink until the list is exhausted.
I have a time format in one excel sheet, I need a macro or a formula to change it into a specific format, this is to be able to import it to another software.
I have attached a file here you can see an example, this I manually, I want Row D and E into the format in A, I s it possible to make it in a macro or formula, manually it takes a long time.
File Type: csv TimeFormat.csv
I have a multiple rows of data, but one column which is K has data like:
1020
1020
1020
1020
1020
1030
1030
1030
1030
1050
1050
1050
What i want my macro to do, is whenever that number in column K changes to copy all the data in row A thru K to a new sheet.
I am looking to find a way and synthesize code that will prompt the user for text data when he/she clicks a radio button, then place that text data in a "Notes" worksheet within the same workbook. Given that there are multiple radio buttons, with options of "OK" and "attention needed" (it is a checklist of maintenance items), I also need to find a way to have each note listed sequentially in the notes worksheet. My questions: Is this possible using macros? Can you steer me in the right direction? (change event, inputbox, then what?)
View 3 Replies View Related=NORM.DIST(-0.7525,0,1,FALSE)=0.30057
I want to do inverse 0.30057 To get -0.7525 ,NORM.INV(0.30057,0,1) This function returns only cumulative distribution .
I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
I have a workbook with lots of analytic data from my company which I want to share with other people not associated with the company. Obviously I can't share private data so I'm wondering if theres a quick way to modify all the data I have with 'fake numbers' which means I could share it. With fake numbers no privacy rules will be breached etc.
Is there a quick way to randomly change all numbers by +/- 10-20%?
Lets say i have 2 columns A and B as shown below. I want to have a userform that has a list that shows the uniques in column A ie. 1,2,3 and a drop down next to each one that shows my options yes, no, maybe. If the user selects yes for 1 and submits the form i want all 1's in column a to have yes in column B.
A B
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
I am trying to separate a huge group of phone numbers from one cell into individual cells in a column. the numbers are currently separated by a semicolon. Is there an easy way to perform this action?
View 5 Replies View RelatedI'm trying to make a BMI (Body Mass Index) calculator. I've attempted it already but to no success. My first mistake was to read the values from cells in the spreadsheet which made things more complex than needed. So I started a new macro using a form instead for all data entry. I'm running into multiple problems with this
-Ask the user which method they prefer (see below formulas).
-Input the result into a certain cell.
English BMI Formula
BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703
or
Metric BMI Formula
BMI = ( Weight in Kilograms / ( Height in Meters ) x ( Height in Meters ) )
I have an accounts spreadsheet that I copy and paste customers names and addies into from the website back end sales information.
I do not copy e-mail addresses.
I have a mailto: with an e-mail address appear in the file in lots of places, it seems I delete it from some cells and it appears in others, my file is infested with the things now.
I can delete one by one, but this would take me weeks any ideas of how I can ctrl a select all cells and mass delete these things.
I am face with making a brand new accounts file which is a lot of work.
I am trying to do a mass import of several thousand excel files into one file. The headers of all the files are the same, but the data inside each file is different. I thought about using Access but it wants me to import each individual one.
I have zero experience in writing a Macro
I'm trying to convert some data which excel is reading as text into numbers for example I have:
[Code] ......
All the numbers I've imported from an app analytics company into excel which are greater than 1.0 appear as numbers (to the right side of the cell) and all the numbers less than 1.0 appear as text (to the left side of the cell). I want all the numbers appearing as text to appear as numbers so I can multiply them with other cells.
I've clicked format cells - number but this has no effect. I click format cells -protection - unlocked but this has no effect.
I have thousands of rows with all numbers less than 1.0 as text instead of numbers. What's the easiest way to mass convert them all to numbers seeing as the above options haven't worked.
I don't want to have to click through thousands of rows to edit it manually...
Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?
View 10 Replies View RelatedI have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;
=============
Part 1: 5x10 Red Cotton candy
Part 2: Yellow 6/18 x7 TTC x11 Picture Frame
=============
My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.
What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.
My question is there something else I can do that is easier and more accurate?
I generate a large dataset of X- and Y-coordinates. Togeter, these datapoints form random curves. So, in excel, I want to calculate the Y-coordinate of the center of mass of these datapoints.
View 3 Replies View Related