Names Replace By One Singular Name
May 27, 2006
I want to be able to have a set of names replaced by one singular name, e.g.
Hilton monkeys
hilton monkeys on top
Hilton Power
Hilton jimmy
Hilton jimmy on top
Hilton jimmy on top with bananas
Marriott jimmy
Marriott jimmy on top
(ofcourse these are made up!). What I would like is to group the names to simply 'Hilton' and 'Marriott'. There must be a way of doing this, so I don't have to select all the separate cell numbers. Is there a simple way. I have tried using replace in Excel and that doesnt allow me to do it. Macros must do, but I'm trying to avoid having to write 5000 scripts selecting each particular cell. Is there any way of doing this?
Surely the 'replace' function should do it with some key character like replace hilton * with Hilton??? Thats all I want, but there doesnt appear to be that option.
View 9 Replies
ADVERTISEMENT
May 14, 2009
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?
The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.
View 11 Replies
View Related
Jun 12, 2014
Macro that will search Column A for Yellow Cells. If it finds a Yellow Cell it will then check to see if the row below it has any value.If there is not value in the cell right below it then it will Delete that entire row that the Yellow Cell is on and stop the program. If it finds a value it will then continue down the column looking for another Yellow cell. The reason for this macro is because I have another macro that is deleting duplicates and it sometimes leaves a Yellow Row at the bottom of my information.
View 2 Replies
View Related
Apr 23, 2014
I have a VBA find and replace question
I have a spreadsheet with cognitive tests (approx 300) that have been given names like
w3_th_e
w4_th_e
dkefs_TMT1_e
pegboard_eh_e
What I need is a VBA macro that replaces the _e only at the end of the string to _ss for those strings that begins with w3_, w4_ and DKEFS_ (e.g. from w3_th_e to w3_th_ss)
Likewise I want to replace only the _e at the end of the string on all other names (e.g. pegboard_eh_e) with _t (e.g. from pegboard_eh_e to pegboard_eh_t) Is this possible?
View 4 Replies
View Related
Dec 18, 2013
I have an email list that I email. Sometimes I get some that doesnt exist anymore. So my program sends email out and respnods to me that says these emails do not exist and gives me the emails. So I manually have to go and find them one at a time on my list to delete by CTRL F. Is there a way to submit all email names to find at once instead of doing one at a time.
View 9 Replies
View Related
Jan 20, 2009
Have a long list of patients with visit details recorded - over 3 years worth
Many patients are seen multiple times, there is an age recorded also to ensure the same names can be 'set as distinct for different ages'
Need to replace (or add another column) the name field with a unique identifier so that we can ensure the same person has the same identifier.
View 9 Replies
View Related
Sep 13, 2010
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
View 9 Replies
View Related
Jul 7, 2007
I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?
View 9 Replies
View Related
Jun 14, 2008
I want to replace all defined range names in the sheet that start with "Street" to "Road"
For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"
I want to change them all in vba to Road-01", "Road-02" etc.
View 12 Replies
View Related
Feb 5, 2009
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.
View 6 Replies
View Related
Jul 2, 2013
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?
View 4 Replies
View Related
Feb 5, 2014
[Code] .....
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?
View 6 Replies
View Related
Apr 20, 2006
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.
View 3 Replies
View Related
Jun 15, 2006
I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.
View 2 Replies
View Related
Mar 18, 2009
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.
View 2 Replies
View Related
Jan 13, 2014
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies
View Related
May 7, 2012
Can I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies
View Related
Oct 15, 2013
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
View 2 Replies
View Related
Jan 4, 2013
I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
View 4 Replies
View Related
May 1, 2008
Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
View 9 Replies
View Related
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Aug 26, 2008
I am using Excel 2003 and Windows XP.
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
View 9 Replies
View Related
May 22, 2013
I'm wanting to use Excel's built in replace function to replace ANY date with "Call:"
I'm not going to go into details about why, but I cannot use a code, as I only want to change them at specific times.
The dates are currently formatted as 12/09/2009. So I need to change the 12/09/2009 and any other date there may be to "call:" without having to go through every possible date.
View 9 Replies
View Related
May 18, 2009
I have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information.
I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.
View 9 Replies
View Related
Sep 1, 2009
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?
View 2 Replies
View Related
Feb 24, 2014
I'm trying to sort out a list of names from a website that publishes names in the following format:
DOE John
VAN GOGH Vincent
DA VINCI Leonardo
NADAL PARERA Rafael
JIMENEZ RODRIGUEZ Miguel Angel
What I'd like to do is get the names in the following format
John Doe
Vincent Van Gogh
Leonardo Da Vinci
Rafael Nadal Parera
Miguel Angel Jimenez Rodriguez
Basically all the last names - which are all capitalized - would be moved to the end of the text string. Of course any leading spaces should be removed and I guess using the Proper() function, all capitalized words could be capitalized in a standard way.
I found the following function, here: [URL] ...
but what it does is just take the capitalized words and separate them into a separate cell, which is not all of what I want.
View 1 Replies
View Related
Jan 22, 2014
I need to create a function that selects 4 names randomly from a list of 15 names and displays the 4 randomly selected names in the one cell. Also, you cannot repeat the same name in that cell, (i.e. bob cannot be selected twice in his group of four)
View 1 Replies
View Related
Dec 10, 2008
I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________
Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub
View 9 Replies
View Related
Apr 2, 2008
Here's what I'm trying to do:
In a spreadsheet I have a series of names with associated data, for instance: ...
View 8 Replies
View Related
Jan 22, 2007
I used the following code to replace " characters to !%. Then I copy the selected cells, and replace the !% back to "-es. The copied cells contain the "-es. When I comment the second replace, the function is working correctly, the copied cells contain the !%. What's the problem of my code?
Selection.Replace _
What:="""", Replacement:="!%"
Selection.Copy
'Selection.Replace _
' What:="!%", Replacement:=""""
View 2 Replies
View Related
