Filtering For Unique And 1 Duplicate
Aug 7, 2009
create a lookup table in access from this excel spreadsheet. Column : "Unit Name" - has some unique and majority are duplicates Unit Names. I want to filter for Unique names and for one name of each duplicate. This will give me all "Unit Name"'s we've entered to date.
When I enable filters for the headings of each column, The list of what I need is there...while in the "Unit Name" Column. If I click on the filter drop down....that the list. My thing is how do I get that on the sheet it self.
View 3 Replies
ADVERTISEMENT
Apr 29, 2014
I am trying to setup a table for someone to track and schedule service hours on heavy machinery.
I have a table with columns for Make, Model/SN(which is the unique field in each row), date of last service, hours at last service, current hours and an hours since last service. Service needs to be done at 500 hours so I filter the table based on the hours since last service for >= 400 hours. That's fine, but it shows multiple lines per machine. Is there a way to use a custom filter to find duplicate values in the Model/SN column and then only show the row with the max date of last service?
I'm sure this would be a cinch with VBA, but I would like to find a way that if something changes later on after I am gone that he can still possibly figure out how to make it work.
View 1 Replies
View Related
Jul 20, 2006
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.
View 7 Replies
View Related
Feb 24, 2014
I have an excel which is containing records of users who have given an online test.Many users have given the test multiple times. I need to extract the records of every individual with the highest score. example it could be individual A, B and C have given the test 3 times with different scores achieved. I need the records of A, B and C in which their score was maximum.
View 7 Replies
View Related
Jul 23, 2009
i need to filter and delete duplicate values. where i have some issues is that i just need keep the first entry and delete the second (and so on) entry. means when i have a list with ids and hours like that:
id1 hours1
id1 hours2
id1 hours3
id2 ...
i need to have id1 and hours1 but delete hours2 and hours3. hours is the result of a formula. i tried with advanced filter but it somehow just hides me the duplicates and it sums me the hours.
View 2 Replies
View Related
May 28, 2012
I am relatively new to Excel so there may be a easy way to do this that I just can't see. Here's the background:
My raw data contains 10,000+ entries with many duplicates. Each entry though has a time stamp like so:
Unique Identifier: TimeStamp
S1111111111 10th July 02:00
S1111111111 10th July 02:10
S1111111111 10th July 00:50
What I want to do is to isolate the duplicates in this sheet of 10K+ that have the earliest timestamps and delete duplicates with later time. So in the above example, I would only be left with the 3rd entry as it has the earliest timestamp.
View 3 Replies
View Related
May 1, 2009
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
0001 a
0001 b
0002 c
0002 d
0003 e
0003 f
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:
0001 a
0002 c
0003 f
I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View 6 Replies
View Related
Apr 29, 2013
VB:
Sub aa()
Dim roow As Long
Dim i As Long [code]...
View 3 Replies
View Related
Jun 5, 2013
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number
What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.
Is there a way to accomplish this via some functions or maybe even a VBA script?
View 3 Replies
View Related
Feb 23, 2012
I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.
View 9 Replies
View Related
Dec 10, 2008
I receive a csv spreadsheet in which Column A contains ID's like this
LS-X23268370.944849537037037
LS-X23181010.445011574074074
LS-X23181010.444930555555556
LS-X23399640.612361111111111
LS-X23399640.612395833333333
LS-X23399640.612407407407407
LS-X23399640.612395833333333
LS-X23399640.612395833333333
LS-X23399640.612407407407407
LS-X23399640.612407407407407
As you can see, some of these are identical. Fort example there are 3 copies of
LS-X23399640.612395833333333
I'd like to run a macro that effectively looks for duplicates and appends a letter to the end so the example above I would end up with..
LS-X23268370.944849537037037
LS-X23181010.445011574074074
LS-X23181010.444930555555556
LS-X23399640.612361111111111
LS-X23399640.612395833333333a
LS-X23399640.612407407407407a
LS-X23399640.612395833333333b
LS-X23399640.612395833333333c
LS-X23399640.612407407407407b
LS-X23399640.612407407407407c
View 9 Replies
View Related
Aug 14, 2014
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx
View 5 Replies
View Related
Jul 28, 2014
I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.
Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.
Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))
Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))
Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order
Spreadsheet Attached : Golf Excel Score.xls
View 11 Replies
View Related
Jul 13, 2009
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.
View 7 Replies
View Related
Aug 29, 2012
I am using the following code to copy a unique list, but it gives a duplicate in the first 2 rows(col Q). There is no headings in the columns.
Code:
Sub CopyUnique()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).row
ActiveSheet.Range("P11:P" & lastrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("Q11"), Unique:=True
End Sub
View 4 Replies
View Related
Jul 28, 2014
I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.
Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.
Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))
Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))
Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order
View 4 Replies
View Related
Jan 27, 2014
I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
View 2 Replies
View Related
Feb 4, 2014
I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).
As filter I would like to be used A,B,C columns.
EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
View 1 Replies
View Related
Jun 13, 2014
I have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
View 5 Replies
View Related
Mar 5, 2014
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
View 9 Replies
View Related
Jan 31, 2014
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
View 8 Replies
View Related
Aug 19, 2014
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx
View 1 Replies
View Related
Dec 11, 2008
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
View 5 Replies
View Related
Nov 1, 2007
I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.
View 9 Replies
View Related
Jun 12, 2008
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
View 3 Replies
View Related
Jan 5, 2004
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100
101
102
105
Col D
6
6
5
5
I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.
Here is my code thus far....
-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
[Code]....
View 9 Replies
View Related
Jan 5, 2004
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100.........................
View 9 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
Jun 5, 2014
I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple
0001: Apple
0002: Google
0003: Sony
0003: Sony
0003: Sony
0003: Sony
0004: Ford
0004: Ford
0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
View 1 Replies
View Related
Oct 22, 2009
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
View 8 Replies
View Related
