Finding Unique Entries Among Two Columns Of Alphanumeric Data
Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844, etc.). Many of the Project Numbers in column B are the same as in column A, but column B also has additional (i.e., newer) Project Numbers scattered throughout. Ideally, I would like to use a built-in function (versus a custom function if possible) that compares all the Project Numbers in both columns and then separately lists those that are unique to column B.
View Complete Thread with Replies
Related Forum Messages:
Counting Unique Values, 2 Columns, Alphanumeric
I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.
So, the result I am after is 2 vans, 1 straight truck
I found an array in a another post, but it is not working properly (see cell F7 in the attached):
Finding Unique Combinations Of 2 Columns
I have a spreadsheet with two columns:
The first one will always have a 'W' or a 'E'.
The second one will have a three digit code. Like this:
Is it possible to have output that show how many of each combination there is? Like this(for the example above):
W 504 2
W 505 2
W 507 3
E 504 2
Count Number Of Rows With Unique Entries In 2 Columns
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.
All the cust numbers are unique so would I be able to use a wildcard?
Finding & Displaying Unique Data Within A List
Attached is a sample file that has a Data entry sheet in which the user can paste in data. They can test & paste anywhere from 1 to 50 compounds at a time and run the list through 1 to 55 Assays. I want to generate a sheet (Plate & Assay Info) that would first display the list of compounds that were tested as well as the list of Assays that they were run through.
Finding The Highest Value In Alphanumeric Strings
I have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.
Unique List Would Grab All Unique Entries
I have a data table that repeats as follows:
From the data above I need to make a new unqie list that would grab all unique entries.
I am trying to use the following guide:
Get stuck on "Listing A:
Transpose Repeated Data With Unique Associated Data Across Columns
Is it possible to associate a range of cells containing different information based on like values in other cells?
How it currently appears in spreadsheet
John Q. Public 12345
John Q. Public 23456
John Q. Public 34567
John Q. Public 45678
Jane Example 11111
Jane Example 22222
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
Jane Example 11111 22222
Jane Example 11111 22222
I know how to remove the duplicates afterwords to get one unique record.
Add Duplicate Values For Multiple Columns From Unique Data..
i would like to paste multiple duplicate values from unique values. Is there any way to do it in excel??
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.
Compare 2 Different Columns Of Data - Find Unique Results
I would like to compare some keyword lists if possible.
I have a large list of Unique keywords in Col A (From A3),
This Column is called Keyword List A - Large"
I then have a keyword list in Column C (From Cell C3),,
This Column is called "Keyword List B - Small.
I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.
What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")
If possible could a pop up box appear saying
List A No Rows:xyz
List B No Rows: xyz
No of Uniques Found : xyz
Time Elapsed (sec): xyz
I'm running windows XP and Excel 2007.
Finding Duplicate Cell Entries
I have a lengthy excel spreadsheet in which I catalog all of my Karaoke song collection. I would like to be able to automatically locate (not delete) cells in which there are duplicate song titles. For my purposes just finding these items quickly so I may deal with them in a manner I've already developed is all I want to do. VBA, Macro or whatever would be appreciated. I'm a relative novice with macros and VBA.
For example row 12 may have the song title "Crazy" in column C and later row 1200 may have the same song title. Manually searching the spreadsheet for dupliates is way too time consuming so I think it can be seen why I'm looking to unlock the power of excel to do this for me.
Sum Unique Entries
I have attached a sample workbook to try and explain what I'm after.
Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.
Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns
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.
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
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
Delete Unique Entries
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........
Count By Unique Entries
I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'.
I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column.
John Potts.................Truancy 2
Matt Jones................Truancy 10
John Potts.................Truancy 4
Matt Jones ...............Late AM
Pete Burns................Late PM
Pete Burns ...............Late Both
Steve Lopez..............Truancy 6
Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once]
Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once].
I have also included a sample workbook.
Identifying Unique Entries
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
I would want the following values in column E:One
Choosing Unique Entries
I'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.
Extract Unique Entries
I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them.
I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).
Find Sum Of Unique Entries
I am using excel 2007.
I am trying to find the sum of unique entries in a table such as below for each respective date.
The results should appear as
What formula would I use to calculate this unique count?
Unique Entries & List
I have the below table of data, in Column A there is a list of entries and there may be duplicates in them, what I want to do in column B is be able to list all the unique entries in there and basically remove any duplicates.
AB1DETAILED LISTUNIQUE LIST2Holden 3Ford 4BMW 5Mercedes 6Ford 7Ford 8Audi 9BMW 10Holden 11Ferarri 12Holden 13BMW 14Toyota 15Toyota
Filtering Unique Entries
I have this 2 columns, A(region) and B(location), and I need a macro to filter the unique entries in Columns A and return both the unique values in A and their corresponding values in B. I've attached a file on it with sheet 1 having the data to be worked on and sheet 2 being the output that I require.
Finding Duplicate Entries In Column Considering Indirect Referencing
I wonder if there is any easy way of findinig (numerical) duplicate entries in a column? Some cells are empty, in case this might cause a problem. I do not wish to delete duplicate rows automatically, just to find them. Why not just sort it? Because indirect referenceing is used where each row corresponds to a separate spreadsheet in the workbook. What I need is to find the duplicate so that I manually can erase one of the spreadsheets for the particular case and adjusting a reference list.
Copying Unique Entries To New Spreadsheet
I'm trying to write a macro to use as an auto-update feature for a spreadsheet.
I currently use Data validation on an input cell to allow the user to select from a list of product types.
I'd like the update to affect this list, so new product types are always available to the user.
However, the list of product types is in another spreadsheet and contains numerous repetitions.
I've managed to get the following code together based on what I've used before and what I've found searching so far, but it seems to have a bug in it as it wont always work. I also need the original copying of the list to be done to either the new spreadsheet of a different page in the original spreadsheet, but every time I try this it causes an object error.
' Macro3 Macro
' Macro recorded 05/12/2008 by Information Technology
Workbooks.Open Filename:= _
Sheets("Mask List").Range("f4:f2000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("a1:a3"), CopyToRange:=Range("E1:E2000"), Unique:= _
Selection.Sort Key1:=Range("E1:e2000"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Formula To Return Unique Entries Only
I am seeking to pull names from a list on one worksheet in to particular cells on another worksheet using each name only once. I need a function or series of them that can accomplish this. I have been trying to figure this out for a couple of months with no luck as I always get into some form of circular reference....
Counting Unique Entries From 3 Workbooks
I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.
I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))
Filtering For Unique Entries Based On One Column
This may be a basic question but I haven't been able to find the solution:
I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example
I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example:
I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
Counting Unique Entries That Meet Certain Criteria
Every other line is the planned production and the others are filled in after as the actual production
I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.
Here is my problem- if a station is running two different products it gets counted twice.
I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines.
Attached is an example of what I have with the formula and below that is what I am looking for
Count Unique Entries Within Variable Date Range
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]
I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.
In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!
The correct result: ...
Advance Filter Check Unique Column Entries
How do you remove entries from a column such that only unique entires are left?
Say the spreadsheet contains only a single column:
I tried an "advanced filter" with "unique records only" checked. It gets rid of one david, but not 2 (there are originally 3 of them, and we want to leave 1)
This is the result.
Count Unique Entries Within Variable Date Range ..
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
Delete Columns, Increment Alphanumeric Text, Insert Rows & Sort
I am having to write a vba code for a survey data captured code online.
I am attaching the code and the vba module that is suupose to do
2) dELETES THE LAST 3 COLUMNS
3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)
4)Inserts new row at top from Q1 TO Q11
5) Sorts data on Q6 (Faculty/Staff)
Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....
Finding Unique Names When Comparing Two Lists
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.
List Unique Entries And Total Cells Based On The Results
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
ISBLANK: Finds Out Which Entries Are Unique To Each List, And Places Them In The EXCEPTIONS Sheet
I'm having problem with the ISBLANK function. I have attached my workbooks if someone would care to look at them. My macro basically loads two lists from other workbooks (old & new (attached)). It then finds out which entries are unique to each list, and places them in the EXCEPTIONS sheet.
Column C in these sheets should say TRUE or FALSE as to whether the corresponding cells in Column B are blank but it does not work. Book1.xls contains my macro.
Old.xls and New.xls will need to be selected when prompted.
Remove All But One Non-unique Line Of Data, While Leaving Unique Data Alone
I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or
b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
Data loaded into the sheet before the macro runs:
1 06/09/07 *
2 06/10/07 *
3 06/11/07 *
6 06/12/07 *
7 06/13/07 *
10 06/14/07 *
* The rows with asterisks are the ones that I would like to have pulled to the second set of data (below)
Data in the sheet after the macro runs:
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.