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.
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.
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
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?
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers() Dim managers1 As Range Dim names1 As Range Dim n1 As Variant Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
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.
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)
I need to compare two lists of client names. One list has 59k names to be compared to second list of 1000 names. The list of 59k are listed in column D and the other list is in Column E. I tried conditional formatting but unfortunately some of the names are slightly different I.e. fair point communications vs fair point communications inc. I was trying to put in v lookup with a trim function but it didn't seem to be working.
I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.
I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?
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]],"
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).
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?
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
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
I do not have any code for this as I am unsure if it is possible. I have a list of range names in a column (A) that refer to different sheets and in an adjacent column (B) I have a list of data. Is it possible to write VBA code that will allow me to copy each value in column B to the corresponding cell in the workbook that relates to the range name in column A.
With the following list on Sheet1, on Sheet2 I want list only the Names that don't have a value next to them:
Aaron Abigail 6 Alexander Alina 24 Allan Andrew 5 Ann
That sounds so easy to explain, but I am baffled as to how you can do it with a standard if function without getting the following type of result (this list could be up to 200 names - I only want ones that don't have a populated field next to them):
Aaron True Abigail Alexander True Alina Allan True Ann
Can this be done with a groovy IF and INDEX formula, or is VBA (of which I have very limited skills) the only way to go?
I have an excel sheet. In the first sheet is a list of about 200 people. Their names and surnames are written in rows and the list is in columns..
I want to make it so that in the second page, someone will type a name and excel automatically list the names and surnames of all the people in the list of first page.
I would like to count the number of time a name occurs in a range. I tried using a simple pivot table but it did not work because I may not always use all of the names from the list. Also I would like to avoid using the count if formula if possible. Is there a way to use a pivot table to do this?
I have a spreadsheet which has the following data;
Names Age Salary Fred 23 $19,000 Joe 35 $26,000 Alan 45 $7,000 Richard 32 $25,000 James 19 $16,000 Ian 23 $28,000 Michelle 30 $45,000
I'm trying to list the names only where their age is "xx" or if I choose a certain salary, obviously there are hundreds of names, age's and salary information. How can I carry this out in excel ?
I have a few names in the range A2:A11 and the exercise asks: Fill in the cell B19 with a temporary software name. Using the appropriate function, compose such name as follow: take the first letter from the 1st, 2nd, 3rd names of the list; take the third letter from the 4th, 5th, 6th names of the list; take the last letter from the 7th, 8th, 9th, 10th names of the list. Moreover, the software name must be in capital letters.
I created a validation list that includes the names "Mary, Joe, Michael, and David"
When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.
Passport Mary Joe David
Lunch Mary Joe Michael
Car Joe Michael David
I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.
I have 1 column of people (LASTNAME FIRSTNAME). I was looking for a way to add a comma after the last name instead of just a space. i.e LASTNAME, FIRSTNAME. Is this possible?
I have a workbook with a collumn full of football championships. As you know there are hundreds of different championships names. And i have a collumn with all of them but some are repeated over and over because this collumn is connected with values of profit/loss in another collumn. For example: Blue Square North (Collumn A1) and in collumn B1 1.75; then Blue Square North (Collum A2) and 2.98 (Collumn B2), and over and over...
So what i need is a formula that retrieves all unique and different names in a range collumn and the puts all the dfferent names in a new collumn (lets say C). Because what i intend to do then is to use vlookup to search for the championships one by one (through collumn C) and count the ones with profit and them loss by the name of each one.
So what i need is a way to retrive a list of the unique names in A and get them in C.
Got a huge dataset with a list of non-repeating names. For example, among the dataset is about 1,500 names that dont repeat itself in a column. How can I isolate just that list of non-repeating names from that huge dataset?
In the Attendance Sheet (file attached), I have a list of all students. I like to add students who attend (with "X" marks) each subject onto Sheet "All Records). Date, time, location, instructor, subject to be added respectivley and in accumulate to the Sheet "All Records". I remember I did this before but I can't now.
I have a column of random names, say 200 of them in column A. I want to have column D show a name if (and only if) it appears more than once and have a count of how many times it appears in column E.
