Duplicate entries

I have a column with many numbers that are duplicates.   How can excel help 
me get rid of the duplicates with out doing each individual line one at a 
time?
0
Keggarboy (1)
2/12/2005 12:37:05 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
463 Views

Similar Articles

[PageSpeed] 39

One way ..

Assume the numbers are in col A, in A2 down

Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,"X","")
Copy B2 down

This'll flag all duplicates in col A with an "X"

Now do a Data > Filter > Autofilter on col B
(put a label in B1 first)

In the droplist in B1, select "X"

This filters out all the duplicate rows

Select the row headers for the duplicates
(this will be all the "blue" colored filtered row headers)
Right-click > Delete Row

Remove the Autofilter
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Keggarboy" <Keggarboy@discussions.microsoft.com> wrote in message
news:92BF6F73-884D-4CD8-ABD7-84A2A0F106CC@microsoft.com...
> I have a column with many numbers that are duplicates.   How can excel
help
> me get rid of the duplicates with out doing each individual line one at a
> time?


0
demechanik (4694)
2/12/2005 12:58:21 AM
Max wrote:
> One way ..
> 
> Assume the numbers are in col A, in A2 down
> 
> Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,"X","")
> Copy B2 down
> 
> This'll flag all duplicates in col A with an "X"
> 
> Now do a Data > Filter > Autofilter on col B
> (put a label in B1 first)
> 
> In the droplist in B1, select "X"
> 
> This filters out all the duplicate rows
> 
> Select the row headers for the duplicates
> (this will be all the "blue" colored filtered row headers)
> Right-click > Delete Row
> 
> Remove the Autofilter
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1� 22' N  103� 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Keggarboy" <Keggarboy@discussions.microsoft.com> wrote in message
> news:92BF6F73-884D-4CD8-ABD7-84A2A0F106CC@microsoft.com...
> 
>>I have a column with many numbers that are duplicates.   How can excel
> 
> help
> 
>>me get rid of the duplicates with out doing each individual line one at a
>>time?
> 
> 
> 

I also need help with this but when I do as you answered I get the 
message "Wrong in formula" and :A2,A2 is highlighted in the formula. I 
use a swedish localized Excel 2003 where the formulas use swedish names 
but english should work as well right?
0
dammskog (1)
4/30/2005 7:21:36 AM
No, you would need to use

=OM(ANTAL.OM($A$2:A2;A2)>1;"X";"")

it will translate English formulas if you get a workbook with them but you 
can't enter English formulas

-- 
Regards,

Peo Sjoblom


"Christopher" <dammskog@hotmail.com> wrote in message 
news:%23Alz9UVTFHA.584@TK2MSFTNGP15.phx.gbl...
> Max wrote:
>> One way ..
>>
>> Assume the numbers are in col A, in A2 down
>>
>> Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,"X","")
>> Copy B2 down
>>
>> This'll flag all duplicates in col A with an "X"
>>
>> Now do a Data > Filter > Autofilter on col B
>> (put a label in B1 first)
>>
>> In the droplist in B1, select "X"
>>
>> This filters out all the duplicate rows
>>
>> Select the row headers for the duplicates
>> (this will be all the "blue" colored filtered row headers)
>> Right-click > Delete Row
>>
>> Remove the Autofilter
>> --
>> Rgds
>> Max
>> xl 97
>> ---
>> GMT+8,  1� 22' N  103� 45' E
>> xdemechanik <at>yahoo<dot>com
>> ----
>> "Keggarboy" <Keggarboy@discussions.microsoft.com> wrote in message
>> news:92BF6F73-884D-4CD8-ABD7-84A2A0F106CC@microsoft.com...
>>
>>>I have a column with many numbers that are duplicates.   How can excel
>>
>> help
>>
>>>me get rid of the duplicates with out doing each individual line one at a
>>>time?
>>
>>
>>
>
> I also need help with this but when I do as you answered I get the message 
> "Wrong in formula" and :A2,A2 is highlighted in the formula. I use a 
> swedish localized Excel 2003 where the formulas use swedish names but 
> english should work as well right? 

0
terre081 (3244)
4/30/2005 7:26:01 AM
Reply:

Similar Artilces:

Prevent duplicate entries in Excel 2000
I'm trying to create data validation to prevent users from entering duplicate rows in Excel 2000. I tried this also with conditional formatting to turn the newly entered line red, which didn't work either. This is a name and address list where I want to check the first name(B), last name(C), address line 2(F), address line 1(G), and zip(J) for being a duplicate row. I highlighted the col., data/validation/settings/custom/and entered formula For col. B the formula is OR(COUNTIF(C:C,B2)>0,COUNTIF(F:F,B2)>0),COUNTIF(G:G,B2)>0,COUNTIF(J:J,B2)>0) For col. C the formula is...

Removing duplicate messages
I have literally thousands of duplicate messages spread over dozens of folders in Oulook 2003. Is there a built-in method or third-party utility to find duplicates and delete them? Thanks! Hi! 3rd party only, e.g.: http://www.mapilab.com/outlook/duplicate_remover/ > -----Original Message----- > From: Andrew Chalk [mailto:achalk@XXXmagnacartasoftware.com] > Posted At: Monday, September 06, 2004 9:17 AM > Posted To: microsoft.public.outlook > Conversation: Removing duplicate messages > Subject: Removing duplicate messages > > I have literally thousands of duplicat...

Money 2002 OFX download has incorrect Payee entries
When downloading credit card activity Money 2002 replaces a payee at random with another payee from a prior transaction. I have seen how to correct this in Money 2003. The other responses on this newsgroup suggest: Accounts&Bills->Categories&Payees->PreferredPayeeNames lets you change replacement of names with names of your choice. While there continue to the Payee Rules Manager that lets you ignore words and phrases such as Check, ATM Withdrawal, etc. However that does not work for Money 2002. Anyone have any suggestions for Money 2002? ...

Unbound Data Entry Form
Bit of a "noob" question but here goes.... I am using an unbound form for data entry purposes. I am using an unbound form as I have some VBA code which checks that mandatory fields are filled in first. This works fine where all the fields are text boxes (i.e. not combos or list boxes) but the problem I'm having is that the parent table "tblContracts" has 4 lookup tables (which are used for combos with various fields on the form) and these are numeric fields which link between the tables (i.e. In the parent table the field would be SupplierID whick li...

Receiving duplicates of each email
When I get email, I am getting two copies of each message I receive. They are identical and are happening from senders both in and outside of the office. We are running Exchange 5.5 and Outlook 2000. Does anybody know what causes this? Thank you, Bill ...

Batch approval for quick entry
Although we have the option to enter an approval password for Quick Entry, it never prompts us for the pswd when posting the quick entry. Is it b/c of the way that it auto-posts a quick entry transaction? Or is there some other setting I need for this? It works fine for regular J/V entries. Any suggestions are greatly appreciated! Tracy: You don't actually have the option to enter an approval password for a Quick Entry. If you look closely at the verbiage in the Posting Setup window, the password is for "Require Batch Approval". Since Quick Entry doesn't utilize batch...

Error while importing organization due to duplicate users (one of which is Disabled)
Hi, I am receiving a "There is more than one Microsoft Dynamics CRM user mapped to the same Active Directory account. You cannot map more than one Microsoft Dynamics CRM user to the same Active Directory account." error message while attempting to import an organization. This seems to be occurring because I have 2 Users setup, one of which is Disabled, with the same domain login: User A - DOMAIN1\usera (Enabled) User A1 - DOMAIN1\usera (Disabled) Since you can't seem to delete users in CRM how can I get around this? I am trying to import a copy of our Production database in...

Remove Duplication from Validation List?
Hi. I want to use a range of cells, all in part of a column, as a validation list source. Here's the formula I have for the validation list source in the cells where the validation list should be applied: =(OFFSET(RangeStart,1,0)):(OFFSET(RangeEnd,-1,0)) The problem I'm having is that if duplicate values exist between RangeStart and RangeEnd, I get duplicate values in my validation list. How could I eliminate duplication from the displayed pulldown validation list? Thanks for any input! Ken See reply in .Functions Biff <kk_oop@yahoo.com> wrote in message news:113746373...

how to prevent sent items to be duplicated in the sent items fold
All of my sent messages in Outlook show up in the sent items folder in duplicate. How do I prevent that from happening? To make it less of a uessing game, how about telling us: version of Outlook type of mail store used Has it always behaved this way? If not, what did you do just before it changed? Regards Judy Gleeson MVP Outlook "Bev" <Bev@discussions.microsoft.com> wrote in message news:0DB0C3DA-4F3C-450B-BE32-E43DAF1B95F5@microsoft.com... > All of my sent messages in Outlook show up in the sent items folder in > duplicate. How do I pre...

Credit Card entries
I have read some of the posts after I Searched this topic, but I am still too dense to understand how to do credit cards. I have read how to do it in the Money Help, but when I actually do it I always end up looking as if I owe bazillions. Here are the factors that complicate their simple directions: *I like to input my data each week, not monthly when I get my statement. I think if I set it up correctly that I could still do this, but durned if I know how. *Each month I pay off my credit card, usually not just the balance shown but however much I've accrued by the date that I ...

Duplicate mailbox created after ADMT
I have an interesting issue. We have 4 domains (1 forest). When I run User Account ADMT from one domain to another (intradomain), sometimes, it will create a duplicate mailbox on the first storage group. My question is, how would you try to troubleshoot this? As for the fix, all I had to do was delete the mailbox that is attached to the user account, then reconnect the valid mailbox to the user account. But, I should really try to fix the root cause of this issue, but I do not know where to begin the troubleshooting. Any tips or suggestions will be greatly appreciated. Thanks. ...

Marking Duplicates and/or Uniques Between 2 Worksheets
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I need to mark either duplicate accounts or unique accounts between two worksheets. Each row is an account identified by a unique, variable-length alphanumeric label located in col 1. There may be 5,100 to 5,500 rows (accounts) on a worksheet. About every half year we get a new worksheet containing roughly the same number of accounts but only about 80% of the accounts are the same. The accounts on worksheet 2 are now the current accounts. I can't just substitute the new worksheet, however, because I have annotat...

Returning Last Instance of a Duplicate
I have a table with Multiple records for the same serial number. I need to return only the latest record and the next previous record based on the date of the occurence. Can any one help me wit this scenario? -- Message posted via http://www.accessmonster.com You can use one of the four methods presented at http://www.mvps.org/access/queries/qry0020.htm Hoping it may help, Vanderghast, Access MVP "Dopple444 via AccessMonster.com" <u33839@uwe> wrote in message news:724722e065db5@uwe... >I have a table with Multiple records for the same serial number. I need to &...

Records in a query between a one-many rel. not allowing data entry
Hey all, I'm fairly new to database design, and have a problem with a query to add new information into tables in Access 2003. I have a repair table in which I am trying to add repair information, and at the same time information to an RMA table that has RMA information. The tables both have an "RMAID" field, which is an autonumber and is the primary key in the RMA table. The repair table is the "many" side and the RMA table is the "one" side. The RMA might have more than one repair tied to it. So far nothing unusual from what I have seen in example da...

Programmable Data Entry & Conditional Formatting
I work as a volunteer for the British Red Cross, I am currently working on developing a Public Events Database for my local office, I have started off using a base that another volunteer built for a different office but it needs to be customised to the needs of the office i am working for, I have done a bit of customiation on it already using my existing skills in regards to access but there are a few things i want to do with the database which i do not know how to do just yet which i will list below. 1. in the Volunteers Form theres a list of 23 certificates a volunteer can h...

Removing duplicate files
Hi Is there a good utility to detect duplicate files based on content and then to remove them? Thanks Regards 'Fast Duplicate File Finder Identifies Extra Copies :: the How-To Geek' (http://www.howtogeek.com/howto/the-geek-blog/fast-duplicate-file-finder-easily-identifies/) -- whs ...

Filter duplicate data in a column
I have a spreadsheet (Excel XP) with numerous columns and 65,000 rows of data. I need to filter the data, preferably hiding, rather than deleting, any duplicate data that appears in one column. This will result in the rows of data reducing dramatically. If I can't hide the duplicate data, is it possible to copy the data, without duplicates, to a new sheet within the workbook? You can use an Advanced Filter to show the unique items in place, or on a new sheet. There are instructions in Excel's Help, and here: http://www.contextures.com/xladvfilter01.html In the criteria...

Duplicate Parameters
I am using Access 2002. I have a report that is grouped by txtCourseTitle and the information is in the group footer. I have another report that I want to show up on this report so I dragged it into the same group footer. Each of these reports has their own parameters to enter for Year and Quarter. When I run the report the first reports parameters run, I enter both of them, then the report that was dragged into the group footers' parameters run. The problem is they run twice. Can anyone tell me why and know how I can fix it to not do that. If the report is run outside...

Duplicate contacts in smart phone
Hi, I have duplicate contacts appearing on my windows smart phone. i have installed the outlook client integration, however does anyone know why the contacts are appearing multiple times on my smart phone contacts list? -- CRM Consultant Are there multiple copies of the contact record in your Outlook contacts? I'm thinking maybe you already had some of the contacts in your Outlook Contacts, and when you synched with CRM it brought another copy in. The way the CRM/Outlook contacts synch works is based on your local data rules in the Outlook CRM client, it brings in any contacts th...

I need to delete duplicates inside of spreadsheet
I have an email database that has duplicate entries and I need to know how to search them and delete duplicates. Then I need to merge 2 different spreadsheets together. Help!!! Hi, Copy and paste all the email addresses into one list. Select the first cell in the list. From the Data menu, select Filter > Advanced Filter. In the advanced filter window: Check the "Copy to another location" thingy, Tick Unique records only thingy, In the Copy to field, enter the start cell for where you want the list of uniques to go OK Unique items are now listed in a new place, and you can d...

Field result duplicates following join
I hope someone can help me. I have two queries: USDBAL has a result of Date Movement 1/1/08 123.45 2/1/08 234.56 3/1/08 345.67 The numbers represent the movement in balance for that day From another calculation I have a query result PURCHASES Date Transaction 1/1/08 50.00 2/1/08 24.00 2/1/08 75.00 I want to join the two queries to show Date Movement Transaction Total Move RunSum 1/1/08 123.45 50.00 173.45 173.45 2/1/08 234.56 24.00 258.56 432.01 2/1/08 75.00 75.00 ...

Consolidate multiple entries
Hi All, I've a query data which has multiple entries in Column A and B and different info in Columns C-H. I'm trying to consolidate this data, but couldn't find a solution. Is there any way to delete blank fields and reduce the number of rows. I've over 45,000 rows data. A B C D E F G H 123 Zip EMP1 123 Zip EMP2 123 Last Name 123 Last Name EMP2 STAPLETON 112 ODS SC066 111208 112 ODS SC284 112 ODS 112 ODS SC176 I want the final data to look like this A B C D E F G H 123 Zip EMP1 EMP2 123 ...

Limit data entries
I have 3 tables and a main form with a continuous sub form. Table one is called tFuel (FuelID, FuelName, FuelType) example 1, JetA, Liquid 2, Kerosine, Liquid 3, Propane, Gas 4, JetA, Duel 5, Kerosine, Duel 6, Propane, Duel 7, JetA, Multi 8, Kerosine, Multi 9, Propane, Multi The second table (tPerfEmissionGua) holds the choice of (Gas,Liquid,Duel or Multi) in the (FuelTypeChoice) field. Can only be one of the choices for the combox (cboFuelType) that is in the masterform called (fPerfEmissionGua) The Third table (tPerfEmissionGua) holds the choices of (JetA, Kerosine,Propane...) This is ...

need help in managing my duplicate contacts
is there any utility out there that can help me in merging my duplicate contacts. I imported my old address book and it seem like it was not too successful in combining some of my address. I hate to do it manually. I need a utility that could help me in the cross checking and merge detected duplicate with ease. anyone seen such or am I wishing? thanks Steve In news:qavff.1005$rM2.872@newsread3.news.pas.earthlink.net, StvyLife <trial13@hotmail.com.no> typed: > is there any utility out there that can help me in merging my > duplicate contacts. I imported my old address book a...

Assembly Serial/Lot Entry Window
I'm building an assembly (food mfg plant), one component's lot number does not appear in the 'Available Serial/Lot' window (I'm FIFO - apparently it was used up in another build). What are the effects of typing in a NEW lot number? How will the new lot number be assigned a value? Will it screw up my inventory counts? Will it hose GL? I could just pick an available lot number, but I want to maintain accurate Lot trace-ability thru my finished goods to the component level. Any one have any idea? We're using Serial not lots and if you enter a new serial number a...