deleting duplicate cells

I am back again... Thanks to everyone's help here last time, I was able
to finish all my work and do it correctly. 

Ken- I asked last time i was here about deleting duplicate cells. Some
of the names(address, etc) are repeated in my sheet. I want to delete
the extra cell of the people who are in here twice. Not jus the cell
but their record, name, address, city, state zip when they are in their
twice so that they will only be listed once. 

You told me how to do this once but i cant find where it is on the
board. Thanks for all the help.... BR4


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

0
11/7/2003 3:20:58 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
437 Views

Similar Articles

[PageSpeed] 12

Here's one of several ways:

1. Assuming the name is in column A, put this in row 2 of 
an open column and copy down:
    =IF(COUNTIF($A$2:A2,A2)>1,"Ken",0)
2. Select this column and press F5.
3. Click Special > Formulas and uncheck everything under 
Formulas except "Text".
4. Press <Ctrl><->.
5. Select "entire row".

HTH
Jason
Atlanta, GA

>-----Original Message-----
>
>I am back again... Thanks to everyone's help here last 
time, I was able
>to finish all my work and do it correctly. 
>
>Ken- I asked last time i was here about deleting 
duplicate cells. Some
>of the names(address, etc) are repeated in my sheet. I 
want to delete
>the extra cell of the people who are in here twice. Not 
jus the cell
>but their record, name, address, city, state zip when 
they are in their
>twice so that they will only be listed once. 
>
>You told me how to do this once but i cant find where it 
is on the
>board. Thanks for all the help.... BR4
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from 
http://www.ExcelForum.com/
>
>.
>
0
jason.morin (561)
11/7/2003 3:39:24 PM
Hi Bill - You are probably reading this reply in ExcelForum, so click on the
Search icon at the top right of the sheet once you have clicked into the
miscellaneous group, and then search for your userid billrebels4, and it will
bring up all the threads you have been involved in, including the one you are
referring to.  You can then drill down through each of the answers.  Post back
if you don't have any joy :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"billrebels4" <billrebels4.wj57n@excelforum-nospam.com> wrote in message
news:billrebels4.wj57n@excelforum-nospam.com...
>
> I am back again... Thanks to everyone's help here last time, I was able
> to finish all my work and do it correctly.
>
> Ken- I asked last time i was here about deleting duplicate cells. Some
> of the names(address, etc) are repeated in my sheet. I want to delete
> the extra cell of the people who are in here twice. Not jus the cell
> but their record, name, address, city, state zip when they are in their
> twice so that they will only be listed once.
>
> You told me how to do this once but i cant find where it is on the
> board. Thanks for all the help.... BR4
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003


0
ken.wright (2489)
11/7/2003 3:40:25 PM
Reply:

Similar Artilces:

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Avoid protected cell warning on BeforeDoubleClick WS event
I am creating an event scheduling worksheet. A grid is generated with days of the month x-axis and users y-axis. Where an event occurs this is logged by a hidden event ID in the appropriate day cell. In order to prevent this ID from being overwritten I protect the worksheet. What I am trying to do is have a worksheet DoubleClick event which either. 1) Captures the event ID in the underlying cell, and opens a custom form for editing the event, or 2) Recognises that no event exists and opens a custom form for logging a new event. What I have tried is to unprotect the workshee...

Miscellaneous words appear in Excel 2002 cells
I am working on an Excell 2002 workbook and when I put my cursor in a cell , spurious words get entered into the cell e.g. "and thee the and ...." is one example. I have cable modem and it feels like "someone" has taken control of my PC - by the way I have also noticed this hapening in MS Word but is not as bad as Excel - can some one help. thanks KK Have you enabled speech tools? Click Tools / Speech and examine your options. /i. "KK" <anonymous@discussions.microsoft.com> wrote in message news:061d01c39d1c$36fc85f0$a601280a@phx.gbl... > I am w...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Where is the Delete Messages options?
I've looked and looked without success. I'm pretty sure I saw it before. Its where it asks you if you want to save a copy of messages on the server and then should give an option for it to be deleted from the server when you delete it from your Deleted folder in Outlook. That seems like a good way of syncing things. Gary Gary wrote: > I've looked and looked without success. I'm pretty sure I saw it before. > Its where it asks you if you want to save a copy of messages on the server > and then should give an option for it to be deleted from the...

Unable to display or delete a folder
Hello, I have OL 2002 with the XP Home OS. I had a Contacts sub-folder which I just used for information. I forget now just what went wrong, but I could no longer access any of the Contacts in that sub-folder. (The default Contacts folder was working fine.) Since the sub-folder was not really critical I thought I would just delete it. That seemed to go fine and the sub-folder went to the Delete folder. However, after that I could not only delete that sub-folder from the Delete file but I can't use the "Empty 'deleted items' folder" command to delete files more recently s...

Can I delete parts of Office?
I recently loaded Office 2004 on my iBook but want to conserve hard drive space for an app i just picked up, and was thinking about deleting all the MS apps except for Word and Excel. Can I do this without messing up Word and Excel to the point that I need to reinstall? I would just try it, but I'm away from home and don't have access to my Office install CD. Any help that could be provided would be great. Thanks <beau.keyes@gmail.com> wrote: > I recently loaded Office 2004 on my iBook but want to conserve hard > drive space for an app i just picked up, and was thinki...

Delete/Copy Path Too Long Files
http://LongPathTool.com - Delete/Copy Path Too Long Files Filename is too long? Computer Complaining Your Filename Is Too Long? The Long Path Tool Can Help While most people can go about their business blissfully unaware of the Windows Explorer path length limit, others are not so lucky. Everything seems normal until one day you download a file or create a new folder in a directory and try to use it, and it happens. You try to open or delete your file and the annoying =93path too long=94 appears on screen, telling you that Windows Explorer can=92t handle the file because the path is longer th...

Sequential number
Is there a way to squentially number a column so that if you insert or delete rows in the middle the numbers will readjust? if you use A1+1 and copy it down, you get a #REF if you delete; and a blank cell if you don't (and then you have to use the fill handle from above the blank cell to resequence? Any assistance would be greatly appreciated. Suzan Enter the formula =ROW() in the first cell and copy it down. (You might like to add or subtract a constant depending on how you want the cells numbered). When you delete a row this will automatically re-number. If you insert a row, thoug...

***Delete UNREAD email >90 Days (Exchange 2003)
I'm trying to delete all UNREAD emails older then 90 Days. Recipient Policy will only let me select the date but there isn't a selection for UNREAD emails. Exchange 5.5 Cleanup Agent (I think that's what it was called) had a check box for UNREAD emails. Is there way to do this in Exchange 2003, or is there 3rd party app that will do this? Boy I miss Exchange 5.5!!! James jdduong@comcast.net when you create a recipient policy (mailbox-managed), you can set the age in days (i.e. 90 days) and size in KB for all items in the selected folder for which the policy applies t...

Duplicate emails #14
Running Exch 2003 Outlook 2003 client. Some users receive duplicate emails from internal users. The sender sends one message or reply to the recipient, and the recipient recives 2 emails on the same message Has any one ran into this issue... Any info would be appreciated. Thanks Steven I am having the same problem and came across this: http://support.microsoft.com/default.aspx?scid=kb;en- us;212934&Product=out2K It doesn't provide an answer but sounds like what is happening to me. The mail that gets duplicate for me is filtered into a second inbox folder I created for "...

stop duplicate entries using validation
I have a named range (one entire column) where I would like to enter SSNs using the data form, But I do not want any duplicate entries. I know the validation function can be used! But I can't seem to find the way. can anyone help? Dennis, Use Data Validation. Select column A, goto menu Data>Validation, change the Allow option to custom, and enter a formula of =COUNTIF(A:A,A1)=1 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dennis" <denneca@earthlink.net> wrote in message new...

Delete 3 end row
Hi I want to delete three end row of any sheet. any sheet has different row number. Thank's for any help. your's Hi, This will clear the last 3 rows of the active sheet. Note I the on error line is to cope when there are less than 3 rows on the sheet Sub del_Last_3() Dim LastRow As Long On Error Resume Next LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row ActiveSheet.Rows(LastRow - 2 & ":" & LastRow).ClearContents End Sub -- Mike Whe...

Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i would like , to delete the full stop. How can i do this. I cant do it in excel as the number of records that i have is over 200000. hi, ma1000 wrote: > In one of my tables i have a field that has a text values like '123.23123' i > would like , to delete the full stop. How can i do this. I cant do it in > excel as the number of records that i have is over 200000. Create a update query, use Replace([yourField], ".", "") as new value. mfG --> stefan <-- ...

Sum of a cell in all worksheets?
I usually just use the search here and quickly find what I need, but can't seem to get it work this morning I am trying to get a sum of a certain cell in all the sheets in workbook. For example of what I mean, Sum(all worksheets, d64). Ca someone possibly help me out Thanks Michae Hi Michael, Look up 3D in your Excel Help. Surely you do not want to include the same sheet. if sheet2 is your second sheet tab and "sheet 24" is the last tab. =SUM(sheet2:'sheet 24'!C14) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: htt...

Insert a graphic in a cell
I'd like to be able to enbed a logo in a cell. Not possible. Graphic objects reside on the drawing layer "above" the cells. Cells can contain formulae or values only. Workaround. Insert your graphic, and size it so that it exactly covers the cell. Right-click it, choosing Format Picture. In the Properties tab of the Format Picture dialog, choose the Move and Size with cells radio button. In article <F253C07B-E71A-445E-B612-0189187A09D9@microsoft.com>, Pete_Escher fan <Pete_Escher fan@discussions.microsoft.com> wrote: > I'd like to be able to enbed a...

Outlook won't let me delete e-mail
I'm using Outlook 2002. Every time I try to delete an item, it tells me "Errors have been detected in the file ...outlook.pst. Quit all mail-enable applications, then use the Inbox Repair Tool." So I go and use the Inbox Repair Tool, SCANPST. I scan outlook.pst (n errors come up), re-open Outlook, try to delete an item and get th same error message. Can someone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Can you delete them if you hold shift-delet...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

Ignore Blank Cells
I update a workbook weekly in separate worksheets. My master worksheet links to the appropriate cells for the updated data. These are simple percentage numbers (not forumlas, etc.) and only need to be a one-on-one link. The problem I have is that if one of the cells is blank, it returns a 0 and I need to to stay blank. Any ideas? There are about 57,000 cells I am working with so I really don't want to have to update manually all blanks. Hi SEF, As long as you want to hide all zero values on a sheet you can use in excel 2003 From the Tools menu select Options On tab V...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

outbox message will not send or delete
In outbox I have a file with a video clip in windows media. It will not send and I can not delete. Dialog box says outlook has already begun sending. thanks for any help ...

Clipart will not scale with array of cells
I want to print out an array of cells, some of which contain clipart. When I try to scale up the array to fill the printed page, the clipart scales differently (the clipart objects move down on the page from their normal position within the cells). The artwork size does not change, nor its spacing; it's as though the clipart is reacting to a larger top margin. ...

formula for visible cells
Can I copy a worksheet so that the new worksheet looks exactly like the visible portion of the old worksheet. For example, if I filter and sort, I only want what is left visible to appear in my new worksheet, and I want it to do it automatically without my having to copy and paste. Automatic means VBA code. See Ron de Bruin's site for copying filtered results. http://www.rondebruin.nl/copy5.htm Gord Dibben MS Excel MVP On Tue, 4 May 2010 11:32:01 -0700, jpstormy <jpstormy@discussions.microsoft.com> wrote: >Can I copy a worksheet so that the new workshee...

How do I copy the wording of a cell into a tab?
I have a large workbook with the tab titles available to copy to save typing every single one, is it possible to populate the tabs by a form of copy-paste You want your sheet tab name to reflect the value of a particular cell within that sheet? You want to copy a sheet and rename the copy to the value of a cell within that sheet? Please explain in more detail. The lack of sentence structure makes it difficult to get a clear picture. Gord Dibben MS Excel MVP On Sun, 24 Jan 2010 11:18:01 -0800, Jamie <Jamie@discussions.microsoft.com> wrote: >I have a large wo...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...