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 want to create a simple worksheet that takes a list of names (All of the members of my family) and Randomly selects another name from that same list and matches them to it ( I don't want to say "paired" because I want each person to have a one way relationship to the other, in other words just because personA is matched to personB, i don't want personB to automatically be paired to personA) I hope that makes sense. Once every name on the list is matched with a person I would like to move that name to a database that I can track each year. The practical application of it is this: Each year our family randomly draws names and we are tasked to buy a new x-mas ornimate for that person, ideally you would not get the same person 2 years in a row, and the person that you have does not have you in return simply to add more variety and the database would track who had who each year.
Code: Sub test() Dim a, i As Long, temp, n As Long a = Range("b2", Range("b" & Rows.Count).End(xlUp)) Randomize With CreateObject("System.Collections.SortedList") For i = 1 To UBound(a, 1)
I need to set up a formula to choose 2 job titles from a single column. There can be 2 to n job titles (non-repeating) and I want to find the title at random. I have no problem finding the first job title using index and randbetween, but I want the second title to meet the same criteria, just not equal to the first job title.
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 have random comments in a column of cells of which I'm searching for a specific string of characters that may be contained in each cell. I want to pull 2 pieces of data from this specific string and place the data in 2 other cells in the same row.
Sample comments in cell 'N1':
..... blah, Appt: Friday; 12/4; night drop off. blah, blah.... Sample comments in cell 'N2' (and so on and so on):
I have a spreadsheet that has employee name and average hours worked.. The employee is listed multiple times because each row represents a pay period with number of hours worked. the last column I used to average the hours worked. So what I need is whenever there is a value greater than 30 in column g, I need to copy the row where column G has a value. It can be copied on the same sheet or on another.
I have a spreadsheet with numerous transactions that contain the same name but are often spelled differently or inconsistently. I would like to pull all the transactions with the similar name and in a new sheet, make all the inconsistent ones, the same e.g. (space)mr smith or mr smiths or mr smith's or mr smith would all be mr smith. I am thinking of using the match command as a possibility but want it to repeat each row.
I am attaching a spreadsheet to show the existing table and the desired result which is similar to what I am showing below.
Existing Names and data A B C D E 1mr smiths xyz10020% 2 mr smith abc12430% 3mr smith's def20040% 4mr smith xyz7550% 5simon inc zbc14065% 6simon's inc xyz20070% 7simon abc9525% 8shows zbd18045%
Want it to show Names and data:
A B C D E 1mr smithxyz10020% 2mr smithabc12430% 3mr smithdef20040% 4mr smithxyz7550% 5simon inczbc14065% 6simon incxyz20070% 7simon incabc9525% 8showszbd18045%
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 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.
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?
how to easily pull numbers out of a column in excel. I have several sets of numbers with a total in column F. I want a macro to go in and pull the last row in each set (this is the total row) and paste them in a different column so I can have the totals only.
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)
Trying to pull data from a list and if the cell above is empty then pull the data from the cell below and so on........
I have some code for it..but its just a little bit too long and I can't use it
This is what i have so far, trying to simplify it as its way to long to fit in a singel cell....i did think about breaking it down in to collums of either 8 or 6 and having it in chunks that way...but if i did that then i would still have to eventually use this forumla below as i don't really want huge gaps within the form i'm doing.
I have a list of active accounts with its relevant data on one sheet, then I have a list of closed/inactive accounts that will be updated periodically by different users. I would like to run a query for the accounts on the closed list to pull in relevant data from the active list,if any, or maybe just highlight the closed accounts on the active list to ensure that the closed accounts aren't active again. Is that possible with a macro? Should it just be a vlookup? The accounts list is going to be a very big file and I am trying to find a solution that won't take up too much time to run.
I'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.
I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).
For example, this is my 'Amortization Schedule':
Balance Due Interest Rate This Month's Interest This Month's Payment
And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)
Name Starting Balance Remaining Balance Interest Rate Minimum
For example, if today were 1/15/13, I would want $3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?
Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a $0 Balance Due?
i will put a command button, like "Randomise" what i wanted is , if i click that command button, the Value in cell A1 should be remain same. but the values in B1 has to be randomly changed that is, eachtime i click the randomise button the value of b1 must keep changing for every click. someting like this A1 B1 1 d 2 f 3 h 4 a etc
I need to create a file with random purchase list that have some restrictions. I’ve been working on this file for few days, but I couldn’t get desirable results. Attached a sample file that describes what I did. It build up as the final file (just the final file has many more items).
I’ll try to explain what I need and how it works: There are 4 categories: Theater, Salon, Kitchen & garden. I need to create random lists of purchases (each list should be a line at the total sheet) there is budget limitation of $100000. Each category has minimum items that must be purchase (headlines are in red color, total of 9 items).
The minimum items that can be on one list are 11 (9 red columns & another 2 random columns). Maximum are 15 items (9 + 6). Same item shouldn’t appear twice at the same list. This file chooses randomly 19 items and check if the list stands up the budget.
There are 3 issues that I couldn’t solve by myself:
1.I should define the file to stay within minimum & maximum items per list (11-15) 2.Make sure it doesn’t go over the budget 3.Stabilize the list the values will not change each time I’m doing changes to file
How do I get a random name from a list that I have created? The random feature only deals with numbers. I want to be able to push F9 or click on something and have a cell randomly pull a name from the list.