In column A of my worksheet I have a list of names (aprox 300) that are shown in the following example..
John Smith
Aron Jones
Sue Hisplof
etc etc
Is there a formula that will place each of the Surnames only in the adjacent column B...so from above list column B would display...
Smith
Jones
Hisplof
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
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.
I have file that may contain about 100 or more rows depending on the request. In column A, i have several names that is seperated by comma in ever row so basically what i want to do is to remove each name and insert a new column and paste the removed name in the new column and keep doing that until only one name is left for each row. I would like the code to check every row until the last row of the file and perform the splitting method that i have mentioned above. Pls. note that some row may contain only one name in this case we don't need to copy and insert new column but if the row contains more that one name then i would like to split it as shown below. I have attached file for details. I really appreciate for your help.
Here is an example of the current issue
Mike, David, John
Eric, Dave
Joe, Johnson, Edward, Ellen
Thompson, Adrienne.........
I have and issue where I have name in one cell and need to separate them into 2 cells. Trouble is it is a combination of single names, husband and wife, and partners with different surnames. Sample of data is below:
Belinda Smith & Grant Jones
Janine
Ken & Marie Smith
Louie & Betty Johnson
Naomi Thomsoon & Craig Brown
Narele & Barry Day
Nicholas & Julie Smithson
S M Bole & Co
Jim Morris
Danielle & JohnSimpson
Is it possible to copy specific names based on cell content?
For example:
If I have a cell containing text and the names Bob, Mary, Bill,
I would like have a macro read the contents of the cell, find each name "Bob", "Mary", "Bill", then write their names in the first available cells to the right.
ie: If Cell A1 contains Bob, Mary, Bill
write the names in the next available cells ( in any order)
Cell B1 = Bob
Cell C1 = Mary
Call D1 = Bill
It would be great if this can be done within a range..
If anywhere A1:A100 contains the names Bob, Mary, Bill,
to write the names Bob, Mary, Bill ,in their next available cells.
Also, I have a few dozen names that i'll be incorporating in my worksheet which I'm hoping to expand on.
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 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 RelatedCan I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies View RelatedIs 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.
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?
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.
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)
View 1 Replies View RelatedI'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).
View 3 Replies View RelatedI 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.
View 2 Replies View RelatedI want to split the dropdown list taking only the first 3 characters to populate the cell.
(An example will be a drop down list with countries eg
GB Great Britain
ZA South Africa
I want to populate the cells using GB and Za only.
DSee the attached.
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.
BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).
I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.
I have an excel sheet with 30 names in column A and I'm asked to do the following:
Write a procedure – a sub – that uses an input box to ask for a name. The procedure should then scan through the names in the list to search for the name and make a message box to state “X is not in the group” or “X is in the group”, where X is the name from the input box. HINT: You can make a variable “found” that starts with the value 0 and gets the value 1 if the name is found in the list.
Im still pretty new toi VBA and im struggling with pulling data from another worksheet and placing it into a list. Below is the code currently I think I have identified were its not working but not sure what to do. the code should essentially do the following
on opening the workbook check CS PS data worksheet find the names of people within the sheet, These start at row six and the step between each is 82 it then should input these names one after another in the following range Range("AO3:AO43") one name per cell until the end of the dat,a it seems to run but only inputs the first name in the first cell even thought it picks up each name in the myformula piece
Within the myformula there is a concatenate piece to flip the names to opposite way round I think this is where is not working possibly something to do with the separators? each name is in the format surname, firstname it should flip it so it shows first name [space] last name
provide ive commented out the piece I think is the issue
Sub CSupdate_()
With Application
.Calculation = xlManual
.ScreenUpdating = False
[Code]...
I have an issue with being able to process my data i have a text file in notepad that lists a massive amount of values (enough for excel to process if they are listed down a column but not across a row) each separated by a space. As an example here is a small portion of the data.
81768102 191193210 386225426 110858190 393958997 21773704 22450052 70617438 843133051 103582830 370163346 819494826 109538724 846339187 19638405 50748904 476397524 128490548 134215188 252862729 387318907 82658728 15822910 199255054 172623979 59872284 773581712 124854321 547098635 604524102 45265054 203132867 225629848 215828319 14779508 300950341 715797961 329121584 366323012 583555062 917794380 216847744 784432795 606179111 537865871 500392632 37701513 830010548
I know how to import this as text but i can only wither get it all in one cell or across the 1st row. The first row can only display about 16000 values and that isn't enough for my end product to be accurate enough.
So is there any way to import the text file and have it formatted so a each new value has its own row? For example...
81768102
191193210
386225426
110858190
393958997
21773704
Or is there anyway to get the data from my already imported giant A1 cell into the above format?
Added an example of how it looks and how i want it to look. This is how it looks when i open the txt file containing my data in a way that all values stay in the excel sheet. As well as this there is an example of how i want it or rather need it to look.
I have the list below and would like to create a new list which contains only names with corresponding values and lists them.
+ A B
1 James 3
2 Derek
3 Brett 6
4 Allan
5 Jess 7
6 Sam 10
7 frank 10
The solution should look like:
+ A B
1 James 3
2 Brett 6
3 Jess 7
4 Sam 10
5 frank 10
I have a task I would like some assistance with…
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?
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?
View 6 Replies View RelatedI 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.
View 3 Replies View RelatedI am creating an interactive tool in Excel.
One of the Fields is "Employee Name"
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?
View 3 Replies View Related