I have multiple columns with names (lets say columns A, B, and C). I want to place only the unique names in column E. Is there a formula to do this? Each column does not contain all the names and each has names that are not in any of the other columns.
View 4 RepliesI 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.
=SUM(IF(FREQUENCY(IF(B2:B20="Temp",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))
The works when referencing two columns. I need a total count of unique across three columns.
Sample Data
A | B |C
Jason | Temp | 1
Jason | Temp | 1
Jason | Final | 1
Kim | Temp | 1
Mary | Temp | 2
My summary table has the unique values of C in first column and the unique values of B as headers. I want the unique counts as the data.
Temp | Final
1 | 2 | 1
2 | 1 | 0
I need to list only the unique values in a range found in multiple columns. Some cells are blank. I need to list all the unique values in numeric and/or alphanumeric order in another column within the same worksheet. I would prefer non-array formulas, if possible. See the attached file.
In the attached table, I have 5 columns and 5 rows of values in the range B2:F6. The unique values, in order, are 0, 1, 2, 3, 4, 5. This looks simple and can be done manually if the file is small but I have 150 columns and 150 rows, thus the need for automation.
List of unique values in multiple columns 2.xlsx
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Solved
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows. I would not really like a macro, since it does not update when certain values are changed.
It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets. I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.
Three sheets:
Data 1: some value lists multiple columns
Data 2: some value lists, again multiple columns
Desired result: the result as it should be listed.
example.xls. zip
I have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.
The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).
I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.
Here's an example : all possible unique combinations.jpg
i would like to paste multiple duplicate values from unique values. Is there any way to do it in excel??
Ex:
Table 1:
3MG_HUMAN453
3MG_HUMAN44
3MG_HUMAN953
A1AT_HUMAN285
A1AT_HUMAN76
A1AT_HUMAN234
A1AT_HUMAN653
A1CF_HUMAN96
A1CF_HUMAN23
A1CF_HUMAN765
A4_HUMAN 944
A4_HUMAN 23
A4_HUMAN 755
Unique table
3MG_HUMAN100
A1AT_HUMAN78.89
A1CF_HUMAN90
A4_HUMAN 98
I wanted to add new column with these values for table 1 including duplicates. this is sample data and i wanted to replace thousands of this type.
I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:
SKU-A Location1
SKU-A Location1
SKU-A Location2
SKU-B Location1
SKU-B Location7
SKU-B Location7
SKU-B Location8
SKU-B Location8
SKU-B Location8
SKU-B Location8
I want it to have the data converted to be like this (with the third column being the the number of times that combination appears):
SKU-A Location1 2
SKU-A Location2 1
SKU-B Location1 1
SKU-B Location7 2
SKU-B Location8 4
this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like.
Example worksheet (Input)-
Worksheet ABC
Col A Col E
1/1/2004 $25
1/8/2004 $30
1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook
ColA Columns B Column C Column D
Sheet 1/1/2004 1/8/ 2004 1/15/2004
ABC $25 $30 $15
LMN $xxx $yyy $zzz
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
I have a column of names in Column D
I need code to list these names in column G
when I hit a button.
The question is presented and explained within the attached WB.
View 4 Replies View RelatedI have successfully loaded a combo box lookup in one cell referencing a column in another worksheet. While the drop down is pulling in all the names listed, there are duplicates in the data set. I want the drop down to only pull unique names. Is there a way I can do this and still use the combo box feature.
View 3 Replies View RelatedI have a spreadsheet with a series of names in a column and respective dollar values in a separate column. I would like a formula to automatically total the value spent against a particular name (without having to enter the name as a value each time).
Example
John Doe 3.89
John Doe 1.27
John Doe 0.98
Amy Smith 1.56
Amy Smith 2.29
Amy Smith 1.11
Alex Brown 0.67
Alex Brown 4.56
Alex Brown 8.87
I would like a formula to automatically populate a column, spreasheet, etc as follows:
John Doe 6.14
Amith Smith 4.96
Alex Brown 14.10
I have a column which has about 600 Names, and some of them are repeated, so I want to extract the Unique of them into a separate column, How can I do that?
View 5 Replies View RelatedI'm making an excel file where I keep track of the scoring in a little competition me and my friends do with betting on sports.
However, I got a little problem trying to automate my ranking.
Naamloos.jpg
As you can see in the image, when there is a duplicate score, the name stays the same for every other same value (Joris). The formula I use (also as seen in the image) just takes the first name it comes across (from left to right) and keeps using that one. But I need it to ignore the name Joris the second time, and ignore Joris AND Tim the third time, so that every score/rank (even if the scores are a draw) has a unique name attached to it.
I have two different lists of names. One is about 3000 names long and the other has about 3500 names. I want to identify the 500 people that are not in the first list that are in the second. If there anyway to do this? I was going to put them in A-Z order and then go through and using an IF(A=B,"yes","no") and go through and insert new cell when ever it says no till it says yes BUT the functions start messing up when I insert a new row and it's taking way too long.
View 2 Replies View RelatedI 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.
Have a long list of patients with visit details recorded - over 3 years worth
Many patients are seen multiple times, there is an age recorded also to ensure the same names can be 'set as distinct for different ages'
Need to replace (or add another column) the name field with a unique identifier so that we can ensure the same person has the same identifier.
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 got a set of data that I update once a month and the number of team members per team changes all the time. I'm trying to write a formula that basically says, if the date matches AND the manager name matches, count the number of team members.
In the attached sample if A2 and B4 are found in the data set, count the number of SalesReps they have. So I'm looking at Sarah for February 2013, she has two sales reps that sold something, but Katherine appears twice, so I'm not looking for a result of 3, the correct answer is 2. How do I write the formula?
A2 will look to the data range of A14:A23 and SarahK will look to I14:I23, but I want to count H14:23.
I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.
The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.
I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.
as there are 600 names and some can appear up to 4 - 5 times!
I have 1500+customer names in column C7 of table. I'm looking for a SUMPRODUCT formula that achieves the following 2 logical goals. First, it needs to determine the number of unique names in column C7. It can't just count the rows because many customers appear multiple times in the range. So, I have been using the following formula and it works great:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&"")))
Second, I'd like the formula to tell me how many of the unique names in C7 correspond to public companies. Column C8 of my table indicates whether or not a company is public ("Yes") or private ("No"). So, I've been using the following formula and it seems to deliver the right result:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&""))
*
(COUNTIFS(Table[C7],Table[C7]&"",Table[C8],"Yes")>=1))
But the added COUNTIFS statement seems very unwieldy and indirect to me. Why do I have to examine every instance of a particular company's name when the answer could be determined by looking at any instance of the name -- after all, each company in C7 is either public or private, and has the same corresponding value in C8 (whether "Yes" or "No") everywhere that its name appears in C7!
So that's my first question: is there a clearer, more efficient way to achieve the same result? My second question has to do with toggling the public company test.
What if I create a special cell, say A1, that will always contain either the word "On" or "Off"........
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 want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.
I should also point out that most company names appear multiple times in column [C7].
a) Count the unique company names in column [C7]...
b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records
c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records
Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
Excel 2007
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code:
For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Sheets("Allocation").Activate
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.
I'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.