Formula To Create Name Initials Where Last Name Listed First
Aug 3, 2014
I am looking to create a formula to get the initials of a name, where the the last name is listed first, followed by a comma.
Now, the names could be full names or initials.
So, it could be something like: Smith, John R - result would be JRSSmith, John Robert - result would be JRSSmith, John Robert Michael - result would be JRMS There were similar formulas that I found here, but these only got the last letter of the last name - in this case the following formulas would have resulted (with the above examples) in: JTS or the last example with Michael, JLS.
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
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.
I'm new to the forums, though I've solved a couple of my problems by lurking here. I'm currently having a problem with a worksheet I've been working on at work. I'm not very experienced in Exel and have inherited a worksheet that keeps track of the machines we do maintenance on during the year. What I have now is a spreadsheet with columns alternating between initials and date, and rows with the machine names all the way down. It looks similar to this :
Name of machine Initials date initials date initials date initials date etc. machine 1 machine 2 machine 3 machine 4 ... machine 121
I'd like to keep this format, but we need to keep track of who does what within a certain week, which means I need to count the number of instances of an initial, then compare it with the date in the column next to it, which I don't think would be a problem with just two columns, but I'm having problems wrapping my brain around how to do so with 20-30 columns. I'm using date ranges in some of my calculation formulas, which I think necessitates the date being in a separate column than the text.
I have 2 columns of Surnames and Initials and I am trying to concatenate an initial (if required) with a surname or return a “blank” if the cell(s) is empty, in other words in cell AC20 I have a surname Smith and in AG20 an initial A which I only enter into the cell if there are identical surnames but when the cells are blank I wish to return a blank not a zero.
I have tried =IF( COUNTBLANK(AC20:AG20)=2,"",AG20&""&AC20) and =IF(AG20="",AC20,IF(AC20>"",AG20&" "&AC20)) but the first one won’t return the initial and the last one works but both return a zero when blank.
I just bought a database and I would say at least 20% of the names in the database have an initial after the first name. I need to have a clean first and last name list. Is there a formula that can delete the initials? I am working on a variable data print project that uses their first name in the message so I cannot have their middle initial.
So like in one example her first name is listed as Susan F. I want it to just be Susan. Another example is T. Jones as a last name, I want it to be just Jones.
Can anyone tell me if its possible to input some ones initials into a cell and when you hit enter, their full name is displayed instead of the initials:
ie input FB and the same cell displays Fred Bloggs?
I have QC checklist with a cell at the bottom for the user's initials. We have 8 users, and I would like to create a way that each user can enter their pin and have their initials come up in the cell. Ideally, there would be no way to the other users to know each other's pins or find out in the spreadsheet. Do you think it is do-able using VBA and how they would approach this?
I am trying to make up a workbook for a friend for their daily food intake. The formulas were working fine but she was hoping to be able to just enter the food and have the rest of the information automatically entered.
I was having no problem with it using a vlookup formula for the foods and a data validation drop down menu but when it comes to the final formula I am getting a #VALUE! error in that column unless there is a food listed.
I have attached to workbook. If you notice in the Sunday Tab I have the problem but Monday is fine since I have not added the vlookup formula. Is there a way to have it not show anything like in Monday when there is nothing entered?
i know by the formula =combin(49,6) how many combination of 6 numbers there are from a available 49 numbers (13983816).
how do i manage to get all combinations listed in a excel spreadsheet? I would want the numbers in individual cells. For example, Row 1 would show 6 numbers individually from A1 - F1.
I do realise that excel does not have enough rows to do this so may have to continue on other sheets.
I do have one issue I need to work on with a VLOOKUP formula.
I have a list of cities and counties that the VLOOKUP goes and looks at to pull information from.
The problem is that when there is a city and county with the same name (Example: Boulder and Boulder County) VLOOKUP always seems to jsut grab the one that is listed first in the list.
When you use the city name of Boulder, VLOOKUP always retruns the information for Boulder County.
Here is my forumula:
=VLOOKUP(qtProjAddress3,'City Mileage and Tax Rates'!A2:C518,2) Is there something I can add to this formula that will allow it to return the proper city or county when the names are similar?
I have a spreadsheet which is used for tracking work items for a team of people. On one sheet I have a column of dates and rows of named 'tasks' against them. I need to identify the first and last dates at which a 'task' is listed.
My spreadsheet is attached.
On sheet2 K2 I want to see the first / earliest date where 'Fish' occurs on sheet1 On sheet2 L2 I want to see the last / latest date where 'Fish' occurs on sheet1
I have tried using SUMPRODUCT MAX / MIN but I got myself in a muddle!!
The current method I'm using to get the results I need in Excel are a bit manual and excessive. is a simple all in one formula for what I'm trying to do,
The data is 3 rows. We can also assume the data is in the first three columns starting from A1.
698 885 499
As you can see in the above set from 0 through 9, the numbers that are not listed are 0,1,2,3,7 And As you can see the numbers that are listed are 4,5,6,8,9.
How do I find and show which numbers from 0 - 9 are not in the set. How do I show the number that are listed in the set Example: Note: the special characters aren't needed.
698 4,5,6,8,9 --- 0,1,2,3,7 885 499
Note the the evaluation is for every three rows. So the three rows in the example are from a table with 150 rows. Each row begins a new evaluation for the next three.....Example:
698 and the following two starts the next set 885 and the following two starts the next set 499 and the following two starts the next set and so on ......
I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day 8 days Thursday (Need a formula that says 8 days from Thursday would be Friday) 13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
I have a project I need to get done, and I've done most of it. But, there are a few things in VBA I'm unfamiliar with, and I was wondering if I could send you the file so you could take a look at it, and see if you're able to do it. This involves VBA. Honestly shouldn't be too too difficult, but something beyond what I can do. I have the specifications listed already, and I am ready to send it to you. Anyone feel comfortable taking a look at it?
I am attempting to create a macro that will loop through a range, each cell of which contains a worksheet name which needs to be activated in turn. My limited knowledge says that either of the two techniques below should work, but they do not.
Sub SelectSheet() For i = 2 To 50 Sheets(Range(("J" & i))).Select Next End Sub
OR
Sub SelectSheetv2() For i = 2 To 50 Sheets((Cells(i, 10))).Select Next End Sub
I have a Combobox with it's 'RowSource' set to two columns x 1500 rows on a spreadsheet. The Combobox is set to 'fmMatchEntryComplete'. When the user types in invalid text I get an "Invalid Value Property" error. I would like to validate the Combobox so that the user cannot type text other than available in the list, or not allow for the focus to be taken away away unless the item is a match to the list. At present due to the interaction of other controls on the userform, the only way to clear the error is by pressing the 'Escape key'. I have a button designed to reset the 'RowSource' of the Combobox but even after adding a 'MouseMove' event to this button with code
I have a list of names on my first sheet, and I want to mirror these on a second sheet, but at the start of a repeating table containing data releating to each named person. eg. I want a Name in
I have a "activity log" sheet which records the date in column A, the activity in column C and the name of the customer that was contacted in column D. There may be multiple entries of a customer's name in column D.
In a separate sheet, I want to find the latest date from the "activity log" sheet that a each customer was contacted and the specific action on that date. In this sheet, the client name will be in column A, and I want to record the last contact date for the client in the same row in column J and the type of contact in column K.
How to get a formula that will substitute every X with the actual type of meat. For instance, under Monday column, my table should list Liver, Pork, Mutton and Lamb Stew instead of X.
I use Excel 2003 & I am trying to design a worksheet to keep track of signs I make & their order numbers. I want the names listed alphabetically. When I add a new name & I arrange the column alphabetically, the columns with the order numbers do not relocate along with the names column.
I have attached an excel sheet which i wanted to write macro as an attachment. .
when I run a macro an email has to be sent to each persons listed in column A with Voucher # in the subject line and URL in the body of the message after the URL, the body of the message will remain same for all the emails:
so I have typed the body of the message in 2nd sheet:
I have below set of value in Sheet 1 (it has category & Name), whenever I open the Sheet 2 & Sheet 3, unique values should get automatically posted in D column..
Category Names
Pet Animal Dog
Pet Animal Cat
Wild Animal Elephant
[code]....
I have two requirements on this..
1. Unique names should listed in cell D of Sheet 2
2. Unique value of both category & name should listed in Cell D of Sheet 3
I have seen in the forums where lists are created by refering to column values in other worksheets.If you create a list by entering text values can you make other lists dependant to those values and can those lists also be made up of text lists?
All the examples I have seen here use lists that exist in some other worksheet
I use lists which use text values directly and not from a worksheet. I'm wondering how to make a secondary/sub list dependant on each value selected so say if text value "A" is selected in list 1 then this only allows the list for "A" to be available for list 2 in its corresponding cell (the next one along)
