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.
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
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....
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 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 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 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?
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'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 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.
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.
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.
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?
I 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.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency Copy and Paste Special > Values only Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
Does anyone have a workaround for the following error "Excel Could Not Save All the Data and Formatting" Error Message (http://support.microsoft.com/kb/215783). I am applying validation to many, many cells
I wasn't sure how to properly title this post. I actually have a couple of questions. The main one is on how I can code my VBA so that for the range A11:N80 I have specific conditional formatting code, and then for O11:AB80 I have different conditional formatting code, and so on for 12 sections.
The conditional formatting reads column L (in the first section) for one of 6 different words and depending on which word is read, it fills the row from A to N with the corresponding color. I have this working, but I do not know how to do the same with with section O11:AB80 and so on. The actual formatting within each section will be the same but the data is different so different lines will be filled in. Here's my code so far:
I have a userform set up to create a new row on a worksheet and enter data that was filled out on the work sheet. Some of the data comes from text boxes which put in name, rate, division, received date, and loss date. The other info is taken from checkboxes and designed to put either an X or N/A, or just leave a column blank. Basically, what I want to do is figure out, when I make a new row, how to format that row to have borders around each cell, make columns 6-29 bold, and the cells in columns 6-29 colored with a gray background. If you need a dummy worksheet to better describe what I'm looking for, let me know. I haven't made one up yet.
