If I have a list in Collumn A such as:
1
1
2
2
3
4
5
5
6
6
Is there a simply excel function I can use so that in collumn B I return only the unique results. That is, collumn B will look like this
1
2
3
4
5
6
????
I have a workbook with the twelve months of the year as seperat worksheets. a thirteenth worksheet is a "total" page. I have three different names to choose from a drop down list in collumn E. I need a formula that will say something like, "if JanuaryE4 = John Doe, total page A1= January A1" Is this possible?
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.
I 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 RelatedI have a column of names in Column D
I need code to list these names in column G
when I hit a button.
I have a list of names in column A these names repeat themselves a lot and I don't know how many there are.
now I want to make a list of the names in column c starting at C2 that looks down row A finds names and puts them in column C one after the other.
I have list of various city names in column B. From that i need unique city names in validation cell.
The reason is,
we have emp id, name, designation, area, marital status for our employees. From that if i select emp id from validation cell1 i have to get all employee id's available in the sheet1 is need to come under the validation2 cell automatically. and if i select "area" from validation cell1, all unique areas need to be list out in the validation cell2. Its look likes a filter. but without filter i need this for creating application.
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.
View 3 Replies View RelatedI have a formula-generated defined list of names. I need to select them in random order without duplication and without choosing any blanks in the list.
View 6 Replies View RelatedI've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
Our unique identifiers are from SF.com... they have created unique Account ID's.
Comp Name Unique ID
Company A - 5102AA
Company B - 5102Aa
The problem:
Excel does not recognize the capital A and the lowercase a as being different. I need to pull this data out and bounce data across tables for analytics.
The Question:
Is there any way I can get Excel to recognize 5102AA and 5102Aa as being DIFFERENT ID's?
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).
So, for example, if i had the following values in column C:One
Two
Two
One
One
Three
Seven
Two
Four
Seven
One
Three
I would want the following values in column E:One
Two
Three
Seven
Four
I have successfully generated my code to validate the 60 + text boxes in a MultiPage UserForm. Now I need to identify those TextBoxes that require validation. To aid me I have distinguished those which need to be validated by naming them with a unique suffix, (i.e., _Text vs. _Txt). This code will participate as a Boolean within a With statement as part of the identifying validating code. I realize that a
Right(?, 5) = “_Text”
statement should do the trick but I can’t seem to correctly get the appropriate syntax to use within the parenthesis. Any suggestions? Is there a better methodology out there to accomplish this?
I realise there are a few threads on similar topics but I can't seem to get any of them to work for my scenario. I have two columns with a list of numbers in each. I'm after a function that will scan column A and identify any numbers that also appear in column B. If possible I would like it to then generate a list of these in say, column C.
View 2 Replies View RelatedI am trying to wash some of the data that is exported from a database into excel. I need to pick out the column header that reads "Revenue" and copy all of the data (the whole column) under it to another sheet starting at A25. I've tried combinations of vLookup, hLookup, IF, and etc.... but can't do it. Details below.
Here is what I am trying to accomplish:
In row A1, identify which Column is titled "Revenue"
Copy contents of the "Revenue" column to Sheet1-A25
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
I have a list of variables created by a user like this:
BAND ID START END
Band1 06:00 08:59
Band2 09:00 15:59
Band3 16:00 17:59
Band4 18:00 22:30
Band5 22:31 24:29
Band6 24:30 05:59
Seperately I have data from a third party like this:
DATE TIME VALUE
18/05/08 17:22 10
19/05/08 02:15 1
20/05/08 07:14 5
20/05/08 21:36 15
20/05/08 25:43 1
21/05/08 19:15 15
22/05/08 18:26 16
22/05/08 24:59 2
etc.
I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.
Here is the deal I have 4 columns. Each line gives you the following information:
The Id is the record number, Code_Name is a code for each Fox in the study, date and area is a sub area in a bigger grid. Basically I have an area divided by squares and every time a marked fox enters in one of my squares a new line in the data is created. What I won’t to know is if a fox when in my area of study will return to the same squares or not.
ID
CODE_NAME
Date
Area
Logical
116
SSS1
02-Jan-09
1A1
273
RRR1
02-Jan-09
2A2
2959
BBB1
02-Jan-09
1B1
2959
What I’m trying to achieve is a 5th column were ill get a logical value of TRUE or FALSE if, for each fox in the next available record a fox went back to the same square or not.
So if you check for the fox RRR1 I have 2 records one in 2 of January in area 1A1 and a second in 7 of January in the same area. For the Fox BBB1 you will see that she was always in different areas and for CCC1 she only came back to one square.
The problem is I have over 400 fox’s and 12000 records and I’m trying to get a way of doing it automatically.
I have a number of workers in one column and how many dvds they have sold for the week in another column. how would I automatically identify the worker from the worker list who has sold the most dvds in that week? is there a formula for this?
View 11 Replies View RelatedI have a cell with a long list of phrases all separated by commas.
So it would appear, chicken, beef, steak, soup, fish
I want to create a rule in a cell to identify if one of those phrases comes up - so if chicken comes up then a 1 would appear.
To make it interesting is it possible to grab two words from the cell, so grab the words chicken and beef from a mass of words in one cell and bring it across.
I have these cells which contain huge amount of text and preferences and I am looking to identify these entities on if they contain these words.
I would like to identify duplicates in a list using conditional formatting in Excel 2007.
I have tried choosing to identify duplicates using the formula that I have found on many threads throughout the message board:
=COUNTIF($A$1:$A1,$A1)>1.
This function works up to 15 characters in a cell, but Excel seems to be treating all digits after the first 15 as the same, resulting in a "fuzzy match" where I want an exact match. Many of the values in my list are 18 characters long, in text format to prevent rounding.
I've noticed that Excel treats the 18-characters values the same way when sorting; for example, it treats these two values as the same:
'234567891011121314
'234567891011122413
Is there a way to force Excel to examine those last four digits for the purpose of sorting & identifying duplicates?
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've been tying to get the vlookup function to not stop when it finds the 1st occurence and actually find all the occurences in that collumn and then sum them together, can this be done? Or by using a different function? Example......
So when vlookup looks for blue it should return 5 which is sum of 1 + 4 rather than stop when it finds 1.
From another thread I got this formula placed on B5:
=NETWORKDAYS(EOMONTH(B4,-1)+1,EOMONTH(B4,0),Holidays)
his generates the number of working days for that month (regardless of the date on B4) that exclude Holidays listed under the range "Holidays".
I also got this code (Credits to XXXX)
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'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.