Is there a quick way to delete all duplicate rows in a column?

Column A consists of names; Column B consists of dates.  If a name is 
registered on more than one occasion, there will be two entries with same 
name, different dates.  Is there a quick way to delete these duplicates other 
than sorting Col. A alphabetically and deleting them manually?  
0
Jellaby (1)
5/16/2005 8:53:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
380 Views

Similar Articles

[PageSpeed] 3

In C1 add

=IF(COUNTIF($A$1:A1,A1)>1,"Dup","")

and copy down

then filter on column C, and select Dup, and delete visible rows

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Jellaby" <Jellaby@discussions.microsoft.com> wrote in message
news:44B970BF-DFE6-4760-8FD0-A3C8279541D3@microsoft.com...
> Column A consists of names; Column B consists of dates.  If a name is
> registered on more than one occasion, there will be two entries with same
> name, different dates.  Is there a quick way to delete these duplicates
other
> than sorting Col. A alphabetically and deleting them manually?


0
bob.phillips1 (6510)
5/16/2005 9:16:55 PM
Data>Filter>Advanced Filter.

Check "unique records only" and "copy to another location".

For more on this see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfilter01.html


Gord Dibben Excel MVP

On Mon, 16 May 2005 13:53:01 -0700, "Jellaby"
<Jellaby@discussions.microsoft.com> wrote:

>Column A consists of names; Column B consists of dates.  If a name is 
>registered on more than one occasion, there will be two entries with same 
>name, different dates.  Is there a quick way to delete these duplicates other 
>than sorting Col. A alphabetically and deleting them manually?  

0
Gord
5/16/2005 9:32:10 PM
Data>adavnced filter, copy to another location and unique records only


Regards,

Peo Sjoblom

"Jellaby" wrote:

> Column A consists of names; Column B consists of dates.  If a name is 
> registered on more than one occasion, there will be two entries with same 
> name, different dates.  Is there a quick way to delete these duplicates other 
> than sorting Col. A alphabetically and deleting them manually?  
0
PeoSjoblom (789)
5/16/2005 9:47:34 PM
Reply:

Similar Artilces:

I hit save instead of save as...is there any way to go back?
I need to retrieve the information from the page before I saved. I needed to hit save as and accidently hit save. Anyone???? You've kinda burned your own bridge. -- JoAnn Paules MVP Microsoft [Publisher] "Jr Bullet" <Jr Bullet@discussions.microsoft.com> wrote in message news:C9AD451B-209A-448F-935B-B2D1EC64A733@microsoft.com... >I need to retrieve the information from the page before I saved. I needed >to > hit save as and accidently hit save. Anyone???? On Thu, 10 Nov 2005 00:52:09 +0000, Jr Bullet wrote (in article <C9AD451B-209A-448F-935B-B2...

duplicate email #7
I have some client using outlook who receive duplicate email (2 times) everytime. How can I fix it?? The server is using SBS2003, just like windows 2003 server + exchange 2003. Any advise? Edf Hi Edmond, did you check the rules. If you get duplicate amails, it is often that you have double rules on a mail. Please check you r rules! -- Gestern lief noch alles, ich habe nichts gemacht! Bitte in der Newsgroup antworten, damit jeder davon was hat "Edmond" <reg@megawharf.com> schrieb im Newsbeitrag news:#GqmYtXPEHA.4036@TK2MSFTNGP12.phx.gbl... > I have some client using ou...

Macro to highlight every 2nd row
Hi, I am trying to work out how to write a macro so that it takes my data range and highlights every second row in a certain colour. if my data is in the range A2:K31, then i want the macro to run so it selects A3:K3 and highlights that in yellow, then skip down two rows and highlights A5:K5 in yellow and so on until there is no more data in the cells (ie. Row 31). the code I have so far is this, but it only highlights every second cell in column A and does NOT extend out to column K... Sub NEWY() Range("A2:K2").Select Do Until ActiveCell.Value = Empty ...

Delete cell and move to next
Hi all Can anyone help with a macro. It is supposed to check if the cell in column A is empty and if it is delete and move the contents of B into A on deletion. I would post my workings but am too ashamed to do so. Please help. Rob Rob, Not sure if you mean any cell in column A, and by move whether you want to shift everything over. You could use: Sub Macro1() On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft End Sub which would do all cells at once, and shift everything over by one cell whereever there is a blank. If it is just a few c...

Copying certain column information quickly
As you know in Excel you could quickly copy the information you needed with a drag of the mouse. Because I need information copied repeatedly and easily for my access unfirmiliar co workers. Example JOB CUSTOMER QUOTE # A B1 110701 A(copied) A2 110701 (copied) A(copied) C4 110701 (copied) How do I do this quickly and efficiently for the computer challenged. Sorry, Access is a database, not a spreadsheet, and can't be used like one. You can write some code to select the rows that you want to ...

XL 2002: Row limit in Pivot Tables
Hello all, Using XL 2002. I was looking for this in XL's help, but couldn't find it: What is the row limit for pivot tables? I have a set of data that I know will have 48,309 rows (less than the 65,536 row per sheet), but when I try to create this pivot table I get the following error: "A field in your source data has more unique items than can be used in a PivotTable report. MS XL may not be able to create the report, or may create the report without the data from this field." I don't know if it makes any difference, but the source data is a filtered view fr...

is there a way of viewing all pages on Publisher at once?
It would be very helpful to be able to see all the pages i have created at the same time - and even better to be able to print them all on one page In print preview you can see most of the pages depending on how many, do a print screen... paste into Paint and print. Can't guarantee the results. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "HJD" <HJD@discussions.microsoft.com> wrote in message news:9534796A-69FA-48C9-8EBD-18E2F7337322@microsoft.com... > It would be very helpful to be able to see all the pag...

Quick help with excel formula
Ok, i need a forumla. this is what I want, i have two columns like this Time Start: ............. Time Finish: ............ I fill out these two columns like this Time Start: ...17:42... Time Finish: ...18:25.. Now i have another Column that says Training Length: ........ I want this to work out the time between 17:42 and 18:25 Any one help pleas -- Message posted from http://www.ExcelForum.com Hi simply =A2-A1 and format as time If your training time can span midnight use the formula =A2-A1+(A2<A1) >-----Original Message----- >Ok, i need a forumla. > >this is what ...

Correct Way to Update CProgressBar from Thread
I have a database program that currently updates the status bar while working from another thread. Currently the code it uses is a parameter passed to the thread with a pointer to the application and: pApp->m_ProgressCurrent.StepIt(); I've seen people talking about not touching dialogs from other threads, and I was wondering what the proper method would be to have it refresh the progress indicator. The controls can range from steping once every 30 seconds (for a large database) to 100+ times a second (for many small databases). What would be the correct way to update a GUI control f...

Adding 1 to a number for 30 columns. #2
Thank you all!, but garfield-n-odie, hit the nail on the head. This is a wonderful place for people to come with questions for exce and all of you are the reason why. Keep up the great work and thank yo all once again. Jason -- Jason26 ----------------------------------------------------------------------- Jason263's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1479 View this thread: http://www.excelforum.com/showthread.php?threadid=26635 ...

Is there a way to have Excel
read a spreadsheet look at various elements then create a new sheet based on what it saw? How would you do that? -- billb Read books, learn answers to things you always wanted to know. This will make you smarter than the average slug walking around out there and equip you far better than anything else to be able to resist all the horseshit temptations that are just plain stupid. --billb 2005 pivot tables... "billb" wrote: > read a spreadsheet look at various elements then create a new sheet based on > what it saw? > > How would you do that? > > -- > b...

A2002: Duplicate values in autonumber fields !!!
I'm experiencing duplicate values in autonumber fields (PrimaryKey = True, Unique = True) in A2002 database. What's going on? How could it happen? To me it seems as a SUPER BUG which should never leave Microsoft!!! Grrrrrrrr........ Did MS guys & gals test A2002 before they released it? :-( :-( :-( Vlado On Thu, 24 May 2007 12:42:26 +0200, Vladim´┐Żr Cvajniga <no.spam@thank.you> wrote: >I'm experiencing duplicate values in autonumber fields (PrimaryKey = True, >Unique = True) in A2002 database. What's going on? How could it happen? To >me it seems a...

PLEASE HELP: Reload a subform with updated columns
Hi All, How can I reload a subform to display new results? I have developed a search form in headersection and inserted a subform in the details section that displays the results. The problem here is that, the data is refreshed BUT I have a dynamic SQL statement for columns, where columns are search criterias. So basically I only want to display columns in the subform that the users have selected as their criteria. Thanks in advance sam wrote: >How can I reload a subform to display new results? I have developed a search >form in headersection and inserted a subf...

Custom Format Column
I have a gridview that I want to format a row based upon a field in the datasource. Specifically, in FieldA = True, format the row as RED. On Apr 6, 12:33=A0am, dacsilva <dacsi...@discussions.microsoft.com> wrote: > I have a gridview that I want to format a row based upon a field in the > datasource. =A0 > > Specifically, in FieldA =3D True, format the row as RED. You can do it using the row's BackColor property. The GridView offers a RowDataBound event that fires once for each row after the row has been created and bound to the corresponding record o...

Is there a way to split a sheet so columns can be different widths?
I have a bunch of worksheets that have a top part and a bottom part. The top part contains settings for various parmeters. The bottom part is a table showing calculations based on those parameters. The problem is that the parameters often need different column widths than the table. Is there some way to split a sheet into a top part and a bottom part were I can adjust the column widths independently? Column widths and row heights are properties of the entire column and row and cannot be changed. I hesitate to mention "merged cells" due to the problems they can cause. ...

Finding max row containing data...
Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan Hi! One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<>"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" <Dan@discussions.m...

Cascade delete
Hi, I need to find out if the cascade delete option is set on any of the tables in my database. Any help is greatly appreciated. Cheers Mounilk Mounilk (mounilkadakia@hotmail.com) writes: > I need to find out if the cascade delete option is set on any of the > tables in my database. Any help is greatly appreciated. Look up the catalog view sys.foreign_keys in Books Online. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL S...

Re-install a database/company after deleting the DB in SQL EM.
Can anyone list all the tables that need to be cleaned out if a company database is deleted in Enterprise Manager before the company is deleted in GP? I have mistakenly done this and would like to clear everything out and set up that same database name again. No backup yet as it was a brand-new migration database ( a poor excuse I admit) that had the migration run on it. Thought we had it by clearing out the related company entries in the SY01500, DU0000xx, SY40500, SY40502? and a few more tables, but the new company setup still bombs on the very last step of the SQL setup (loading d...

moving first word in a column to a separate column
In an Excel 2007 with 26,000 rows I have a column that contains names with 2 or 3 words. Example: John Andrew McPherson Phillip something something Anderson I would like to split this into 2 columns, one for the first name and the other for the last word (which may be the second or 3rd or even 4th). What would be a way to automatically move: John to a Given Name column and McPherson to the surname column, and Phillip to the Given Name column and Anderson to the surname column. Thanks. Jeff See Chip's site for extracting first, middle and last names. http://www.cpearson.com/excel/F...

Quick Question
I have been using Office 2007 and OneNote for some time now and have recently set up the 2010 Beta on a second system. In the 2007 version there was a way to repair office if things went flaqky. How do I do that in Office 2010? Michael On Tue, 8 Dec 2009 23:59:26 +0000 (UTC), Rev. Michael L. Burns <sdbpastor@charter.bet> wrote: > >I have been using Office 2007 and OneNote for some time now and have recently >set up the 2010 Beta on a second system. In the 2007 version there was a >way to repair office if things went flaqky. How do I do that in Office 2...

Is there a quick way to...
highlighting only the odd or even numbered rows? Thanks. Something like this should do: Sub banding() ActiveSheet.Range("A65536").Select Selection.End(xlUp).Select lastrow = ActiveCell.Row Range("A6:AK" & lastrow).Select Selection.Interior.ColorIndex = xlNone 'Change rownum to the the first row number where data occurs rownum = 6 For i = 1 To (lastrow - rownum) Step 2 Range("B" & rownum + i & ":AK" & rownum + i).Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With ...

add info to column
I have a list of over 1000 names in my a column but need to add @gmail.com to the end of all of them. How can i do that in excel? HI Lets assume your list is starting at A1, just type in B1 =A1&"@gmail.com" and copy down to A1000. HTH John "jj348206" <jj348206@discussions.microsoft.com> wrote in message news:E7E6911F-BE61-422D-BBA7-BCFB83A66656@microsoft.com... >I have a list of over 1000 names in my a column but need to add @gmail.com to > the end of all of them. How can i do that in excel? Assume names running in A2 down In B2: =IF(TRIM(...

Is there a way to compare 2 spreadsheets with Excel?
Is there a way to compare 2 spreadsheets? Almost like a Unix 'diff' command. I have several variants of certain large spreadsheets with only minor differences - and I want to review them. Find the differences and reconcile them. Any suggestions? Thx. Tmuld. > Is there a way to compare 2 spreadsheets? > > Almost like a Unix 'diff' command. > > I have several variants of certain large spreadsheets with only minor > differences - and I want to review them. Find the differences and > reconcile them. Do "minor differences" include adding o...

Quick campaign- change to all quick campaigns
Does any one know how to set the following to default. In the quick campaign view, change 'my quick campaigns' to 'all quick campaigns' Cheers Sid ...

best way to track check to credit card
M05 Standard Acct #1 - Checking - Bank ABC - outgoing check to credit card for partial payment Acct #2 - Credit Card - Bank ABC (same bank) - incoming credit via check payment as stated above again, usual monthly check written to pay the credit card payment that is due what is the way to go, keep the checking account transaction as a withdrawal (check written) -better as a withdrawal to keep the original form inact? or convert it to a "transfer" so i can match it between accounts. -better as a transfer to match between accounts and gain clarity in the budgeting process (i haven&...