-1

I've got a workbook that has spread a list across 10 different worksheets, but the list has become out of date. I'm trying to find out which of those in the new list aren't already included in one of the other worksheets.

Basically, I'm looking for a formula that will tell me whether or not the contents of a cell can be matched anywhere else in a workbook.

Cheers.

11
  • Nothing yet, to be honest. I'm not very experienced with excel; my abilities pretty much start and end with vlookup. I would use that but I don't think you can make a table across multiple worksheets.
    – Ixon
    Apr 11, 2016 at 13:42
  • What you have to do is copy and paste each list, from each worksheet (all in one long column), into a new worksheet. Then sort the list in alphabetical order so you can see any duplicates. Start from there. It's a manual process. This is the process whereas a formula will not work in this scenario. I would try to keep one list from that point on. Since I've no idea of what those lists are for, I can't further suggest a different approach in a better approach of tracking for future projects.
    – ejbytes
    Apr 11, 2016 at 13:49
  • @ejbytes if OP did put everything into a single column (which wouldn't be my approach) why wouldn't OP use conditional formatting to highlight duplicates? Apr 11, 2016 at 13:57
  • @Raystafarian Why don't you write up a solution? What formula to use from several worksheets. Why are you asking me?
    – ejbytes
    Apr 11, 2016 at 14:02
  • @ejbytes I'm addressing you because it didn't seem you considered that option, which would be a lot easier than a manual procedure. I thought that information might be useful to you sometime in the future. Apr 11, 2016 at 14:04

1 Answer 1

0

Consider making your "Lists" a database. Think of each list as a category as you have already stated. You would need a column for "category" and/or each "subcategory". Consider the image here. I am a movie buff and created this database for my movies that can be categorized by Genre, and sub-genre, and further by runtime, leading actor, or even actor mentioned in the movie, and release date. Click on the image for a "zoomed view".

enter image description here

The top is an image of my working database in "Design View". This is a database set up in MS Access. The image below the database is my "Table". Your lists are actually tables. A table is nothing less than a list. Take a closer look at the columns in my "table". With these headings I can manipulate my Titles and a dynamic way and sort them any way I wish. Although this is more than what you are doing with your Excel datasheet lists, I hope this will inspire you to think in terms of data and tables, instead of lists.

1
  • This is only a working answer for sake of images for illustration to help demonstrate why a database with tables is more useful over several lists. The table in the image (bottom portion) gives perspective on how to arrange a list in an excel spreadsheet; needed header values for data manipulation and sorting/filtering (querying).
    – ejbytes
    Apr 11, 2016 at 14:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .