Coding Duplicate Records

How can I code that a record is to be "K" kept or "D" deleted when
compared to its next row?  Example: A sorted file by Address

BEFORE
LastName            Address                                  Sales     
Date            Keep/Delete
1.  WASSEM	0N642 TITUS PL	                67,500 	6/25/2001	
2.  HART	                0N655 E WEAVER CIR	70,000 	3/19/2001	
3.  HART	                0N655 E WEAVER CIR	40,000 	3/19/2001
4.  HART	                0N655 WEAVER CIRCLE	33,700 	6/19/1998
5.  POYTH	                0N662 W WEAVER CIR	294,690 	6/21/2000	
6.   BLAND	0N670 GREEN PL	                25,000 	3/20/2000

AFTER
LastName            Address                                  Sales     
Date            Keep/Delete
1.  WASSEM	0N642 TITUS PL	                67,500 	6/25/2001    K	
2.  HART	                0N655 E WEAVER CIR	70,000 	3/19/2001    K	
3.  HART	                0N655 E WEAVER CIR	40,000 	3/19/2001    D
4.  HART	                0N655 WEAVER CIRCLE	33,700 	6/19/1998    D
5.  POYTH 	0N662 W WEAVER CIR	294,690 	6/21/2000    K	
6. BLAND	                0N670 GREEN PL	                25,000
	3/20/2000    

Reasoning:
Wassem 1. not equal to Hart	=K
Hart 2. = Hart 3. 70K> 40 & Latest Date =K
Hart 4. not equal to Poyth BUT should be =D because only one "Hart" can
be coded =K

Can This be done with cell formula in "Keep/Delete"  or is this a task
for a de-dupe facility & the way to go?  I need to use the K records as
well as analyze the number of D & K to measue file integrity.  Thanks


-- 
HWmR
------------------------------------------------------------------------
HWmR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28580
View this thread: http://www.excelforum.com/showthread.php?threadid=482425

0
11/5/2005 7:36:41 AM
excel 39879 articles. 2 followers. Follow

1 Replies
682 Views

Similar Articles

[PageSpeed] 44

Here is a formula for the first row, just copy down

=IF(COUNTIF($A$2:$A$8,A2)=1,"K",IF(AND(C2>C3,SUMPRODUCT(--(A$1:A1=A2),--(E$1
:E1="K"))=0),"K","D"))

-- 

HTH

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


"HWmR" <HWmR.1y0jom_1131176401.5607@excelforum-nospam.com> wrote in message
news:HWmR.1y0jom_1131176401.5607@excelforum-nospam.com...
>
> How can I code that a record is to be "K" kept or "D" deleted when
> compared to its next row?  Example: A sorted file by Address
>
> BEFORE
> LastName            Address                                  Sales
> Date            Keep/Delete
> 1.  WASSEM 0N642 TITUS PL                 67,500 6/25/2001
> 2.  HART                 0N655 E WEAVER CIR 70,000 3/19/2001
> 3.  HART                 0N655 E WEAVER CIR 40,000 3/19/2001
> 4.  HART                 0N655 WEAVER CIRCLE 33,700 6/19/1998
> 5.  POYTH                 0N662 W WEAVER CIR 294,690 6/21/2000
> 6.   BLAND 0N670 GREEN PL                 25,000 3/20/2000
>
> AFTER
> LastName            Address                                  Sales
> Date            Keep/Delete
> 1.  WASSEM 0N642 TITUS PL                 67,500 6/25/2001    K
> 2.  HART                 0N655 E WEAVER CIR 70,000 3/19/2001    K
> 3.  HART                 0N655 E WEAVER CIR 40,000 3/19/2001    D
> 4.  HART                 0N655 WEAVER CIRCLE 33,700 6/19/1998    D
> 5.  POYTH 0N662 W WEAVER CIR 294,690 6/21/2000    K
> 6. BLAND                 0N670 GREEN PL                 25,000
> 3/20/2000
>
> Reasoning:
> Wassem 1. not equal to Hart =K
> Hart 2. = Hart 3. 70K> 40 & Latest Date =K
> Hart 4. not equal to Poyth BUT should be =D because only one "Hart" can
> be coded =K
>
> Can This be done with cell formula in "Keep/Delete"  or is this a task
> for a de-dupe facility & the way to go?  I need to use the K records as
> well as analyze the number of D & K to measue file integrity.  Thanks
>
>
> -- 
> HWmR
> ------------------------------------------------------------------------
> HWmR's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28580
> View this thread: http://www.excelforum.com/showthread.php?threadid=482425
>


0
bob.phillips1 (6510)
11/5/2005 11:45:31 AM
Reply:

Similar Artilces:

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

duplicate emails received
I've been receiving duplicate emails. This doesn't happen with all the emails I receive, only with some of them. "Leave a copy on the server" is not checked. I don't know the reason why this happens. Can somebody help me. Thanks in advance. ...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

Trying to make a duplicate roster with a button. #2
That seems like it would be easier, but sometimes easier isn't what i wanted... : -- virte ----------------------------------------------------------------------- virtex's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1488 View this thread: http://www.excelforum.com/showthread.php?threadid=26516 ...

fields taking data from one record to another
I have a coworker who is experiencing problems with a database that she created. The main table in this database lists client information, including first name, last name, address, etc. Within the past 1-2 weeks, they've noticed problems where people's information is incorrect when they pull records back up. For example a record this morning came up with a man's name being switched from Lawrence to Elizabeth... In every case, the field now contains the same information that is in the record before it.. so for example in this case the record before the one for Lawrence was for a wom...

Modal Dialog Record and Replay
Hi, How do we do record and replay of modal dialog in a large code base ? I have created a new dialog class, say CFooDialog derived from CDialog and overridden OnInitDialog and OnOk. In OnOk, the control values are dumped into a file and in OnInitDialog the values are read from the file and controls populated. In replay mode I donot want the dialog box to be shown. For that I have overridden DoModal and done the following INT_PTR CFooDialog::DoModal() { CSagDumpDialogApp *pApp = (CSagDumpDialogApp *)AfxGetApp(); if(pApp->b_ReplayMode) { return IDOK; } return CDialog::DoModal(); } ...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Counting Cells in a column depending on another column but excluding duplicates
Hello, I would like to count the total in a column depending on another column, but exclude duplicates. I my case I would like to know how many employees (column A) have taken a course in 2010 (column B). Column A has duplicate names. Column B has 2009 and 2010 as the year the course was taken. I want to count 2009 and 2010 separately. I named the range of column A course_attendees and column B course_taken Name Course Year Wilbert Bugay 2009 Wilbert Bugay 2009 Zahid Gul 2009 Zin Minn Lwin 2009 Zin Minn Lwin ...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

Duplicates!
Why do the songs in my media player library often duplicate or tripulicate themselves, so i end up with a library three times the size it should be. I have on occassions gone through and deleted all the replicated songs, but over a period of time they seem to "grow again" filling my library again with unwanted duplicates, taking up more space and memory. Is there anyway i can stop this happening????? Thanks. What version of WMP/Windows? First, make sure that WMP isn't monitoring the same folder twice. Duplicate songs can appear when, for example, both C:\Music a...

Find duplicates in a column
Hi, I have 6000 emails addresses in a col and wish to know if there are any duplicates. How can I do this please. I have Excel 2002. rock Assuming your emails are in A1 to A6000, in B1 enter =COUNTIF($A$1:$A$6000,A1) copy down B1 all the way to B6000 Apply a conditional format of red pattern on cells B1 to B6000 where Cell is greater than 1 and all duplicates will be shown in red. "rock" wrote: > Hi, > > I have 6000 emails addresses in a col and wish to know if there are any > duplicates. > > How can I do this please. > > I have Excel 2002. > ...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

problem with duplicate names in GAL after replacing server
Recently we had a problem in our company with an exchange server in one of our sites. The exchange server 5.5 crashed and couldn't be restored. We did the following: - extracted all the mailboxes from the database into .pst files - installed a new exchange server with the same name as the last one - created all the users (10-15 of them) - imported the .pst files into their new mailboxes. The problem is - now we have in other sites duplicate names for those users. We have current entries that are now working, and duplicate entries for users with the same names that were on the old ...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

advance filter unique records except on column
Is there a way to filter a database to find unique records where one column is ignored? I have over 6,000 records with the following columns. Cust_rec, Bco, pol_csr, Expires, Cust_name, Cust_attn, Pol_type I want to eliminate duplicates (there are many) but the last column "policy type" should be disregarded. Any suggestions? You could use data|filter|advanced filter (in xl2003 menus). Select the first 5 columns in your table and excel will use that in the advanced filter dialog. Make sure you check the "Unique records only" checkbox. ...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

How do you sort records in a table using Database?
I am suppose to sort the table by the Records Series field and then by the Record field. How do I do this? You don't sort a table. Create a query based on the table and sort that. Hope this helps -- Wayne Manchester, England. "Sonja" wrote: > I am suppose to sort the table by the Records Series field and then by the > Record field. How do I do this? ...

Duplicate Customers
Howdy, First-- sorry I keep duplicating my posts...How about a Preview pane? Microsoft? I have customers who appear multiple times in my database.. (due to different spellings or just duplicate entries) this is not a problem if they have no purchase history, but when both instances have significant histories it doesn't allow me to get a true lifetime value or number of visits etc...Often it is customers who visit often, but go by a nickname or a name different than on their credit card. This must happen all the time for brick and mortar retailers with a webstore, No?? I want to m...

use IUrlHistoryStg2 to get the history record,but how to get the user name ?
IUrlHistoryStg2 can tell you the url,titile,some visited time information.But how can I know who visit this url ? Anyone can give me some hint or code ? Thanks very much ...

Recording macros with relative row and column relationships
I am trying to copy information in Excel 2003 from one row to a new row on separate worksheets, using a macro. However, the macro records actual rows and columns (even though the "$" is not present), which I have verified by running the macro with a newly created row. (It still copies the previous row to the other worksheets and not the new row.) How do I record a macro with relative rows/columns, so that I can copy the data from each newly created row on the first worksheet to the remaining 14 worksheets in the workbook? (Please note that I am not copying all the data fro...