I have a list of names in say column A, but the names are prefixed with numbers. So, for example you have 2 Peter Jones, 5 Johnny Piper, 10 Andrea, 3 St. Mary's, 13 White etc.
I want to return these list of names in another column say B, but without the prefixed numbers. So, column B will have Peter Jones, Johnny Piper, Andrea, St. Mary's, White etc.
I'd like to be able to repeat a list of names X number of times.
Example: Repeat count = 3
Name1
Name2
Name3
Name4
becomes
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
I have a list of names for my wedding and am making place cards out of scrabble pieces and need to know how many times each individual letter appears in the 150names to know how many of each letter to buy
View 5 Replies View RelatedI 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.
Is there a way to open a file similair to
fMask="Post Biz Voice Installs QC Report*.TXT"
varFiles = Application. GetOpenFilename("TEXT Files,"&fMask, 1, "Please select all the Post Biz Voice Installs QA Report files", , True)
I want to filter the filenames to only certain prefixes
I am then importing the files and populatin the ws
Here is my whole module so it can be seen in context
Sub GetBCDV()
Dim varFiles As Variant
Dim SourceBook As Workbook, TargetBook As Workbook, strAddress As String ', 1 As Integer
Call Archive_WB_BCDV
fMask="Post Biz Voice Installs QC Report*.TXT"
varFiles = Application.GetOpenFilename("TEXT Files,"&fMask, 1, "Please select all the Post Biz Voice Installs QA Report files", , True)
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 RelatedPublic Sub ListMyFiles()
Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long
Set fso_Folder = fso.GetFolder("S:SHARED All of TransportationTraffic AssessmentMultiway Warrants")
file_count = 1
For Each fso_File In fso_Folder.Files
file_count = file_count + 1
Cells(file_count, 1).Value = fso_File.Name
Next fso_File
Set fso = Nothing
End Sub
This works great, but is there a way for the macro to recognize the file names that have already been added to the spread sheet and only return new ones that have been added to the folder?
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.
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've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
Does this involve IF, MATCH or VLOOKUP formulas?
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).
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 Relatedtrying to come up with a formula that will return the last non-zero value in a row. In the example below, I'd like the formula to return the value "456". If there is a way to do this without VBA or array formulas, that much the better!
View 2 Replies View RelatedI 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]...
The conversion rate is updated daily from telephony reports, and I manually sort it into conversion order each day before sending it round.
What I want to do is find the highest converter for each team.
So:
Column A ('TeamLeader' range) has the Team leaders in, column B has the agent name ('Agent' range), and column F has the agents conversion rate ('conversion' range).
I think I'm on the right track with this formula:
=(MAX(IF(TeamLeader="Bonnie",Conversion))
But this only displays the figure in the Conversion rate, how to I get it to 'step back' 4 column's and show the agents name?
I tried this:
=VLOOKUP(MAX(IF(TeamLeader="Bonnie",Conversion)),A5:F60,-4,FALSE)
but that returns #NA - is this because I'm asking the VLOOKUP to look 'backwards' 4 columns?
I know how to create drop down list but I have a problem to Return different value then is displayed in Dropdown List. For example: On the workbook I have two wokssheets: one with empty cell and secound with list created for dropdown list.
On the secound woksheet I have created: column A namebox "Country" - cointains full name of countries (Great Britain, Australia ...) and column B namebox "CountryCode" - contains country codes only (GBR, AUS ...) When I click on dorp down list on first worksheet, I would like to my drop down list display me list with columnA "Country" but when I choose one of that it would returns me cell from columnB "Country Code".
Is there any function in Excel that returns all values from a list above or below a certain percentage rank/percentile?
For instance, if you want the average of the 2% highest numbers in a list? Or the sum of the 25% most expensive items in a shopping catalogue?
It has to be a "dynamic" function, where you can just drop in a set of figures, sort the list, add a percentile, and perform a calculation on all the figures from the cutoff point signalled by the percentile...
I have run across a problem that I am unable to solve with a combination of index, match and countif funtions. What I am trying to do is have the formula return disctinct symbols. I have a list of currencies on one sheet: audusd, eurusd, usdcad etc, more than one from each. Id like to return one from each onto another sheet...
View 9 Replies View Relatedi have a list of numbers in column A and B and in column C is = B - A. But what i want is for Column C to always = positive number regardless even if say A = 10 and B = 8 the true answer is -2 but i would like the reading of 2. Is there a way to say B - A and if = - make it -( ) ? (otherwords +)
View 2 Replies View Relatedis there a way to return a textbox full of information based on a selection from a dropdown box?
View 4 Replies View RelatedActually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.
I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.
What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.
create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.
[URL]
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.
View 7 Replies View RelatedIs there a way of returning the nth factor of a number via a formula?
That is to say, if I have 10, its divisors are 1, 2, 5 and 10, and if I wanted to return the second factor, it would be 2.