I have a long list of names in Column A sorted alphabetcially. I dont know how long the list is.
I wish to find the start and end row numbers of names that begin with a specific letter, for example if the letter is B I want the first row number that starts with B and the row number that ends with B. Assuming the letter I want to use is in C1, here is what I came up with:
I have an issue sorting values. i can get the numbers together and sorted, but want to keep a name associated with the values being sorted. so when the values are sorted, and they change the name associated with each value moves with it. Its acutally a first-tenth place in a nba fantasy league.
View 7 Replies View RelatedI have a sheet with about 20000 rows consisting of hundreds of names which
occur randomly.After each name is a date of the type 29-Jan-06.The dates are in ascending order.I wish to print in a third column how long it is in days since that name appeared previously in the list,if it never appeared before this will be zero.
I have a list of entries on one sheet. This sheet contains part numbers in random order. I would like to draw out the part numbers onto a new sheet, with no repeats and in numerical order. I want this to be done automatically without using a sort command. Also, I would like to avoid VBA if possible.
View 8 Replies View RelatedSuppose I have in A1, names of people seperated by comma....
James Dean, Bruce Lee, Marilyn Monroe, Tom Cruise, etc
How do I break them into rows like
James Dean
Bruce Lee
Marilyn Monroe
Tom Cruise
I have a workbook with a collumn full of football championships. As you know there are hundreds of different championships names. And i have a collumn with all of them but some are repeated over and over because this collumn is connected with values of profit/loss in another collumn. For example: Blue Square North (Collumn A1) and in collumn B1 1.75; then Blue Square North (Collum A2) and 2.98 (Collumn B2), and over and over...
So what i need is a formula that retrieves all unique and different names in a range collumn and the puts all the dfferent names in a new collumn (lets say C). Because what i intend to do then is to use vlookup to search for the championships one by one (through collumn C) and count the ones with profit and them loss by the name of each one.
So what i need is a way to retrive a list of the unique names in A and get them in C.
I have an Excel sheet with a list of data, which is likely to change frequently and has to remain in sorted order.
Requiring that users of the spreadsheet maintain the data in this way (i.e. sort it every time it changes) is a really bad solution and I'd rather not use a macro if I can avoid it (too much stuff can go wrong).
Is there a good way of doing this? At the moment, I've implemented merge-sort within a sheet, which works, but is a heavyweight solution, taking 6 columns per merge-sort iteration (so 60 columns to sort 1024 rows, 96 columns to sort 65536 rows). I could try to compact it, but my head was starting to implode programming it even with as few as 6 columns.
There must be a better way of getting Excel to maintain an automatically sorted list.
I want to copy the first 30 rows of a autofilter list can someone point me in the right direction?
View 13 Replies View RelatedDependent Dropdowns from a Sorted List:
i have one file here..
how did they created validation here.
is there any macros?
i tried..but i didnt able creat like this.
i got this file in this link.
http://www.contextures.com/xlDataVal13.html
i saw this problem in this post..
http://www.excelforum.com/excel-prog...hierarchy.html
I have a list of names in column A and days across row 1, with a tick where each name is working. I need a formula to be able to count how many names are working a sixth day in each week.
View 9 Replies View RelatedI am currently using a pivot to return and populate the required data but this does not refresh correctly and returns zero values every time this report is refreshed. I am therefore hoping that there is a way to calculate this using formulas .
The following applies:
The attached document has 2 tabs: Date AND Report
The Data tab feeds the report tab
The REPORT tab has 2 reports in it but doing the first one then I should be able to do the second one. If you look at the first table you will see the data that needs to be populated. I have added a highlighted (in YELLOW) row at the bottom of this table which shows you which columns populate what column
Requirement:
I am after something that will populate this table in a descending order - IE: the figures in the DATA tab Column BE should be sorted from highest to lowest. It must be filtered to the top 30 results only and then all the other columns must be populated.
Column H in the REPORT tab should be formatted to [h]:mm
Column F & G in the REPORT tab should be formatted to dd/mm/yyyy hh:mm
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 created a spreadsheet with several combo boxes that that is linked to other data.
When this data is sorted, the values in the combo boxes are automatically changed.
Is there any way to prevent the the values to change?
I have a list of email aliases for my company, approx 10,000 entries, mapping the alias to an email address. Two columns, one the alias firstname.surname, second column being the full email address. I have a second list, a subset of these aliases, approx 1200 of them, that are users of a particular web app. All i have in this list though, is the firstname.surname bit.
What I need to do is compare the lists, and where the aliases match I need to pull out the corresponding full email address from the long list, so I have 1200 aliases in the shorter list, with the full email address mapped to it. Is this possible in excel? i've tried all sorts of LOOKUP functions, and MATCH functions, but none seem to work. MATCH will give me the corresponding row number in the long list, but then it seems to offset itself every row it goes down, so the values are 1 out for every row. First row is good, second row -1, third row -3, fourth row -4 etc.
I don't want to have to manually go through 1200 entries and match them against a 10,000-entry list. Hopefully there's a quick way in excel to filter the long list down to the entries specified in the short list?
I am trying to create a summary Calendar that would display all the names of the people who are off on a given day. I have a grid where I display each day of the month and a person in the group has their own column. If they are out of the office for any reason their name appears in that row. In the big Summary Calendar cell for each day of the month I want to look across all the people columns for that day and then display the names of the people who are not going to be in the office. Can I do this by a formula or will using VBA work best.
View 6 Replies View RelatedIs there any way to determine whether a column is sorted? Like a property of a column like ".ascending" true or false?
View 9 Replies View Relatedif i have a column of number that is not sorted(it cannot be sorted due to other requirements), is it possible to find the cell that reflects the nearest number that i want to look up?
View 4 Replies View RelatedI 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 am trying to sort a fairly long list of tasks into a custom calendar order. I have created the custom list as Sept, Oct, Nov, Dec, Jan etc through to Aug, with one additional heading of "Recurring". The list won't seem to sort by the column where these headings occur. See attachment.
View 7 Replies View RelatedMy data is set up as
A
B
C
1
A
B
C
[Code]....
How Can I make that data appear as
A
1
A
2
B
[Code]...
Where is takes the row and keeps transposing it down 3 lines at a time?
I have a spreadsheet with only entries in column A - 154 cells. Nothing in other columns. When I try to print, it says it'll be 4 sheets long...
1
2
3
4
down to
154
How can I get it to print in a snake-like fashion so all on one page?
- In column A, I have list of items that take up 700 cells (i.e. the text is in cells A1, A2... A700).
- I have applied conditional formatting to these 700 cells, so some of them are now highlight YELLOW based on a criteria (i.e. cells A3, A14, A422, A654 are yellow)
- I want to create a list of these yellow cells in column B.
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 Relatedhow to add alternating cells when all are numbers, e.g. B8 to KW8 are all numbers and I want to add B8, D8, F8, etc. until KV8 and then separately add C8, E8, G8, etc until KW8. Is there a more efficient way to do this than =sum(b8,D8, etc)? I also need to do the same with =counta(b8, d8, etc).
View 2 Replies View Related
