I have 2 columns to compare which has almost same data but in different formats or sources. I need to update a 3rd columns as 'Yes or No' by checking the checking the data in both the columns. For ex. - Column A has data as - "BIENZ, STEPHEN R"
and Column B has data as - "SALTZER MED GRP/BIENZ". Now as 'BIENZ' is same in both columns so 3rd column should get updated as 'Yes'. There are 3000 rows which needs to be compared in this way.
I need to compare the third octet of two ip addresses to make sure they are identical. In case you're not familiar with what i mean:
ex 192.168.1.2
192.168.1.3
I need to make sure that the red part is the same. So, I need a function that will only look at this part of the ip address and then tell me true or false if they are the same or not. Each ip address is its own cell so I am only comparing two different cells.
how can i convert a column of hyperlinks that i have to the actual addreses. for instance, i may have in one cell ebay that would link to "www.ebay.com". i would like the cells in a column, or if not another column to display in text the actual addresses and not the word, if that makes sense. can i do this via worksheet formulae or is it in the realms of vba?
View 2 Replies View RelatedI have used a formula in a cell, the error option button appears at the corner of that cell, it is working well but showing Inconsistent Formula
View 7 Replies View RelatedI have my Excel set up for a default font size of 12 point.
I often download CSV data to insert into spreadsheets. It opens in a new spreadsheet, and it's properly displayed in 12 point size. When i copy and paste it into the ultimate target spreadsheet (which is also set to 12 point size) the pasted data shows up as 10-point and I have to change the font size back to 12-point every time.
Why is this happening and how can I avoid that re-sizing that occurs?
I want to stop those little green error checking markers in the top corner of cells from appearing in my spreadsheet. My formula is correct however the error markers appear in 40(ish) cells and make it look very untidy. I know there is the option of turning background error checking off, however isn't appealing to me as I have other worksheets where I want to check for errors. Also, my report is used by 20 people so I can't change the options which might affect other reports they may have.
What I'd like to do is either turn off the option for one specific worksheet, or add a few lines of code which sets the cell properties to ignore the error for each of the cells affected. I've tried to use macro recorder to work out how to ignore a cell but it doesn't record anything for this action. Is anyone aware of the correct piece of code to carry out this action?
How figure out how to drag down a formula that looks like this for the first three cells (C3:C5)?
=(SUMPRODUCT(Polls!D2:D5,Polls!B2:B5))/(SUM(Polls!B2:B5))
=(SUMPRODUCT(Polls!D8:D11,Polls!B8:B11))/(SUM(Polls!B8:B11))
=(SUMPRODUCT(Polls!D14:D17,Polls!B14:B17))/(SUM(Polls!B14:B17))
...etc.
Is there a way to let Excel know that I'm adding six to each cell, rather than it automatically adding one?
I am using Excel 2002 and trying to do something which should be simple with MATCH.
I have an array of numbers ordered, starting at 0 and incrementing by 0.05 up to 20. I am then using the MATCH function to find the relative position of numbers 0,1,2,3,4 and 5. However, for number 0,1 and 2 the return value is one less than what it should be, for the numbers 3, 4 and 5 it is working as expected. By the way, the match_type I am using is the default 1.
Also I have formatted everything to be numbers and tried various things already, so am beginning to wonder is this is a known issue.
OK I feel really stupid on this. I must be overlooking something very obvious.
If you look at the attached userform there are some checkboxes where the "check" has a bold appearance, and others where the check is not bold.
If you examine the properties of the checkboxes in VBE, they appear to be identical.
I have to pull data from a workbook poasted on a Sharepoint site on a weekly basis. The name of the spreadsheet changes each week, based on the date, and may not be consistent (ie: WBook12_4_9.xls, Wkbook12_04_09.xls, WBOOK12_4_09.xls). My users will have the spreadsheet open, and I'll tell them to ONLY have that one open, but how do I tell my macro to pull from that workbook?
View 10 Replies View RelatedI have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquery
Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST
I need a formula to extract the stock price '2699.00' only from this text.
The problems are sometimes its may change to underlying "stock" to "index", SBIN to RELIANCE or TATASTEEL, and the price some times two digit say 16, some times 6 letters say 150.05, or 8 digits say 15160.00
Formula, the text in B1 and need the price at A100.
Problem:
I'm attempting to consolidate 3 columns of data that is a varying number of rows in length (a range of 0-1000 roughly) for 53 different worksheets (1 per week of the year, named '1' , '2', etc.). All three columns on each page are the same length. What I want to do is consolidate all the data onto one worksheet 3 columns wide and X rows long (so one week's range of data after the other, but it doesn't have to be in any specific order). I am looking mainly for code because that seems to me like the best option at this point unless someone can provide a viable alternative.
What I've tried:
I've tried on a smaller scale, 5 worksheets, naming the ranges using the OFFSET function to create a dynamic range and then trying to paste one right after the other with no luck getting it to paste such a large range. Going along with that I chopped and dropped some code from another online source (forum? tutorial? I don't remember) and modified it to consolidate the 5 named ranges, however, the code does not really seem feasible for 53 named ranges. Loops seem reasonable, but I couldn't come up with an easy way to cycle through the worksheet names and named ranges.
I only have a few hours worth of VBA tinkering so I'm sort of lacking, but I have other coding experience so the structures and concepts are familiar just the syntax and finer points are greek to me. I sadly don't yet have the time to sit and learn VBA either.
If there's an easy way to put a few loops in or you think it should loop through and copy each cell rather messing around with ranges then go for it.
Here's what the code looks like after I modified it: ....
I have a data set in an Excel spreadsheet entered as one column of data. It is names with addresses, phone numbers, job title, etc. I want to select and transpose each person in the list so when I am done the person's name is in column A, Company name in column B and so on. The problem is the information listed is not the same for each person - so there is a different number of rows for each person. The names are in bold text though, so I need to select from one cell with bold text to the row BEFORE the next row with bold text and transpose the data for each selection. I found this forum by finding an old question here that is similar at Copy based on Bold Paste-transpose provided in that post and it produced no result.
View 4 Replies View RelatedBasically I have some performance figures for a fund that I want to make a chart for, the problem is that the first date (the start date of the fund) is 15/09/2011 but every reporting date after that is the end of the month.
So my data looks something like this (not real figures)
15/09/11 20
30/09/11 22
31/10/11 23
30/11/11 25
31/12/11 26
31/01/12 27.5
29/02/12 29
So what I want is a graph where the x axis starts at 15/09/11 but then the next label is 30/09/11 and 31/10/11 etc, while still keeping the scale proportionate to the dates, ie the distance between 15/09/11 and 30/09/11 will be half the distance between 30/09/11 and 31/10/11.
The 2 outcomes I seem to be stuck with is either forcing the graph to start at 31/08/11 and making the y axis cross at 15/09/11, however with this the x axis goes left past the y axis cross point and it just looks dumb, although everything right of the y axis is correct.
Or secondly I get the graph to start at 15/09/11 and set it to label every 1 month and all the labels are 15/09/11, 15/10/11, 15/11/11 etc
I have access to excel 2003 or 2007.
I need to identify every 5th occurence of a product code (In column B)
the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51
So I would need to identify the one in Row 51 in the above example,
WorksOrderNumber
Product Code
DateEntered
Column1
56
80100811
01/08/2012 00:00
[code]......
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I'm trying to write a formula which will return postcodes from a list of descriptions which aren't consistent in their layout.
For example, I need this to happen
UB3 3NQ - APR13
SW3 5RQ - APR13
Jul 12 - apr13 accrual - ME9 4FW
Mar 12 - apr13 accural - SO14 7P2
Returned to another column as,
UB3 3NQ
SW3 5RQ
ME9 4FW
SO14 7P2
The issue I'm having is that the postcodes aren't in the same place in order to use LEFT, RIGHT or MID functions, and they aren't always proceeded or followed by dashes or spaces in the same way.
I need the returned postcodes to come back in a uniform way so that any duplicates are grouped by the relevant pivot table.
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.
View 13 Replies View RelatedI need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
I am trying to automate the below process:
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
========================================================
Private Sub OK_Click()
' Requires a reference to Microsoft Word xx.x Object Library
Dim sFile As String
[Code]....
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I want to say if the word 'suitcase' is in column W (any row), place the word 'suitcase' in column Z, same row.
View 2 Replies View RelatedI am in the middle of automating a process here at work, the program takes a word, "pencil" for example, from excel. It will then open up a word document with content already in it (premade template). The program will then find all instances of a string, "placepencilhere" for example, and will replace that instance with the string from excel.
Basically I want to be able to take a variable that has a stored string value from excel and use it to replace another variable in a word document.
I tried recording a replace (ctrl+f, replace tab) macro, copying the code, and inserting it into the excel vba code.. but I get a error message. Here is what I have:
---
I have a bunch of cells in column b that have products.
Column B
Dell 24" lcd
vaio sony laptop
8.0 mpxl kodak camera
photoepsonprinter
Basically in Column A I want a formula that'll say.
If the word "dell" is somewhere in cell b1, then put the word "Dell" in cell a1.
If the word "sony" is somewhere in cell b1, then put the word "sony" in cell a1.
And then so on and so forth down through column A. The brand names are potentially endless, so is there a easy way to on like a seperate sheet make a list of brand names and have it pull from there?
Originally I made a if function that said if the brand name was contained in the cell then output the brand name, but that caps at 7 for the amount of brands i can use....
My cell to cell tab function is not working properly in Excel (but works in other programs like Word). Instead of moving one cell to the right, such as A1 to B1, when I hit the TAB key it moves from A1 to T1, then to AM1. This is the case in EVERY excel document I open/create on my work computer (but all other computers at work do not have this problem). The documents are NOT protected and the cells are NOT locked.
View 9 Replies View Related01. I want to Type "Al Raha Pharmacy" in a cell
02. When I type "Al Raha" and I press space, then the word automatically changed to "Al Rahma"
03. I tried this in other cells also but the result is same.
