I am trying to do a find and replace on two list of names which are in different formats to get the list in a correct format for processing in our system.
Eg
Sheet1 - Column A - (Has Duplicates)
Tom Jones
Homer Simpson
Sheet2 - Column A - (unique values only)
JONES TOM I1234
SIMPSON HOMER I4321
I want a macro/method of looking at Sheet1 Col A - finding the EXCAT name in Sheet2 ColA and then either Replacing Sheet1 Col A with the Text in Sheet2 ColA or putting it in Sheet 1 ColB next to the one i need to replace.
Desired Example
Sheet1ColA || Sheet2ColA || Sheet1ColB Or Sheet1ColA
-------------- || ------------- || -----------------------
Tom Jones || JONES TOM I1234 || JONES TOM I1234
I have Approx 4187 names to check like this and change to the same format as is in Sheet2 Col A
So a looping macros that looks at sheet1 col A and then finds the name in Sheet2 Col A i think is required, if it does not find a matching name it should leave it BLANK and then move on to the next one to find and replace.
I have tried to split up the cells in Sheet1 ColA into FName, Lname..then doing Vlookups, but thats not working for me
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and Find all occurences of With or WITH and replace with with. Find all occurences of Or or OR and replace with or
It's been a while since I've visited MrExcel, but I've been busy learning C# & ASP.Net & Visual Studio & SQL Server - work doesn't want too much from me :p
Anyway, I'm writing a procedure that plays around with lists in Excel (i.e. the type of list that would show up in CurrentRegion. They're not connected to any outside data source, or as a pivot table - just columns of data that will have a header row in a different format than the data body.
Is there any way of cycling through each sheet in the workbook and identifying each occurrence of a list?
I've been playing with the code at the bottom of the post, and it finds the boundaries of each list providing (at the moment) that each list starts on row 1. There are other problems with it however.
And to identify the values in the header row? .....
I have a sheet with many columns of data relating to different companies as shown below (obviously my actual sheet has much more data):
NOTE: the Mr. Excel forums display the data in the correct form. I've tried to fix it here but obviously that didn't work perfectly, so if you want to view it correctly, click on the link above.
Comp Name Facility Name Product Name clean date Comp A Plant 1A Prod A1 11/11/2008 Prod A2 Prod A3 Prod A4 Plant 2A Prod A1000 12/1/2008 Prod A1001
Comp B Plant 1B Prod B1 12/5/2008 Prod B2
In a new worksheet I want to see only the information relating to the Facilities, specifically the clean date of each facility, such that the information looks something like this:
I know that I can use a simply vlookup formula such as the one below: =VLOOKUP(C21,Info,3,FALSE), where C21 is the Facility Name and "info" is a list of the last 3 columns of my initial datasheet. However, I need to remember add each new facility name to the 2nd list when it is added to the main datasheet.
Is there a way to have the 2nd sheet automatically add the new facility name every time I add one on the main sheet, but without showing the many empty rows inbetween?
I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two. I am using this formula currently, but is returning an incorrect result-- =INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)
this is the record number in M2 BU1HAD80
it is returning BI1METBRSM as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions
A stab in the dark here, but is there any way to be able to 'jump' to a particular position within a filtered list. My list has around 20,000 items in it and I'm having to go through each one systematically to make edits on the relevant line and it is so laborious trying to find the correct place in the list every time.
Is there anyway to be able to may be key in the first letter or two letters to avoid all the continuous scrolling to find the relevant entry?
Is it possible to find the minimum distance between two points using two separate lists of different lengths without creating separate columns of equal row sizes
? I am trying to find the shortest distance form one point to another which is a fixed value. I am using the standard distance formula: =SQRT(((X2-X1)^2)+((Y2-Y1)^2)+((Z2-Z1)^2)).
My X1,Y1, Z1, has 2,000 rows in three columns that need to be compared to find the minimum distance from my one row/column of X2,Y2,Z2.
Is there a command that could go through and use each row of the X1, Y1, Z1, while looking for the MIN of the above formula without having to create an equal amount of rows for the X2, Y2, Z2?
I know I could do it by running this out the long way and then finding the min after I do all the math but I am wondering if there is an automated way for Excel to go through and do the forumla and then only return the MIN value?
I was wondering if it is possible to do this unique find and replace that is explained below.
I have many columns with data from our database. I have one column that has my html layout in it. I want to find within the html text data and replace it with data from another column for that row.
Example: We have [[manufacturer]] in the html area that we want to replace with data from our column called Manufacturer. So, it will replace the text in the html named [[Manufacturer]] with that rows Manufacturer data we have.
I have around 10 names which I want to replace with their code names. For Example, "Alan Richard" needs to be replaced with AR001. Problem is I don't know cell range and this name might appear more than once in the data.
I am trying to find and replace #N/A from a formula (link to another sheet) with nothing in the cell, using VBA.
Using this code it will do the first "find" Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True, SearchFormat:=False).Activate ActiveCell.ClearContents Trying this Cells.Replace What:="#N/A", Replacement:=" ", LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _ ReplaceFormat:=False LookIn:= is highlighted and I get a "named argument not found" Don't understand that because LookIn:=xlValues works in the first code.
I did try and place After:=ActiveCell,before this, but that does not work. I Get the same error.
I have more then one cell with the #N/A which I would like to get rid of. I want this to work only on this one sheet. The other sheet that the formula is pulling from does have #N/A, and that is okay, I need it there.
I have a list of sales associates that are assigned numbers. For example, John Doe is 1022. The data I have to work with only shows his number 1022 but for my reports management wants the name John Doe. I have about 60 sales associates and have a VB code that 'finds and replaces'. The code is huge. It has slowly grown out of hand, with more sales associates added daily. I also have 22 sheets in one workbook with specific columns to search for the numbers to replace with the names.
This is what I would like to do. Have a sheet named associates, as more associates are added I would just have to add their name and number. Have two columns with headers column A - Associates column B Associate #. Name ranges for the two columns and write a code that says look in the sheet associate, at named range 'numbertoname' and if the current sheet 'total sales' I am in has the number 1022 in column C replace it with the name John Doe. It seems like it should be easy but.......I have tried and tried today.
I am facing a strange problem with Find and Replace function of Excel. I converted a PDF document into a Word document. I copied the tables in the Word document to an Excel document. As the data had many ‘*’ characters, which I did not want, I used Find and Replace function to get rid of them. However, to my surprise, two things were happening.
1.When I tried to replace ‘*’ in a cell with ‘ Construction revenue ********************** ’, it was deleting all the contents of the cell. 2.When I tried to find all the cells with ‘*’ , it was selecting cells even without ‘*’
I tried various things like changing the format, pasting only values..etc, but nothing worked. Finally I had to use the Replace function of VBA to do the task. For further analysis I am attaching my Excel file with that data.
In the attached file I want to do a Find/Replace for 599 --> 599.0 and 428 --> 428.0. When I do a F/R for each it tells me it has made 3 and 5 replacements respectively but the data is not changed?
HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search?
I'm using Excel 2007 and trying to replace about 2000 commas with full stops. I want to use find and replace but keep getting a message saying that Excel cannot find the data I'm searching for.
Excel help suggests I haven't clicked on "find" before clicking "replace" but I did. I've tried highlighting the column in which I want to make the replacements, but same message.
Also tried highlighting nothing, same message.
Tried copying and pasting the whole thing into a new file, but same message.
The Formula bar shows: ="01/01/2009" The cell presents: 01/01/2009
I need to remove the equation sign as well the two inverted-commas.
I am familiar with the "Text to Column" feature, the use of SUBSTITUTE Function, a short macro and also the Find&Replace is also a good idea but, as far as I understand, it must be run Twice(!)
I am looking for a way to use Find&Replace in "One! shot" - meaning, to put the TWO different(!) characters [ the '=' and the " ] in the 'find what' window, leaving the 'replace with' window empty.
I know that something similar to that can be done in a "Word" document - but can it be accomplished in Excel?
I have two groups of numbers. The first group is x amount of rows and each row has 6 numbers. The second group is x amount of rows and each row is 7 numbers.
Each number from the first group needs to check to see if it exists in the second group. If it does exist, the cell should change to a highlighted cell, or switch to an 'X' or something else that identifies it has been matched. Note: Numbers may be duplicated in both groups. Is there an easy way to do this, or will it involve programming? I'm attaching an example of the "group" setup.
I have various formulas (such as: =(D3-$E$3)/$E$3 ) which continues for each row (IE next row 3 is replaced with 4 etc)
I have made som errors when I made the formulas and therefore I have the following questions:
1. Is it possible to find and replace all E's with another letter (F in my case). I haven't been able to resolve this, when it has to be done inside formulas? The optimal would be if I could set 'Display formulas' under tools, and find and replace in the formulas displayed.
2. Is there a smart way of swapping D3 with E3 for all formulas? (So the result will be: =($E$3-D3)/$E$3 and etc for all formulas)
I need to do a find and replace on cell E13 on every worksheet in the workbook - a several hundred pages. The sheets are individually named not Sheet1, Sheet2......
First of all I need to find say "oldchippy" (without quotes) in cell E13 and if its there replace it with "oldchippy2", if its not there leave the contents as it is.
Is there a way in which you can use two cells (a2 & b2 for example) with a button to trigger a macro which will search for a2 and replace with b2 over a certain range of cells? unless there is a function to do it of course, which i would prefer!!
