Trying to count duplicates and place unique value in results column

[ozgothic]

Hi,

I have 2 worksheets, each with a list of email addresses.

I'm looking to see which emails are in both worksheets. A simple COUNTIF would solve this, however instead of a 1 or 0 in the results column, I'm actually looking to place the value from another existing column, in place. This is the date the email was entered into the system.

So for example:

Sheet 1 A1 has an email address
Sheet 1 B1 has the date the email was entered
Sheet 2 A1 has an email address
Sheet 2 B1 is my formula: =COUNTIF(A:A, Sheet 1!A1) is as far as I have got. That displays a 1 or 0 depending on whether there is a duplicate.

What I want to have displayed in Sheet 2 B1 is the date from Sheet 1 B1 IF Sheet 1 A1 is the same as Sheet 2 A1. The date changes depending on when each email was entered so I'd want the corresponding date that goes with the duplicate email address.

Hope that makes sense.

Let me know if you need any more information.

Kind regards,

Peter

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Trivial examples from a text description are not the best way of ensuring a valid solution first time. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

[ozgothic]

excelforumExample.jpg

Thanks Richard, hope this image explains things better

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[ozgothic]

You don't have to be rude, I'm new to this and figured it was a pretty simple formula for someone who knows their way around excel (I've got most of the way with COUNTIF), so wouldn't require an actual workbook to be uploaded.

Nevermind, I'll find somewhere a little more welcoming to newbies

[jtakw]

Hi,

No one is being "rude" to you, it's merely pointing out that by uploading a sample file will make it much easier for potential helpers to Help You.

BTW, COUNTIF is not the way to go...

[AliGW]

I am sorry you think that a request to see the actual workbook with clear instructions about how to attach the workbook here is rude. It is not meant to be - in fact, it is intended to help you find a solution quicker. It's up to you entirely whether you stay and get that help, of course. By the way, post #2 was also asking you to upload a workbook, not an image of it.