Checking for Duplicates

Hi,

I am new to this forum, infact this is my first thread. I have been
reading various threads and replies and I must say that I am impress
with the level of knowledge available here.

Now, I would like assistance in figuring out a solution.

I have 2 sheets with following data (for example):
Sheet 1:

Cert #                        Amount
CA 1000345              $1,000.00

and the above continue on to upto 600 rows.

On Sheet 2: I also have the same data (not necessarily the same
numbers).

I want to do the following:

I would like to (on a new sheet) list of all the duplicate "Cert #s"
and their corresponding "Amounts".

What is the easiest way to do that?

BTW, I am using Excel 2002.

Thanaks is advance.



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

0
10/19/2003 5:31:48 AM
excel 39879 articles. 2 followers. Follow

2 Replies
580 Views

Similar Articles

[PageSpeed] 21

Chip Pearson has a bunch of techniques for dealing with duplicates at:
http://www.cpearson.com/excel/duplicat.htm

He has suggestions for worksheet formulas and VBA code there.

Axim5 wrote:
> 
> Hi,
> 
> I am new to this forum, infact this is my first thread. I have been
> reading various threads and replies and I must say that I am impress
> with the level of knowledge available here.
> 
> Now, I would like assistance in figuring out a solution.
> 
> I have 2 sheets with following data (for example):
> Sheet 1:
> 
> Cert #                        Amount
> CA 1000345              $1,000.00
> 
> and the above continue on to upto 600 rows.
> 
> On Sheet 2: I also have the same data (not necessarily the same
> numbers).
> 
> I want to do the following:
> 
> I would like to (on a new sheet) list of all the duplicate "Cert #s"
> and their corresponding "Amounts".
> 
> What is the easiest way to do that?
> 
> BTW, I am using Excel 2002.
> 
> Thanaks is advance.
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/19/2003 12:21:00 PM
Try the following steps, which uses Advanced Filter
in *back-up* copies of your workbook (not the original)

Note1:
(i) "Duplicate(s)" means: the 2nd, 3rd, 4th etc instances
of each Cert # in the col.
(ii) "Unique" means: the first instance of each Cert # in the col.

1. Assuming you want to extract the duplicate Cert #s
in Sheets 1 & 2 separately, i.e. you want the duplicates
present in each sheet (on its own)

(a) In Sheet1:

Sekect Cert # col
Click Data > Filter > Advanced Filter
(Click OK to the Excel prompt to use the first row as labels)

In the Advanced Filter dialog:
Check Filter the list, in-place
Check Unique values only
Click OK

Select all the "blue colored" filtered rows
(select all the "blue" row headers)

These are the "unique" rows which you do not want

Right-click > Delete rows

Click Data > Filter > Show All

This will reveal the duplicate rows in Sheet1

(b) Repeat steps for Sheet1 in Sheet2,
to get the duplicate rows in Sheet 2

(c) Copy > Paste the duplicate rows remaining
from (a) and (b) above into a new Sheet3

2. Assuming what you want is to extract the duplicates in Sheet 1 & 2
together,
i.e. you want the duplicates present when you combine the data from both
sheets

(a) In a new Sheet 3:

Copy > Paste the data from Cert # & Amount cols
in both Sheets 1 & 2 into 2 cols in Sheet3,
with say, Sheet2's data immediately below Sheet1's

[Note2: the data arrangement order, viz Sheet1's data first followed by
Sheet2's,
or the other way around, will dictate what is considered unique / duplicate
Cert #s.
Refer Note1's explanation above]

Perform same steps for 1(a) above to extract the duplicates
for the combined Sheet 1 & 2's data

hth
Max

"Axim5" <Axim5.vj78b@excelforum-nospam.com> wrote in message
news:Axim5.vj78b@excelforum-nospam.com...
> Hi,
>
> I am new to this forum, infact this is my first thread. I have been
> reading various threads and replies and I must say that I am impress
> with the level of knowledge available here.
>
> Now, I would like assistance in figuring out a solution.
>
> I have 2 sheets with following data (for example):
> Sheet 1:
>
> Cert #                        Amount
> CA 1000345              $1,000.00
>
> and the above continue on to upto 600 rows.
>
> On Sheet 2: I also have the same data (not necessarily the same
> numbers).
>
> I want to do the following:
>
> I would like to (on a new sheet) list of all the duplicate "Cert #s"
> and their corresponding "Amounts".
>
> What is the easiest way to do that?
>
> BTW, I am using Excel 2002.
>
> Thanaks is advance.


0
demechanik (4694)
10/19/2003 12:30:53 PM
Reply:

Similar Artilces:

Incorrect information on Check Stub
When printing a batch of checks, on the check stub in the field that we have designated for PO Number the information is incorrect/overwritten. In this case I have 18 invoices to pay on one check, nine of them with legitimate PO Numbers and the other nine with notes pertaining to the Invoice number that have been entered in the PO Number field in GP. Seven of the nine PO numbers have been overwritten with the nine notes that were related to the nine invoices directly under the top nine, i.e. the seven PO Numbers have been replaced and are no where on check stub the other two printed f...

Disable Spell Check
Is it possible to disable the spll check feature in Word 2002? It depends what you mean by 'disable'. You can prevent the spell checker from checking a document by selecting all of it and applying the no proofing language parameter to the selected text. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>><...

Subject Spell Check
How do I set Outlook 2002 (SP-2) to spell check the subject line when I hit F7 to force a spell check? Please advise! Thanks. ~jl YOu can't - it will only spell-check the message body. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, J. E. Lewis asked: | How do I set Outlook 2002 (SP-2) to spell check the | subject line when I hit F7 to force a spell check? | ...

Command button
[Excel 2003] I have command buttons on the userform which access other userforms (which drive the spreadsheet data entry) I have a listbox on the userform displaying a number. I would like to enter code into the command button activations such that if the number in the listbox does not equal the number stored within the code of the command button then a mesage box is displayed and the command button does not activate its sub routines. Can anyone help? Thanks, Roger Something like this: Option Explicit Public Property Get MayProceed() As Boolean Const M...

Printing Checks #4
Do I have to buy checks that already have my account and routing number printed to use Money 2003 Standard? Yes. "Tom P" <anonymous@discussions.microsoft.com> wrote in message news:06b601c3aef9$dc7264c0$a301280a@phx.gbl... > Do I have to buy checks that already have my account and > routing number printed to use Money 2003 Standard? ...

Concise Payable Check Voucher Listing
Is there a way (besides modifying the existing Check Edit List - this is needed as well) to get a concise pre check run listing of the all of the Vouchers in a batch prior to posting for easy review? If I can copy the Edit List and create a custom report then the user cant parameterize to limit to a given batch, so this is not ideal. My AP Manager wants to see a tabular listing with just a few basic fields (like VendorID, Voucher#, Date, AmountBeingPaid for this voucher), much more concise than the edit list. I dont see unposted check batches in SmartList, and I can't figure out ho...

duplicate supplier code
I have one supplier and two supplier codes. One is MTT and the other is MT. How do I -properly- convert all of the items from MT to MTT? How do I -properly- delete the vendor? I recommend that you get your Microsoft Partner to fix this one for you. Regards, Ivan Brebner "daytraitors" <daytraitors@discussions.microsoft.com> wrote in message news:6C3AF6F6-52B7-4AD1-9DEF-322B6C4E9249@microsoft.com... >I have one supplier and two supplier codes. > One is MTT and the other is MT. > > How do I -properly- convert all of the items from MT to MTT? > > How do I -...

Why can't a primary key be duplicated?
Why can't a primary key be duplicated in Access? Because a primary key, by definition, identifies a unique record within a table. If it can be duplicated, then you don't have uniquely identifiable records in your table. -- Ken Snell http://www.accessmvp.com/KDSnell/ "torijoshmom" <torijoshmom@discussions.microsoft.com> wrote in message news:ECB13770-C283-48A0-BC94-8374572F4E4E@microsoft.com... > Why can't a primary key be duplicated in Access? torijoshmom <torijoshmom@discussions.microsoft.com> wrote: >Why can't ...

Spell check not working in Outlook 2001 for Mac in OSX
Hello, When I try to use the spell check for messages I get the error message: "The spelling operation could not be completer. The spelling checker could not be started. Some components are either missing or incorrectly configured. See your administrator." Thanks, Virginia ...

Force TRUE in a check box as result from an option button??
I have a set of option buttons and a set of check boxes. One of the check boxes is for a certain value that is similar to one of the option buttons. Essentially, what I want is for that check box to automatically go to a "TRUE" (checked) state when the certain option button is selected. When the other option buttons are selected, the check box will remain in it's normal state which will allow the user to select it or not depending on other variables. But, if the option button is on that certain selection, I would like to make it so that check box MUST remain on TRUE, and ...

Duplicate spreadsheets opening
I'm having a problem with a certain file in Excel 97. When I open the file and select "Window" it shows that 2 spreadsheets were opened with the same name. Can anyone help with this? KC Close one of the windows and then save your document. It sounds like you saved the file once with two windows open. Andy. "KC" <karen.carlisle@valero.com> wrote in message news:2a76101c3925d$8411d1c0$a601280a@phx.gbl... > I'm having a problem with a certain file in Excel 97. > When I open the file and select "Window" it shows that 2 > spreadsheets we...

Duplicate Emails #9
I keep receiving the same emails over and over again. Sometmes they are on the same day; other times a few days later. I have Outlook Express 6.00.2800.1123 and Norton Antivirus. Thanks ...

many duplicate emails
I have trouble where recipents will receive the same mail that was sent last week. Sometimes as many as 60 of the same messages. It happens when I first mount the store and if I dismount and remount the store it will repeat those 1500 messages again. Once it sends the messages (or I delete the queue) it seems to run fine until it is restarted again. Of course restarting the server has the same effect and sends them out again. I have a Win2003 SP1 Server with Exchange 2003 SP1. Most users (~60) are on the mailbox store but ~20 are still on pst files. The clients are either OL 2003 or...

Duplicate Contacts mess
The pc has Office/Outlook 2003 with one user. It was originally configured, for some reason to do with importing, with several data files. To simplify I moved all the messages within Outlook then hid the extra data files in Data File Management. The problem is that there are two Contacts lists. I have it so that one pops up when the Address Book icon is pressed but when I do Rt-Clk Add to Contacts it gets added to the other one, which I assume is part of one of the other .pst files. When I look at the dialogue that is supposed to display a list of Contact folders none is displaye...

Duplicating parts of a worksheet
I am working with an excel worksheet with five columns (account, date description, purchase order, and expense amount). I want to summariz from this worksheet all of the expenses by account. I know how to get the total expenses for each account from this list but I want to be able to create another worksheet for each account tha lists date, description, purchase order, and expense amount. Is ther a way to do this without requiring double entering of everything -- Message posted from http://www.ExcelForum.com This sounds like a job for a Pivottable. You can create a nice summary repor...

Populate multiple records via check boxes
I've read through some of the posts here and found similar situations with solutions but I just can't quite understand how to do this. I am trying to use form controls i.e. checkboxes and/or multiselect list boxes to populate multiple records from a related table. I have 3 tables: teachers, subjects, & subjects_to_teachers. In the table "teachers_to_subjects" each record contains one teacher & one subject. The purpose of this table is to relate teachers to subjects in a many-to-many fashion. I have a primary form which shows teacher info. What I want is a form...

check decimal and if numeric
I have an unbound textbox, txtAmount. I want to make sure that user enters only two decimal places, and that the input is numeric. I tried using InputMask 99999999.99 which works for positive numbers, but that mask won't allow the minus sign (or plus sign) to be used. So, how can I allow input of a negative number? Currently, if TransactType is "Payment", it takes the value in Amount * -1 (if existing Amount > 0), so that effectively makes it a negative. But, a TransactType of "Adjustment" could be either negative or positive. I could make a TransactType of "...

Check off boxes
Publisher 2003 in use. I am creating a questionnaire that will be included in a newsletter. I am using the publisher software to make this form. Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, however, I cannot remember the key strokes. Any help along this line will be greatly appreciated. Thank you and enjoy your day. Mike Mike Reshetar wrote: > Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, > however, I cannot remember the key strokes. Use Wingdings and the � or q characters. -- Ed Bennett - MVP Microsoft Publisher h...

combining duplicate row values?
Excel 2007 XP Pro SP3 I have several rows of data as: 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st that I would like to combine (preferably into a separate worksheet) as: 3 gl 24 36 cw1 north 1st 2 g2 24 36 cw2 north 1st based on rows 1, 3, and 5 and rows 2 and 4 having duplicate data. Is there a way to do this? Dave DDP 1) Use the options in advanced filter to copy unique records to a location in the same worksheet. (Advanced filter does not copy to alternate sheet). 2) Move the records to the required sh...

Need formula to check data in 2 columns to sum 3rd column
Need a formula to enter on Report Sheet under Month as indicated from tables below. The data base worksheet has info inserted daily and the report will be on another worksheet to calculate as data is updated. Any ideas?? (Need total paid for all rows paid to Name 1 with dates in month of Jan) Data Base Worksheet Report Sheet NAME DATE PAID NAME JAN FEB Name 1 01/01/04 $200.00 Name 1 ??Paid ??Paid Name 1 01/22/04 $200.00 Name 2 ??Paid ??Paid Name 2 01/15/04 $200.00 Name 1 02/04/04 $200.00 Hi try the following =SUMPRODUCT(--(A1:A100="Name 1"),--(YEAR(B1:B100)=2004),--(MONTH(B1:...

Check Box Grouping
Hello, When I group on a check box field, I get duplicate records. Would anyone know why this would happen? Ellen What happens if you change your Grouping and Sorting expression to: =IIf([YourYNFieldName],"A","B") -- Duane Hookom Microsoft Access MVP "EllenM" wrote: > Hello, > When I group on a check box field, I get duplicate records. Would anyone > know why this would happen? > > Ellen Why would you group on a check box? I would think you would apply criteria only to get either Yes or No's. Post your que...

Voiding a check to a temp vendor
Our A/P group needs to void a check issued to a temporary vendor. However, when they try to do so, the payables transaction entry zoom indicates that the vendor isn't found. Can anyone provide any insight to this issue? They seem to recall that they may have purged temporary vendors, although they're not certain, and I am wondering if this may be the cause of this problem. You need to provide a bit more information... is the check still outstanding, meaning has the vendor not cashed the check yet? Are you voiding the check because of an NSF? Are you going to still owe the vendor for...

How to check programmatically if Excel is installed?
Hello, What will be the best way to check if Excel is installed and what is the name of executable? Is it always excel.exe? I think some older version had msexcel.exe, but I can be mistaken. Your thoughts please, Claire Claire By "programmatically" I take it that you mean VBA in Excel. To run any VBA macro in Excel you must first have to have Excel installed. Or did I miss something? HTH Otto "Claire" <replyto@fra> wrote in message news:#5oGjlPpKHA.1552@TK2MSFTNGP04.phx.gbl... > Hello, > What will be the best way to check i...

duplicate account keeps reappearing
Recently, my bank informed me that they were changing my credit card number, due to a security breach. Soon afterward, a duplicate account showed up in Money. I updated the credit card number in the original account, then merged the new account with it and deleted duplicate transactions. Then the new account reappeared. I merged it again, and it reappeared again. I tried it a few more times, but it keeps coming back. How can I fix this? In microsoft.public.money, Paul Pedersen wrote: >Recently, my bank informed me that they were changing my credit card number, >due to a se...

merge checking accounts
I just installed Money Small Business 05. Apparently my business checking account was listed as a personal account and it won't merge the existing data with the buisness account data it downloaded. I have two accounts now. Is there a way to convert the old account to a business account, or vise versa? Brian In microsoft.public.money, brian wrote: >I just installed Money Small Business 05. Apparently my business checking >account was listed as a personal account and it won't merge the existing data >with the buisness account data it downloaded. I have two accounts now. ...