How To Change First And Last Name When Person Has Middle Name

Apr 4, 2014

I have a worksheet with some names in a column with a persons first name then the second name and I would like to place the names in a different column I can have this to work ok with the following formula

=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))

This works ok but I have people with three names like Peter May Jones

When I have changed the name I would like to have (Jones, Peter May) but I get (May Peter Jones)

Also the colon I have after the first name gets put at the end of the name not after the last name.

View 12 Replies


Make Chart That Will Constantly Change As Different Selections Made By Person?

Jun 18, 2014

I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.

Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. What I mean as you open the example sheet and start messing around with the data validation boxes.

The problem I am encountering now, is creating a chart that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.

Here is what I am looking for in the graph:

1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.

2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.

3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.

View 2 Replies View Related

Last Date By Person

Jan 4, 2013

I have a sheet that tracks when people are paid. Each time a person is paid, another entry goes into the sheet with his/her name, pay amount, and date they were paid. So, a given person could have up to 50 lines devoted to them, each with a different date associated with it (the list is sorted by name).

What I need to do is figure out the most recent pay date for each person in the sheet. I would prefer to avoid vba, but experimenting with native excel functions has gotten me nowhere...

View 2 Replies View Related

Calculating Person Age In Excel?

Jul 25, 2014

I'm looking for a formula to calculate the age of some one. I used a formula but it gave me a #value! Error

20/05/1936 (E4)

View 4 Replies View Related

Returning The Person With The Highest Value

May 31, 2007

I have some data as below and I want to return the name of the person with the highest value.

Col A Col B Col C Col D
John Peter Paul Luke
3 4 7 8

View 9 Replies View Related

Random Item Per Person

Dec 19, 2007

I have a list of order numbers and sales persons associated with those order numbers..

I was trying to come up with an idea of how to enter a Sales Persons Name in a field and have a random order number based on their name populate so that I can audit that particular order.

Can this be done in excel with a vlookup formula or something to that affect?

And if so, how?

Order # Sales Person
256796 Judy
248986 Brian
897655 Fred
245466 Judy
364599 Tomas

Since Judy has more than 1, I would like to just radomly pick one of those... Now, just a little side information, my actual sheet has approximately 3000 orders with 52 sales reps...

View 9 Replies View Related

Summarize Results Onto 1 Row Per Person

Feb 2, 2009

I am trying to write a series of macros to automate the creation and population of a summary tab for transmission of patient results to our Laboratory computer system and have hit a bit of a brick wall. One of our analysers creates an Excel workbook for each analytical run with each patient sample analysed on the run assigned to a separate worksheet. These worksheets contain the patient ID (lab number) in cell C1 with results for the different analytes in cells F7 to F56. We need to be able to present this data with each patient having a row to themselves (i.e. Patient ID for patient 1 being in cell A2, test result 1 in cell B2, test result 2 in cell C2 etc, then patient 2 taking row 3 etc). Row one is populated with the field Sample name in cell A1 and the cross-referenced test IDs running across the rest of the row. As analytical runs can contain varying numbers of samples we need the Summary tab to be able to cope with a indeterminate number of worksheets.

View 2 Replies View Related

View Details By Person

Dec 27, 2007

I am creating a personal expense tracking sheet for the upcoming year. I have each day of the year going down as my rows. My column headers are various expenses I expect to have, such as a phone bill and internet bill. I want a way to pick which expenses (columns) are visible. I expect this to aid in the inputting of expenses and the reviewing of expense types. I know of course, I could just click all the columns I don't want and hide them, but I'm looking for a quicker way if possible.

So, if I want to record the phone bill, or just look at the phone bill expenses alone, I could use a drop down which listed all my column headers and check which one(s) I wanted to view. I thought of like the way a pivot table option looks to pick what you want to view /shrug.

Maybe a drop down is not the way to accomplish this, and maybe my spreadsheet should be laid out differently.

View 6 Replies View Related

Line Graph With 2 Lines Per Person?

Sep 15, 2013

I need to create a line graph that has 2 lines for each person, one with the amount, one with the points. The X-Axis is by Weeks going up. How can I do this without making a separate series for each? I've attached an example.


View 1 Replies View Related

Calculating Whether A Person Has Upgraded Between Lists

Feb 10, 2014

I am trying to write a formula that isn't a gigantic IF statement to calculate whether a person has upgraded their type of house or not within a time period.

The below are the names of the types of housing. I have two of these lists, the first is from the beginning of the time period and the second is from the end of the time period:

Pucca - Normal
Kuchha - Strong
Pucca - IAY
Kuchha - Medium
Kuchha - Weak

The best option on this list is Pucca - Normal and the worst is Kuchha - Weak.

A person can start at any option on this list and end on any option. I only want to measure whether their type of house has been upgraded (i.e. from Kuchha - Weak to Kuchha - Medium). I do not need to calculate whether they have stayed the same or been downgraded.

If the person has upgraded I want to display the value of 1. If they have stayed the same or downgraded I do not want to display anything.

An added complication is that if they have upgraded to Pucca - IAY I want to also do not want to show anything.

View 3 Replies View Related

Conditional Formatting Based On Person Name?

Apr 6, 2012

I have uploaded my excel spread sheet.


Viewing only displays half the written request, but I downloaded it, and it was ok

View 8 Replies View Related

VBA DATEDIFF Function To Calculate Person Age

Jul 9, 2012

I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:

Sub Test_click()
DOB1 = #7/19/1930# 'DOB
date2 = #7/18/2012#
Date3 = #7/21/2012#
age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82)
age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct)
End Sub

View 5 Replies View Related

Count Number Of X In Column When X Is In Same Row As Person Name

Oct 15, 2012

I have a spreadsheet that I want to use to plan projects across days and I want to assign a task to any one of four people. there maybe more than one task in any project that the same person may be assign to. Want I want to do is count how many assignments they have on that day. I've tried using things like vlookup, row(), hlookup and a various combinations of these with countif, or maybe dcount, I'm just not sure, but I think I'm finding it too tricky.

I've uploaded the sheet here..[URL] .....

So in other words how many tasks does person y have if the x is on their row. The person is a data validated drop down list so if the person changes from y to b then this will effect the total at the bottom.

View 2 Replies View Related

Min / Max And Count Of Entries By Sales Person

Jan 7, 2014

I have some data, similar to below which is sales data for each sales peson in my firm. The info I have is the name, the quoted value and the sold value. I estimate by the end of the month I will have over 400-500 entries so dont want to do this manually.

I have been able to use the sumif function to tell me the totals that each sales person has sold and quoted.

Now I want to work out the

min and max that each sales person has quoted and sold


The number of sales each sales person has had

Are there formulas to do this?

Ive looked at the min/max functions but I believe they will only give me the min and max for all of the quoted and sold whereas I want this by sales person.

I have also looked at the count function but this will only provide a count of the entire number of sales and quoted, again I want this by sales person.

Sales Person


View 2 Replies View Related

Searching For A Response From Specific Person

Apr 15, 2014

Is there a way to search responses from a specific Person? I think it was before the Mr Excel format change. Domenic was the responder.

View 2 Replies View Related

Pull Details Of Each Person From Another Worksheet

Sep 21, 2007

I have worksheet 1 which calculates a member's pension which is based on his date of joining a scheme, date of leaving, salary and yearly contributions paid into the scheme. Normally I would input these details manually but now I have a large number of members to produce figs for. These members' details will be on worksheet 2 with a separate row for each member's details. I want excel to process each member one by one and enter his details in worksheet 2 on the calculation worksheet 1 and put the results in worksheet 3. Is this possible? And how???

View 8 Replies View Related

Number Of Calls Made By A Person In A Month

Sep 5, 2013

The first column is "Person Name" and the second column is date in format "6/27/2013". I want a formula to calculate the total number of calls a person made in the month of June.

View 2 Replies View Related

Send Each Individual Person Their Own Data - Create A New Tab

Aug 7, 2014

I have a list of data which i need to send to individuals. I can sort the data by person's name.

I need to send each individual person their own data. Instead of copying and pasting each person's data into a new tab for everyone, is there a quick way in excel that for each change in person's name (Column A), that it creates a new tab with all the data?

View 5 Replies View Related

2 Person Can Open File As Compatible Mode?

Jun 7, 2013

I have a file is used by many person at the same time. Sometime 2 person can open it as compatible mode , when 2nd person save will write over the 1st person data, that means all have been done by 1st person will be gone, serious problem.

Normally open a file will tell me who have using it , but it show wrongly person although the person has not open the file. worse that 1 day only show 1 person name.

View 4 Replies View Related

Print Details Of Each Person With Background/Watermark

May 16, 2009

I have to print the admit card of each students with their details like Name of student, Fathers name, roll no., subjects, examination center years, etc. in a given admit card format. I had scan the blank admit card and used as a background image in excel but the image of the admit card does not print in excel except the cell values that I had entered.

I had made the database of all students details. I want to use the students database to print students data on the scanned admit card image. I also want a option to select the name of the student before printing (to verify the details). Please help me how to do it in excel. I am using excel 2000.

View 8 Replies View Related

Create A Formula To Pull The Person's Name And The Avg Number Of Cartons

Dec 5, 2007

I am trying to create a formula to pull the person's name and the avg number of cartons sorted for the month from this spreadsheet.

I've attached a sample of the spreadsheet I created, you will see at the bottom I would like to see the person that had the highest avg for the month and the person with the lowest avg for the month.

This is just a simple spreadsheet to track productivity of my sortation employees.

View 9 Replies View Related

IF Function Inform If Person Worked Over 6 Months In 2008

Feb 1, 2009

I have attached a worksheet that shows the last and first name of an employee along with their hire date and term date, if termed. What I am looking for is a function that will tell me if a person was active as of 12/31/08 which will read true on the side of their name under active; I also need a function that will tell me if a person worked over 6 months in 2008.

The first person John Doe was hired in 2005 and has a true on the side of his name which meant that he was active as of 12/31/08 but as you can see Jane Doe has true on the side of her hire date and false on the side of her term date and I don't want it to read the first true but the last true of someones name, but Jane Doe qualifies for working over 6 months even though she started in 2007.

View 7 Replies View Related

Time Calculation - Calculate Total Cost Per Job Per Person?

Dec 22, 2013

I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.

I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?

sheets.time chart.xlsx

2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here

View 1 Replies View Related

Search Formula - Lookup Person Name In Given Worksheet In Specified Cell?

Jan 15, 2014

How to create a search formula that will look up a person's name in a given worksheet in a specified cell?

View 8 Replies View Related

Finding All Occurrences Of Person's Name And Calculating Hours Worked

Jan 28, 2014

I'm trying to create a macro that will take name values from a list (Last name, First name) and then find these particular names in a long list of names in another sheet. Once all the occurrences of the name are found, the macro would need to add up all the hours that the person has worked during a given month. For example, I have Jane Doe and John Smith on my shortlist, the macro would have to take the name of the first person, Jane Doe, find all the occurrences of that name in the second sheet that contains names, dates and hours worked, then add up all the hours worked by that person and return the total hours worked value for that parson. Then take the name of the second person, John Smith, and do the same, and so on. Is it possible to do it with macros?

View 2 Replies View Related

Convert The Distance Jumped By A Person To A Specific Rank

Jun 25, 2006

I am making an excel file that should convert the distance jumped by a person to a specific rank. For example if the distance was greater or equal to 34, he would get a 10. and 34> distance >= 32, he would get a 9 and so on...

I need a formula that will should nest more than 7 functions, in this case it wont work right? So what I did was name 2 separate fuctions for example

View 11 Replies View Related

Random Select One Line Of Data For Each Person In A List

Dec 20, 2012

I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.





[Code] .....

View 1 Replies View Related

Get Rid Of Middle Initial

Jul 6, 2007

how can I do this?

Mike A Johnson
Tomas P. Carbone
Samuel L Jackson
Peter B. Lacrone

some have the period some dont.

im thinking I could just get rid of the periods first with a simple find/replace

so getting rid of just the middle initial ?

View 9 Replies View Related

Going Out Of A Loop In The Middle

May 16, 2006

I want to get out of a loop in the middle of it, with out going out from the whole procedure.

By the way, is there is possibility to send him to other location in the code instead of going out of the loop?

Do While ( Cells(counter, 5).Value = 1)
'some commands
If R(1) >= 0.83 * R(2) Then
'some commands
' here I want it to go out of the loop,
' but not to get out of the whole porcedure
End If
counter = counter + 1

View 7 Replies View Related

First Name-Last Name To Last Name-First Name Some With Middle Initials

Mar 8, 2008

An application I use at work exports names in a first name-last name format. At this point I have a list of about 315 names in this format. Is there a way I can convert these names to a last name-first name format? I need to export these data on a daily basis, so I'll need to perform this conversion every day. I did a search for this, but didn't find anything.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved