Formula for Finding Duplicate Entries within Multiple Worksheets

Hi there folks!

I have a workbook that contains multiple worksheets for each month of the year that all contain a date, a house number, and other various information on shipments. These worksheets are distinguished by month and are organized by least recent to most recent. The sample is below.

I would really like to create a formula that can go into an added column C (added manually) and check to see if the number in column B is duplicated in any of the other worksheets, and return either the date (column A) or "Not Duplicated". I was thinking a VLookup but I'm not sure.

So there are two worksheets attached, the first being the original workbook with almost all the months and data, and the second one being what I desire as the outcome. In the second one, July is the month that I chose to put the column in. Any help would be much, much appreciated. I am using Excel 2003 so I am sure that will affect how to write this formula.

Thanks!

So, for the June sheet you would want to look back into the May sheet, but for July you want to look back into both June and May, and for August you would want to look at sheets July, June and May - is this right?

And you want to return the first date in a month where there is a duplicate?

Pete

Pete,

As far as looking back, yes. I would like each new sheet to be able to check all of the sheets that were created prior. Sorry I couldn't fit more of the months in, but the 1000 kB limit stopped me. The months go all the way back to the start of the year as a January sheet.

As far as the date, the first column, column A, has the date that the document was recieved, and that is what I would like to be returned. I should have mixed up the dates a little more, I can see that now.

Thanks!

So, will there only ever be one duplicate? If there are more (maybe in different months), which date would you like returned - the earliest or the latest?

Pete

Pete,

There shouldn't be duplicates, but if there are, the most recent date should be returned.

Thanks!

Okay, using your Outcome file, and assuming you have inserted a new column C in both the May and June sheets (to keep an identical layout), you could use this formula in C2 of the June sheet:

Formula:

Please Login or Register  to view this content.

and copy this down. For the July sheet you have to nest two IF functions like this in C2:

Formula:

Please Login or Register  to view this content.

This looks in June first and then in May.

To apply this to later sheets you would need to do a similar nesting, i.e. copy that formula to C2 of the August sheet and add this part to the beginning of the formula:

Formula:

Please Login or Register  to view this content.

then add a close-bracket to the end. Eventually you will end up with a mammoth formula where you are searching back through 11 sheets.

Hope this helps.

Pete

Thanks Pete! This definitely helps a lot.

Only one problem though, instead of returning the date in column A as it is, it returns some weird numbers that I would assume are cell assignments?

For example, it returns 41395 instead of 5/1/2013.

Any thoughts?

Ohhhhhhhh makes sense. Thanks FDibbins!

...how do I format it? (sorry)

GOT IT. All solved! Thanks everyone!

Glad to be able to help.

Pete

Hey so when I go to put it in the original spreadsheet I was sent, it comes up with an error message that describes the problem as "The value was already entered. All Item Numbers must be unique. Pleast try again."

All I did was create a new column C in June and May, and input this formula:

Please Login or Register  to view this content.

Dang it can someone help me out with this error?

Yes, the same thing happened to me. I thought it was data validation from column B being carried over to the new column C. I just copied another column (e.g. F) into column C, and deleted the contents to retain the formatting, and then put the formula in.

For other sheets you can just copy that column over first and then add the extra parts, as advised above.

Hope this helps.

Pete

Pete,

Yep, totally worked that time. Once again, thank you so much for your help through this formula. Much Obliged.